How to approach this SQL query

Question

I have data related as follows:

• A table of Houses
• A table of Boxes (with an FK back into Houses)
• A table of Things_in_boxes (with an FK back to Boxes)
• A table of Owners (with an FK back into Houses)

In a nutshell, a House has many Boxes, and each Box has many Things in it. In addition, each House has many Owners.

If I know two Owners (say Peter and Paul), how can I list all the Things that are in the Boxes that are in the Houses owned by these guys?

Also, I'd like to master this SQL stuff. Can anyone recommend a good book/resource? (I'm using MySQL).

Thanks!

Answer 1

SELECT t.name
    FROM Houses h 
        INNER JOIN Boxes b ON b.houseId = h.id
        INNER JOIN Things t ON t.boxId = b.id
        INNER JOIN Owners o ON o.houseId = h.id
    WHERE o.name = 'Peter' OR o.name = 'Paul'

By using inner joins you can combine these 4 tables with all the linked information. There is also an other way using inner select queries:

SELECT t.name
    FROM Houses h 
        INNER JOIN Boxes b ON b.houseId = h.id
        INNER JOIN Things t ON t.boxId = b.id
        INNER JOIN Owners o ON o.houseId = h.id
    WHERE h.id IN (SELECT o.housId 
                       FROM Owners o
                       WHERE o.name = 'Peter' OR o.name = 'Paul')

This query works differently (by first finding the two house ID's of Peter and Paul and then performing the join), but it has the same effect.

Hopefully these examples will help you understand SQL :)

Answer 2

This isn't tested and written on the spot:

SELECT * 
FROM 
    `things_in_boxes` AS a 
    LEFT JOIN `houses` AS b 
         on ( a.`house_id` = b.`house_id` ) 
    LEFT JOIN `owners` AS c 
         on ( b.`house_id` = c.`house_id` ) 
WHERE c.`owner_id` IN( 0, 1 )

That is the general structure I would use, where the "0, 1" in the last IN statement are the owner ids for Peter and Paul. If you wanted to do it by name, you could simply make it something like

c.`name` IN( 'Peter', 'Paul' )

As far as books, I can't really tell you, I've learned through tutorials and references.

Answer 3

Here's one approach:

SELECT * FROM Things_in_boxes t
WHERE box_id IN (
    SELECT b.id
    FROM Boxes b
    INNER JOIN Owners o
      ON (o.house_id = b.house_id)
    WHERE o.name LIKE 'Peter'
    OR o.name LIKE 'Paul'
)

Note that you don't need to join on the House table, since both the Boxes and Owners have a house id.

Answer 4

Without knowing the full structure, I will assume a structure to build a query, step by step

1. Get the IDs of the houses belonging to the owners

   select id from House where owner in ('peter', 'paul')

2. Get the boxes in those houses

   select boxid from boxes where homeid in (select id from House where owner in ('peter', 'paul'))

3. Get the things in those boxes

   select * from things where boxid in (select boxid from boxes where homeid in (select id from House where owner in ('peter', 'paul')))

This should get you what you want, but is very inefficient.

In the above method, The final query in step 3 gets the ids in each step, and stores them in temporary storage while it consumes them. This is a very slow operation in most DBMS.

The better alternative is a join. Combine all the tables and select the desired data.

   select * from things join boxes on  things.boxid =boxes.boxid join houses on boxes.houseid=house.id join owners on houses.owner=owner.ownerid where owner.name in ('peter',''paul)

Answer 5

SELECT
    Things_in_boxes.*
FROM
    Houses
    JOIN Boxes ON Houses.HouseID = Boxes.House
    JOIN Things_in_boxes ON Boxes.BoxID = Things_in_boxes.Box
WHERE
    Houses.Owner = 'Peter' OR Houses.Owner = 'Paul'

As for resources to learn from... I can't really suggest anything specific. I learnt how to use (My)SQL gradually and from a number of sources, and can't single any of them out as having been of primary importance. w3schools has OK coverage of the very basic stuff, and MySQL's own documentation (available on the web, google for it) does an OK job and is a reasonable reference for when you want to know the nitty gritty of some topic or other.

EDIT: The above answer is wrong. I had missed the stipulation that a House can have multiple Owners.

New approach: I'll assume that there is a cross-referencing table, HouseOwners, with House and Owner as foreign keys.

My first thought was this:

SELECT
    Things_in_boxes.*
FROM
    Houses
    JOIN Boxes ON Houses.HouseID = Boxes.House
    JOIN Things_in_boxes ON Boxes.BoxID = Things_in_boxes.Box
    JOIN HouseOwners ON Houses.HouseID = HouseOwners.House
WHERE
    HouseOwners.Owner = 'Peter' OR HouseOwners.Owner = 'Paul'

However, this is not quite right. If both Peter and Paul are Owners of a given house, then the things in the boxes in that house would show up twice. I think a subquery is needed.

SELECT
    Things_in_boxes.*
FROM
    Houses
    JOIN Boxes ON Houses.HouseID = Boxes.House
    JOIN Things_in_boxes ON Boxes.BoxID = Things_in_boxes.Box
WHERE
    Houses.HouseID IN (
        SELECT DISTINCT House
        FROM HouseOwners
        WHERE Owner = 'Peter' OR Owner = 'Paul'
    ) AS MySubquery

Answer 6

Peter and Paul are gay couple ? Then you should go for many-to-many relationship instead of having ownerID inside of Houses Table ie. Houses2Owners with two columns ownerID and houseID

then the query would be

select item from houses as h
left join Boxes as b on h.houseID=b.houseID
left join Things as t on b.boxID=t.boxID
left join Houses2Owners as h2o on h.houseID=h2o.houseID
left join Owners as o on h2o.ownerID=o.ownerID

Main question you should ask yourself while designing that would be whether each object will appear once ie. if there are two similar boxes with similar things in them or ie. two boxes with ski masks in them.

Then you should create tables with no relationship to parent object and also to create a table that connects two tables. This way you will avoid ski mask to appear twice for two boxes which contain that mask.