SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Thread: SQL join query

  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2003
    Location
    Beijing
    Posts
    33
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL join query

    I am trying to figure out how I can create a SQL statement that will query two tables and return all rows where table1.id = table2.link_id as well as where table2.link_id = NULL.

    The table structures are as follows:

    Table1:
    id
    First name
    Last name

    Table2:
    id
    linked_id
    Company name

    I am trying to select all records from Table2 where Table1's ID field is equal to Table2's linked_id field. The problem is that I also want to display all records in Table2 that do not contain a link to Table1 (via the linked_id field). So, the following is the SQL statement I've tried:

    Code:
    SELECT * 
    FROM table1, table2
    WHERE (
    table1.id = table2.linked_id OR table2.linked_id = '0'
    )
    The problem with this is that the query returns duplicated rows, so for example, if the contents of Table1 are as follows:

    Table1:
    Record 1:
    id - 1
    first_name - Joe
    last_name - Pesci

    Record 2:
    id - 2
    first_name - Forrest
    last_name - Gump

    And the contents of Table2 are as follows:

    Table2:
    Record 1:
    id - 1
    linked_id - 1
    Company name - Goodfellas

    Record 2:
    id - 2
    linked_id - 0
    Company name - Bubba-Gump

    Note that with the linked_id being 0, record 2 (Bubba-Gump) will not be displayed with the following SQL:

    Code:
    SELECT * 
    FROM table1, table2
    WHERE (
    table1.id = table2.linked_id
    )
    However, if I use the following code:

    Code:
    SELECT * 
    FROM table1, table2
    WHERE (
    table1.id = table2.linked_id OR table2.linked_id = '0'
    )
    The system returns about 6 records, even though there are only 2 records in the table2 table. I've tried using the Distinct and DistinctRow functions as follows:

    Code:
    SELECT Distinct table2.id, table1.*, table2.* 
    FROM table1, table2
    WHERE (
    table1.id = table2.linked_id OR table2.linked_id = '0'
    )
    but they don't seem to make any difference, I still receive 6 results. I'm a bit confused as to why this is happening.

    Can anyone let me know what SQL command I should use in order for the system to list all the records in Table2 that are joined to Table1, as well as all the records in Table2 that are not linked to Table1?

    Thanks for your help!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    do you know about LEFT OUTER JOIN?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Oct 2003
    Location
    Beijing
    Posts
    33
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for pointing me in the right direction, that was exactly what I was looking for.


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
  •