Select rows problem

[fsqueeen]

I had 2 tables here, tbl1 and tbl2. Now we got 6 items in tbl1, and tbl2 store the color for each item (maximum 3 colors for each item).

tbl1
----
id(PK) name
1 Jack
2 Amy
3 Bill
4 Ken
5 Rye
6 Icy

tbl2
-----
id(PK) t1_id(FK) color
1 1 green
2 1 red
3 1 blue
4 2 red
5 2 yellow
6 2 blue
7 3 purple
8 3 white
9 4 blue
10 5 black
11 6 red

What I need is a query to select item from tbl1 WHERE none of its color is 'green' in tbl2. Suppose item 'Jack' is not qualified but the query below will include 'Jack' in the result.

SELECT tbl1.id, name, color FROM `tbl1` left join tbl2 on tbl1.id = tbl2.t1_id where color != 'green'

I got no idea how to write a query to exclude 'Jack' since one of its color is green. Anyone can help me? Thanks in advance!

* My Mysql version do not support subquery.

 

[swampBoogie]

SELECT tbl1.id, name, color 
FROM tbl1 left join tbl2 on tbl1.id = tbl2.t1_id 
where not exists
  (select * 
     from tbl2
    where tbl1.id = tbl2.id
       and color = 'green')

 

[fsqueeen]

Thanks for your reply, swampBoogie.

But I'm sad that my Mysql version do not support subquery. Any other way to write this query?

Thanks...

 

[swampBoogie]

SELECT tbl1.id, name, t2.color
FROM tbl1 left join tbl2 t2 on tbl1.id = t2.t1_id
  left join tbl2 t3 on t2.t1_id = t3.t1_id
    and t3.color = 'green'
 where t3.color is null

time to upgrade ...

 

[fsqueeen]

Thanks swampBoogie! It's work!