query two tables

hi,

I have two tables,

t1 t2

col1 col1 col2

1 1 0
2 1 0
3 1 1
4 2 0
5 2 0
6 3 1
7 3 1
8 4 1
9 4 1
10 4 1
11 4 1

t2.col1 is the key from t1.col1

I want to retrieve all t1.col1 records which equal to t2.col1 and tb2.col2 has ONLY "1"

the result should be: 3, 4

I try:

select tb1.col1 from t1 as tb1 where tb1.col1 in (select col1 from t2 where col1=tb1.col1 and col2=1 and ...?.)

any help?

my idea is to have it like this:

SELECT top 2 start at 3 t1.col1 from t1 left join t2.col1 on t1.col1 = t2.col2 where t2.col2='1'

hope it helps.

Cheers,

CLIPER

|||

This should do the trick. I don't think you want to use "TOP" as there may be a different number of results each time.

SELECT DISTINCT
tb1.col1
FROM
tb1
INNER JOIN
tb2
ON
tb2.col1 = tb1.col1
GROUP BY
tb1.col1
HAVING
AVG(tb2.col2) = 1

|||

well, just been curious of what he says: "the result should be: 3, 4"

so your query would not be valid if he wants only 3 and 4. :)

Cheers,

CLIPER

|||

Hi CLIPER - try my query - it returns 3 and 4.

|||

thanks for the reply.

Sohnee's trick: AVG(tb2.col2) = 1

this is not valid, as tb2.col2 is "bit" data type

1 = true, 0 = false.

nice try, but still need better solution...|||

well, I should look into it later. :) thanks!

|||

If it's a bit type, this should work.

SELECT DISTINCT
tb1.col1
FROM
tb1
INNER JOIN tb2 ON tb2.col1 = tb1.col1
WHERE
tb1.col1 NOT IN (SELECT tb2.col1 FROM tb2 WHERE tb2.col2 = 0)

|||

Thanks, Sohnee, you just save my day...