Join Two Tables, Mixed Results

I have two tables:
Table 1: adopt_a_pet (10 records)
adopt_id
heading
content

Record content:
1 HEADING CONTENT CONTENT CONTENT
2 HEADING2 CONTENT CONTENT CONTENT
3 HEADING3 CONTENT CONTENT CONTENT
4 HEADING4 CONTENT CONTENT CONTENT
5 HEADING5 CONTENT CONTENT CONTENT

Table 2: adopt_a_pet_docs (2 records)
link_id
adopt_id
title
link_url

1 1 TITLE OF LINK URL OF LINK
2 1 TITLE OF LINK2 URL OF LINK2

Problem:
The results I’m getting are this:
1 HEADING CONTENT CONTENT CONTENT
1 1 TITLE OF LINK URL OF LINK
1 HEADING CONTENT CONTENT CONTENT
2 1 TITLE OF LINK2 URL OF LINK2
2 HEADING2 CONTENT CONTENT CONTENT
3 HEADING3 CONTENT CONTENT CONTENT
4 HEADING4 CONTENT CONTENT CONTENT
5 HEADING5 CONTENT CONTENT CONTENT

I’m getting a repeat of the links when I just want the links to fall under adopt_a_pet.adopt_id 1

The MySQL I’m using:
SELECT *
FROM adopt_a_pet LEFT JOIN adopt_a_pet_docs ON adopt_a_pet.adopt_id = adopt_a_pet_docs.adopt_id

THE RESULTS I WANT:
1 HEADING CONTENT CONTENT CONTENT
1 1 TITLE OF LINK URL OF LINK
2 1 TITLE OF LINK2 URL OF LINK2
2 HEADING2 CONTENT CONTENT CONTENT
3 HEADING3 CONTENT CONTENT CONTENT
4 HEADING4 CONTENT CONTENT CONTENT
5 HEADING5 CONTENT CONTENT CONTENT

Am I to perform another SELECT join?

Yes, well, I still appreciate the replies nonetheless. I always look into them.

Thank you!

I’ll look into UNIONS. I never paid much attention to that.

Thank you.

I don’t know if its still useable information, but you should use MySQL UNION like:

(SELECT column1, column2) UNION (SELECT column1, colomn2) ORDER BY column1

More info: http://dev.mysql.com/doc/refman/5.0/en/union.html

Take a look at this: http://www.sitepoint.com/forums/showpost.php?p=4558479&postcount=3

Of course, you’ll have to tweek it a bit to make it work in your situation.

Darn. This post is more than 6 months old… :nono:

what you say you’re getting isn’t what you’re really getting

you show the rows from separate tables “underneath” each other, as though they were produced by a UNION

in a JOIN, the rows are side by side in the result –

1 HEADING1 CONTENT CONTENT CONTENT 1 1 TITLE OF LINK1 URL OF LINK1
1 HEADING1 CONTENT CONTENT CONTENT 2 1 TITLE OF LINK2 URL OF LINK2
2 HEADING2 CONTENT CONTENT CONTENT NULL NULL NULL NULL
3 HEADING3 CONTENT CONTENT CONTENT NULL NULL NULL NULL
4 HEADING4 CONTENT CONTENT CONTENT NULL NULL NULL NULL
5 HEADING5 CONTENT CONTENT CONTENT NULL NULL NULL NULL

yes, the data from HEADING1 is “repeated” in the result set, but this is completely due to the fact that it’s a one-to-many relationship

in your application language (php?) you would loop over the rows of the result set and handle the repeating data accordingly

make sense?

It took some finaggling, but I nested the loop and it is now looking like I wanted it:

HEADING 1
CONTENT CONTENT
LINK 1
LINK 2
HEADING 2
CONTENT CONTENT
HEADING3
CONTENT CONTENT, ETC.

I guess I was making it harder than I should’ve.

Toad78, could you please share the PHP you used to solve the problem (as you said, by “nesting the loop”)? I am having the exact same issue as you did and I’d rather not start a new thread.

Thanks in advance.