[SOLVED] Need help with a weird query based on count()

[leafface]

I'll make it as illustrative as i can.

 

---

Tables:

 

main

+-----+----------------------

| id  | some other fields...

+-----+----------------------

| 4  |                     

| 65  |                     

| 123 |                     

+-----+----------------------

conn

+-----+------+---+

| id  | main | x |

+-----+------+---+

| 1  | 4    | 0 |

| 2  | 4    | 1 |

| 3  | 123  | 0 |

| 4  | 123  | 0 |

+-----+------+---+

---

Query:

 

SELECT m.* FROM main m

LEFT OUTER JOIN conn c ON m.id = c.main

GROUP BY m.id

HAVING count(c.id) = 0;

---

Result will be the line of 65.

 

What i want is that that "count" only counts when x=1. In that case the result would be the lines of 65 and 123.

 

Thank you in anticipation.

[teng84]

SELECT m.* FROM main m

LEFT OUTER JOIN conn c ON m.id = c.main

where c.x = 1

GROUP BY m.id

 

or maybe

 

SELECT m.id,count(c.id ) FROM main m

LEFT OUTER JOIN conn c ON m.id = c.main

where c.x = 1

GROUP BY m.id <-- should give you something like (65 numberfields)

 

 

 

is that what you mean?

[leafface]

SELECT m.id,count(c.id ) FROM main m

LEFT OUTER JOIN conn c ON m.id = c.main

where c.x = 1

GROUP BY m.id <-- should give you something like (65 numberfields)

 

No, that's exactly the opposite of what i want. Because this will return: (id => 4, count => 1)

The count() should avoid those rows while counting where c.x<>1, not the query.

 

Actually i can go on by fetching the opposite of the needed result, and exclude the result set from the complete ID list with php. But that's pretty lame, isn't it? I'm sure it can be solved prettier, everything can be queried in sql, can't it?

[fenway]

You need to add c.x=1 to the ON clause.

[leafface]

Oh joy, thank you very much!  (Funny enough i remember trying to add it also to the on clause, well looks like i didn't. )