Double join question

[DaanOpstaan]

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!

[samanime]

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.

[DaanOpstaan]

a right join makes no difference. maybe something with a subquery??

[samanime]

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.

[DaanOpstaan]

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

[samanime]

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).

[DaanOpstaan]

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

[swampBoogie]

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.

[samanime]

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.

[DaanOpstaan]

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

[samanime]

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.