SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Guru
    Join Date
    Jul 2003
    Location
    england
    Posts
    822
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Question 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?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Jul 2003
    Location
    england
    Posts
    822
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Smile

    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.

  4. #4
    SitePoint Zealot eeight's Avatar
    Join Date
    Oct 2006
    Location
    New York City
    Posts
    106
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #5
    SitePoint Member
    Join Date
    Jan 2007
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Wink

    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

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by eeight View Post
    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.
    Take a look at this: http://www.sitepoint.com/forums/show...79&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...

  7. #7
    SitePoint Guru
    Join Date
    Jul 2003
    Location
    england
    Posts
    822
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by koenpunt View Post
    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
    I'll look into UNIONS. I never paid much attention to that.

    Thank you.

  8. #8
    SitePoint Guru
    Join Date
    Jul 2003
    Location
    england
    Posts
    822
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Take a look at this: http://www.sitepoint.com/forums/show...79&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...
    Yes, well, I still appreciate the replies nonetheless. I always look into them.

    Thank you!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •