Revelation Posted September 30, 2007 Share Posted September 30, 2007 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 More sharing options...
Barand Posted September 30, 2007 Share Posted September 30, 2007 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 More sharing options...
Revelation Posted October 1, 2007 Author Share Posted October 1, 2007 Thanks! A pity there isn't another way than to create a temporary table Link to comment https://forums.phpfreaks.com/topic/71232-selection-problem/#findComment-358872 Share on other sites More sharing options...
fenway Posted October 1, 2007 Share Posted October 1, 2007 Thanks! A pity there isn't another way than to create a temporary table There is, but not until 4.1. Link to comment https://forums.phpfreaks.com/topic/71232-selection-problem/#findComment-358987 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.