Even trickier join :-)

I seem to hang mySql every time I try a variation of this query, so I must be doing something wrong...

Scenario - simplified to two tables:

Table1: THINGY

id | name
------------
1 | aaa
2 | bbb
3 | ccc


Table2: ATTRIBUTE

id | thingy_id | type | value
----------------------------------
1 | 1 | you | 500
2 | 1 | silly | 600
3 | 2 | you | 700
4 | 2 |sausage| 800
5 | 3 | silly | 900
6 | 3 |sausage| 1000


I want a query that will select *all* thingy's, and show the value of *a particular type* of their attribute, regardless of whether or not it exists (i.e. give me NULL if it doesn't).


example: get the value of "you" for each thingy

Result:

name | type | value
------------------------
aaa | you | 500
bbb | you | 700
ccc | NULL | NULL


I tried this:

select t.name, a.value from THINGY as t left join ATTRIBUTE as a on (a.thingy_id = IF(a.type="you", a.id, NULL));

But that just hangs the server (overnight!)


Any ideas?

Thanks!!!

Mark

markw@illuminae.com

I think the below should work.

SELECT t.name, a.type, a.value
FROM thingy t
LEFT OUTER JOIN attribute AS a ON t.id = a.thingy_id AND a.type = 'you';

Good luck.

Yes, it does work. I just found this in the mysql online documentation too :-)

thanks!

mark