A simple (?) SQL query for someone clever

[Philip White]

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.
Thanks

[David Portas]

Re: A simple (?) SQL query for someone clever

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
--

[Philip White]

Re: A simple (?) SQL query for someone clever

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



[color=blue]
> Assuming that Visible is not nullable and contains only the values 0 and[/color]
1:[color=blue]
>
> SELECT fk_mainid
> FROM GRP
> GROUP BY fk_mainid
> HAVING MIN(visible)=1
>
> --
> David Portas
> ------------
> Please reply only to the newsgroup
> --
>
>[/color]