Jump to content

Selection problem


Revelation

Recommended Posts

Hi,

 

I've got this (simplified) table:

 

Name Value
Ben 1
Ben 2
Tom 3

Hank4

 

What I would like to do is select every name from this table that has not got 2. The problem is that I can't use a subquery (MySQL 4.0) and that WHERE NOT Value = '2' doensn't work, because then Ben gets selected anyway (via Ben 1) and that's not what I want.

 

Can someone help me?

 

Thanks!

Link to comment
https://forums.phpfreaks.com/topic/71232-selection-problem/
Share on other sites

SELECT name, COUNT(*) as ct

FROM table

GROUP BY name

HAVING ct = 1

 

Interpreting your question as eliminating any name that has a value of 2, rather than a count of 2, use a temporary table instead of a subquery

 

CREATE TEMPORARY TABLE tmp SELECT name FROM tablename WHERE value = 2

 

Then

 

SELECT t.name

FROM tablename t

LEFT JOIN tmp p ON t.name = p.name

WHERE p.name IS NULL;

Link to comment
https://forums.phpfreaks.com/topic/71232-selection-problem/#findComment-358339
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.