SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Double join question

    I have 3 tables linked with a left join. see example below:

    SELECT s.suiteid, s.testid, s.naam, ms.id, ms.projectid, ms.naam FROM suites as s
    left join tests as t on s.testid = t.id
    left join meta_suites as ms on t.projectid = ms.projectid
    where s.testid = 44

    result:
    '10000', '44', '000_10000_suite_algemeen', '10000', '1', 'Basis test SKM'
    '10000', '44', '000_10000_suite_algemeen', '10050', '1', 'Informatievoorziening'
    '10000', '44', '000_10000_suite_algemeen', '10100', '1', 'Test beheer'

    below is the outcome that I really want:
    '10000', '44', '000_10000_suite_algemeen', '10000', '1', 'Basis test SKM'
    ' ', ' ', ' ', '10050', '1', 'Informatievoorziening'
    ' ', ' ', ' ', '10100', '1', 'Test beheer'

    The connection is the "10000" in both tables. Because 10050 and 10100 has no connection they don't have data in the first table

    thanks for helping!

  2. #2
    SitePoint Wizard
    Join Date
    Dec 2003
    Location
    USA
    Posts
    2,582
    Mentioned
    29 Post(s)
    Tagged
    0 Thread(s)
    Oh, nvm... I'm a dummy head. =p

    LEFT JOIN will basically only get something where all of them have values.

    What you want is probably a RIGHT join for the first table.

  3. #3
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    a right join makes no difference. maybe something with a subquery??

  4. #4
    SitePoint Wizard
    Join Date
    Dec 2003
    Location
    USA
    Posts
    2,582
    Mentioned
    29 Post(s)
    Tagged
    0 Thread(s)
    Can you break down the data in each database (as it relates to this).

    If there was no relationship at all, the 10000 wouldn't be there... so there is some relationship I can't determine from that little bit of data.

    Thanks.

  5. #5
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Table Suites:
    '10000', '44', '000_10000_suite_algemeen'

    Table Tests:
    '44', '1', '2011-02-25 19:00:47', '2011-02-25 19:01:29', 'passed', '2', '0', '2'

    Table Meta_suites:
    '10000', '1', 'Basis test SKM', 'Basis test SKM'
    '10050', '1', 'Informatievoorziening', 'Tests van de informatievoorziening'
    '10100', '1', 'Test beheer', 'testen beheer activiteiten'


    Relations are:
    44 is testnumber
    10000 is testcasenumber
    and 1 in tests and meta_suites is the project number

    i want to make a collection of suites in a test (nr. 44) AND meta_suites which id not in suites.

    '10000', '44', '000_10000_suite_algemeen', '10000', '1', 'Basis test SKM'
    ' ', ' ', ' ', '10050', '1', 'Informatievoorziening'
    ' ', ' ', ' ', '10100', '1', 'Test beheer'

    so 10000 has matching information in the meta_suites table and 10050 and 10100 has not yet meta information

  6. #6
    SitePoint Wizard
    Join Date
    Dec 2003
    Location
    USA
    Posts
    2,582
    Mentioned
    29 Post(s)
    Tagged
    0 Thread(s)
    Ah, I see.

    So, if you ignore the second join, you get this:
    10000, 44, 000_10000_suite_algemeen

    Then, because of the test ID, with the third join this winds up getting duplicated, so you get the results you get, where it joins the test ID to the other.

    Changing the second table to be a RIGHT JOIN should do the trick, though you'll likely get more rows then you want. If you add something like "GROUP BY ms.id', you can get rid of the duplicate ms.id rows (assuming ms.id is unique... if it's not you may lose results you want).

  7. #7
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Unfortunately, nothing works. I tried left, right and inner joins with group by and having

    the result is:
    '10000', '44', '000_10000_suite_algemeen', '10000', '1', 'Basis test SKM'
    '10000', '44', '000_10000_suite_algemeen', '10050', '1', 'Informatievoorziening'
    '10000', '44', '000_10000_suite_algemeen', '10100', '1', 'Test beheer'

    or only
    '10000', '44', '000_10000_suite_algemeen', '10000', '1', 'Basis test SKM'

    maybe another type of query will do the trick??

  8. #8
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    697
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Don't try to solve this type of problem with SQL.

    It is much each easier and more efficient to handle in the presentation layer.

    Retrieve the data ordered by s.suiteid, s.testid, s.naam and keep track of when there are new values for these values.

  9. #9
    SitePoint Wizard
    Join Date
    Dec 2003
    Location
    USA
    Posts
    2,582
    Mentioned
    29 Post(s)
    Tagged
    0 Thread(s)
    It seems that you want everything from meta_suites, so it may be prudent to rewrite your query and make that be the main table (in the from), and then do a cross join to get the other tables.

  10. #10
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    with a bit of tinkering I found the solution!

    (SELECT s.id, s.suiteid, s.testid, s.naam, s.result, s.totalTime, s.numTestTotal, s.numTestPasses, s.numTestFailures FROM suites as s left join tests as t on s.testid = t.id where t.projectid=1 and s.testid =44)

    union

    (SELECT null,id,null, ms.naam,ms.omschrijving,null,null,null,null FROM meta_suites as ms where ms.projectid=1 and ms.id not in (select suiteid from suites))

    With the union i combine the two tables with the folowing result:

    '256', '10000', '44', '000_10000_suite_algemeen', 'passed', '26', '4', '4', '0'
    NULL, '10050', NULL, 'Informatievoorziening', 'Tests van de informatievoorziening', NULL, NULL, NULL, NULL
    NULL, '10100', NULL, 'Test beheer', 'testen beheer activiteiten', NULL, NULL, NULL, NULL

    if someone knows a better solution i like to hear. Thanks

  11. #11
    SitePoint Wizard
    Join Date
    Dec 2003
    Location
    USA
    Posts
    2,582
    Mentioned
    29 Post(s)
    Tagged
    0 Thread(s)
    Not bad. There probably is a slightly better way, but off the top of my head I'm not sure what it'd be.

    Good job.


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
  •