A simple (?) SQL query for someone clever

Unfortunately I can't use the excuse of being a SQL newbie, but drawn a
blank on the following "simple" problem.
I have a table containing the following (extract) :
+--+--+--+--+
| id | fk_MainID | fk_GroupID | Visible |
+--+--+--+--+
| 4 | 158 | 1 | 1 |
| 3 | 148 | 1 | 1 |
| 5 | 150 | 1 | 1 |
| 6 | 146 | 1 | 1 |
| 7 | 127 | 1 | 1 |
| 8 | 125 | 1 | 1 |
| 9 | 117 | 1 | 1 |
| 10 | 119 | 1 | 1 |
| 11 | 128 | 1 | 1 |
| 12 | 118 | 1 | 1 |
| 13 | 105 | 1 | 1 |
| 14 | 99 | 1 | 1 |
| 15 | 102 | 1 | 1 |
| 16 | 153 | 1 | 1 |
| 17 | 157 | 1 | 1 |
| 18 | 152 | 1 | 1 |
| 19 | 149 | 1 | 1 |
| 28 | 162 | 10 | 0 |
| 25 | 160 | 1 | 1 |
| 27 | 162 | 1 | 1 |
| 26 | 160 | 10 | 0 |
| 29 | 151 | 1 | 1 |
+--+--+--+--+
I need to find the "fk_MainID" where there is a "Visible=1" value, but NOT a
"Visible=0".
For example:
SELECT fk_MainID FROM GRP WHERE Visible=1
is no good because it will select fk_MainID=162, which also has a Visible=0
row.
I can do it with a sub-select, eg.
SELECT fk_MainID FROM GRP WHERE fk_MainID NOT IN (SELECT fk_MainID FROM GRP
WHERE Visible=0)
but unfortunately the query has to be "cross platform" at least to the
extent that it will also work on MySQL, which doesn't support sub-selects in
the release 3.x versions.
Very grateful in advance for any help.
ThanksAssuming that Visible is not nullable and contains only the values 0 and 1:
SELECT fk_mainid
FROM GRP
GROUP BY fk_mainid
HAVING MIN(visible)=1
--
David Portas
--
Please reply only to the newsgroup
--|||David,
You are a very clever man and your beer is in the mail.
I was trying all combinations of WHERE conditions, and never considered this
approach.
Thanks
> Assuming that Visible is not nullable and contains only the values 0 and
1:
> SELECT fk_mainid
> FROM GRP
> GROUP BY fk_mainid
> HAVING MIN(visible)=1
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
>