SQL Stuff

Joans · **Joined:** 31st Mar, 2008 **Posts:** 8658

It's late and my head hurts, do my work for me please beex.

Let's say I have a table that looks like this:

A | B
------
1 | True
1 | False
1 | False
2 | True
2 | True

I want a query that will return field A, where every result in B is true (so in this case I want it to return 2).
I'm a bit rusty, so I'm probably missing something easy.

Grim... · **Posted:** Tue May 24, 2011 21:40

Er...

Code:

SELECT `A` FROM `tablename` WHERE `B` = 'True'

?

[edit]Oh right, hang on.

Joans · **Joined:** 31st Mar, 2008 **Posts:** 8658

Grim... wrote:

Er...

Code:

SELECT `A` FROM `tablename` WHERE `B` = 'True'

?

Er...
No

Grim... · **Posted:** Tue May 24, 2011 21:43

Here's one way:

Code:

SELECT `A` FROM `tablename` AS tablename1
LEFT JOIN `tablename` AS tablename2 ON tablename1.A = tablename2.A AND tablename2.B = 'False'
WHERE tablename2.A IS NULL

Better column names next time, please

[edit]If the table is huge and the join is too slow, you can do it with a GROUP BY and an IF.
[edit2]Capital 'F' on False.

Trooper · **Joined:** 4th Mar, 2010 **Posts:** 22406

Grim... wrote:

Er...

Code:

SELECT `A` FROM `tablename` WHERE `B` = 'True'

?

[edit]Oh right, hang on.

Heh, I wrote exactly that! Then re-read the question and thought, hang on...

Joans · **Joined:** 31st Mar, 2008 **Posts:** 8658

I know I said I was rusty, but give me some credit. Cheers Grim... I shall test that later.

GazChap · **Posted:** Wed May 25, 2011 9:58

Could you not do

Code:

SELECT DISTINCT(A) FROM tablename WHERE B='True';

//edit: No, I've just re-read it. Haha.

Be Excellent To Each Other

SQL Stuff

Who is online