Select data from multiple tables with relations [closed]

Question

You have all been great help in the past and I'm struggling with something so I have a question.

I have 3 tables in my SQL databas called 'Child', 'Parents' and 'Family'. The structure for the tables is shown below.

Here is some example data in the parents, Child and family table respectively.

Family.ChildID corresponds with ID in the Child table so Child.ID is the primary key and Family.ChildID is the foreign key. The same applies for the Family.ParentID and parents.ID

What I want to do is select all the fields in the child table and parents table and select the "Relation" field in the family table. However the condition is that I provide the ChildID and I want to select the corresponding parent by matching the child id and parent id in the family table.

Example:

SELECT * 
FROM Parents, Child, Family 
WHERE (Child.ID = 1 AND (Family.ChildID = 1 AND Parents.ID = Family.ParentID)

I hope that made some sense.. If you have any questions please let me know.

Thanks

Answer 1

You want to use JOIN to link the tables together. You need to join the child table to the family table and the family table to he parent table. Something like:

Select c.*, p.*, f.relation from child c
    Left Join family f on f.child_id = c.id
    Left Join parent p on f.parent_id = p.id
    Where c.id = 1

Answer 2

Try this:

SELECT Parents.*, Child.*, Relation
FROM FAMILY
INNER JOIN Parents ON Family.ParentID = Parents.ID
INNER JOIN Child ON Family.ChildID = Child.ID

You may need to use LEFT OUTER JOIN instead of INNER.