Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    Dec 2006
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    mySQL query with 3 tables

    I have to query 3 tables from a database. I am just using abbreviated table description since the tables are very large

    Table A
    ID_A
    Name_A

    Table B
    ID_B
    Name_B

    Table C
    ID_C
    ShowName
    ID_A
    ID_B

    Now, I need to query all 3 and have Table A and B show the name

    Table C is the main table that I need. Table A and B are reference tables.

    If I do a select TableA.Name_A, TableB.Name_B, TableC.ShowName WHERE TableC.ID_A = TableA.ID_A and TableC.ID_C = TableB.ID_B

    It only shows the records in Table C that have a reference to Table A and B. If there is nothing in those fields, then it doesn't show. How can I query C to show all the records and the ID_A and ID_B to show their names if there is data, if not, just leave blank?

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    Don't use list syntax when you are joining tables, use explicit on clauses.

    Change this style:
    Code:
    select TableA.Name_A, TableB.Name_B, TableC.ShowName WHERE TableC.ID_A = TableA.ID_A and TableC.ID_C = TableB.ID_B
    to this style and stick with it:

    Code:
    SELECT
    TableA.Name_A, 
    TableB.Name_B, 
    TableC.ShowName
    FROM
    TableC
    INNER JOIN
    TableA
    ON
    TableC.ID_A = TableA.ID_A
    INNER JOIN
    TableB
    ON TableC.ID_C = TableB.ID_B
    list syntax does not allow you to mix in outer joins without running into errors. you need LEFT JOINS if you still want info from your tables even if it doesn't match that in the other tables, so change INNER JOIN above to LEFT OUTER JOIN to get what you need.

  • #3
    New Coder
    Join Date
    Dec 2006
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts
    left outer join did the trick. THANK YOU. THANK YOU. THANK YOU.


  •  

    Posting Permissions

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