if statment or mysql query I'm not sure..

[c7borg]

Hi all..

 

My head just isn't working correctly today I can write this in english  but can't think how to put this or even search for the code I'm looking for..

 

so I have a table such as this..

id - lineno - hitcount
1 - 2 - 0
2 - 2 - 0
3 - 3 - 0
4 - 3 - 1
5 - 4 - 0
6 - 4 - 0
7 - 4 - 0

 

And want to echo only if the hitcount is 0 for everything with a lineno of x

 

So in this case I should return lines 1,2,5,6 & 7 but not 3.

 

Any help appreciated

Andy.

[Ch0cu3r]

That can be handled by your query, example

SELECT id, lineno FROM table WHERE hitcount = 0

The above query will return all records with a hitcount of 0

 

If you want all records where lineno is not 3 and has a hit count of 0 you'd use

SELECT id, lineno FROM table WHERE lineno != 3 && hitcount = 0

[Psycho]

@Chocu3r,

 

I think he only only wants records where ALL records for each unique lineno ID have a hitcount of 0. In his example above there are two records with a lineno of 3. But, one of them has a value that is not 0. Therefore, all records with a lineno of 3 should be excluded.

 

@c7borg:

 

Use a subquery to select the lineno's which have a non-zero value. Use that result to then select all records that do not use those lineno values.

SELECT id, lineno
FROM table_name
WHERE lineno NOT IN (SELECT DISTINCT lineno FROM table_name WHERE hitcount <> 0)

Edited May 29, 2014 by Psycho