3

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.

enter image description here

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

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

Naqash Tanzeel
  • 103
  • 2
  • 8
  • Can you post some sample data and the desired result? What RDBMS are you using? – Taryn Jan 13 '13 at 22:20
  • Why are you not able to create the SQL query you want? You did soo much for your question, which actually makes me wonder what is your problem to come to a working query. It can't be the commitment or love you put into this. – hakre Jan 13 '13 at 22:20
  • @hakre I am not able to create a query because I have never worked with joining tables before.. – Naqash Tanzeel Jan 13 '13 at 22:24
  • 2
    You even now what to do (join two tables), start with two of your three tabels and do your first steps then. Check your database servers manual, it should tell you how you can join two or more tables. – hakre Jan 13 '13 at 22:26
  • @bluefeet I have added some example data. – Naqash Tanzeel Jan 13 '13 at 22:32
  • This is a very well presented example on a common problem. It should be of great help to thse learning JOINs. – Peter Wooster Jan 13 '13 at 22:43

2 Answers2

2

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
Peter Wooster
  • 6,009
  • 2
  • 27
  • 39
  • This is something I've been looking for :) If I wanted to use `ORDER BY 'row' DESC` and `LIMIT` on say table family. Where would I put this n the query? – bashleigh Jan 13 '13 at 22:32
  • Here's the MySQL select documentation. The order by goes after the where clause and the limit and offset go after that. – Peter Wooster Jan 13 '13 at 22:35
  • @PeterWooster I have tested your query and it seems to work well but there only problem is that when I execute the query and print the results it doesn't print the Child's forename and surname... only the parents.. This may be because both the table's have the same column names and the array index is defined by column name? – Naqash Tanzeel Jan 13 '13 at 22:41
  • But what if I wanted to order only the one table? so like this? `WHERE c.id=1 ORDER BY f.child_id DESC LIMIT 0,10`? – bashleigh Jan 13 '13 at 22:42
  • @Beneto You can try this.. I'm not sure if it will work Select c.* ORDER BY f.child_id DESC LIMIT 0,10, 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 – Naqash Tanzeel Jan 13 '13 at 22:45
  • You can use aliases to give different names to the name field, select p.forename as pforename... – Peter Wooster Jan 13 '13 at 22:47
  • @PeterWooster I have resolved the issue but I also have another question... I have loaded all this data into textbox's and let's say someone was to change the data and wanted to save it... Is there a single query to do that? Thanks – Naqash Tanzeel Jan 13 '13 at 22:53
  • You should probably make another question on how to do the updates. You will need to update each table independently and put all of that inside a transaction so they happen together. – Peter Wooster Jan 13 '13 at 23:10
1

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.

Sparrowhawk
  • 358
  • 1
  • 4
  • 11
  • You need to add a where on child.id since the OP wants it for a specific child, and it definitely needs a left join on child since they probably want the child info even if the child is an orphan. – Peter Wooster Jan 13 '13 at 22:38
  • @Sparrowhawk I have tested your query and it seems to work well but there only problem is that when I execute the query and print the results it doesn't print the Child's forename and surname... only the parents.. This may be because both the table's have the same column names and the array index is defined by column name? – Naqash Tanzeel Jan 13 '13 at 22:44