Pure SQL for this (1 Viewer)

[Mihail]

Hi everybody !
I have attached a very simple database that contain only 2 tables:

Houses
ID_House (PK , AutoNumber)
House (Text)

Bulbs
ID_Bulb (PK , AutoNumber)
ID_House (FK on Houses)
Bulb (text)
HasLight (Yes/No)

If (all bulbs in a house have light) OR (all bulbs in a house have not light) Then
all is OK, nothing to do.

And now, the question:
I am looking for SQL string that I can paste in a report Record Source in order to show me that houses where [some bulbs (at least one) have light] AND [some bulbs (at least one) have not light].
The report must show all the bulbs in those houses, grouped on houses.

The best is to see the attached database because I can't explain very well in English.

Note please that I can manage this (I have a solution based on an external function and more than one query) but I am looking for pure SQL (if it is possible) to manage things "at once" in a single query (SQL).

Thank you !

 

[Galaxiom]

SELECT B.ID_House, House, Bulbs.ID_Bulb
FROM ((
       SELECT ID_House
       FROM (
             SELECT ID_House, HasLight FROM Bulbs GROUP BY ID_House, HasLight
             )  AS A
            GROUP BY ID_House
            HAVING Count(ID_House) = 2
           ) AS B
INNER JOIN Houses
ON Houses.ID_House=B.ID_House)
INNER JOIN Bulbs
ON Bulbs.ID_House=B.ID_House;

 

[Mihail]

Somehow... it work
Now, let me alone few weeks (or month) until I understand this and I'll be able to adapt it to my needs. :banghead:

Thank you.

 

[Galaxiom]

Working outwards through the subqueries:

This subquery returns one entry for each house and bulb HasLight combination.

SELECT ID_House, HasLight FROM Bulbs GROUP BY ID_House, HasLight

Then from that we select the houses that have two records meaning they have at least one HasLight and one Not HasLight.

SELECT ID_House
       FROM (
             SELECT ID_House, HasLight FROM Bulbs GROUP BY ID_House, HasLight
             )  AS A
            GROUP BY ID_House
            HAVING Count(ID_House) = 2
           ) AS B

Then we join that back to the Houses table to get the name of the House.
Then we join that to the Bulbs table to get a list of the bulbs in that house.

The bulbs are joined in at the end because if they were in the earlier groupings they would return a record for each ID_Bulb.

 

[Galaxiom]

BTW This line can be substituted with a SELECT DISTINCT
SELECT ID_House, HasLight FROM Bulbs GROUP BY ID_House, HasLight

It is more elegant but I suspect is much the same for the engine to process.

SELECT B.ID_House, Bulbs.ID_Bulb
FROM ((
     SELECT ID_House 
     FROM (
          SELECT DISTINCT ID_House, HasLight FROM Bulbs
          )  AS A 
          GROUP BY ID_House 
          HAVING Count(ID_House) = 2
     )  AS B
INNER JOIN Houses
ON Houses.ID_House=B.ID_House)
INNER JOIN Bulbs 
ON Bulbs.ID_House=B.ID_House;

 

[Mihail]

Thank you very much !
Now my top bulb has a little light.
Thank you again !