SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    blonde.... Sarah's Avatar
    Join Date
    Jul 2001
    Location
    Berkshire, UK
    Posts
    7,442
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    multiple left joins.....?

    Is this possible?

    What I have is a questionnaire which goes into 6 tables on a mysql database, everyone fills in the first general page (table1) and then goes on to another page etc whilst viewing all the information for the client I have found that about 200 records aren't being viewed. i.e. the ones where they only filled in the first page and then haven't filled in anything else, as all my select statements look for id=aid etc. So I tried this statement:

    select table1.id from table1 left join table2 on table1.id=table2.aid where table2.aid is null;

    This works fine BUT I want to check for left joins with other tables at the same time i.e. looking for all the ones in table1 which aren't linked to any other table.

    Any thoughts? It seems simple but I just can't get the commands to work right!

    Any help much appreciated bit worried that I have "lost" 200 records!!!

    Thanking you

    Sarah

  2. #2
    SitePoint Evangelist CyberFuture's Avatar
    Join Date
    May 2001
    Location
    San Diego, CA
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've been puzzling over your problem, mainly because I am not clear on what you are to trying accomplish. Are you trying to get a list of rows in table 1 that don't have corresponding rows in table 2 (and 3 and so on) or do you want to show all rows regardless of whether or not they have corresponding rows in other tables?

    The query you showed, would only give the IDs of rows that didn't have a link to table 2. If you wanted to extend this to all the tables, then try this:

    select table1.id
    from table1
    left join table2 on table1.id=table2.aid
    left join table3 on table1.id=table3.aid
    left join table4 on table1.id=table4.aid
    left join table5 on table1.id=table5.aid
    left join table6 on table1.id=table6.aid
    where table2.aid is null
    OR table3.aid is null
    OR table4.aid is null
    OR table5.aid is null
    OR table6.aid is null;


    Or you can change the OR to AND to get the IDs of all rows where only table 1 has enteries.

    Hope this helps.

  3. #3
    blonde.... Sarah's Avatar
    Join Date
    Jul 2001
    Location
    Berkshire, UK
    Posts
    7,442
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    perfect.. that is exactly what I wanted all table1 id's which had no left join to any other table.

    Thank you sorry that I wasn't clear on what I wanted

    Cheers

    Sarah


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
  •