-
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