[scottyadam]

Sorry for the really vague title but wasn't sure how sum this question up into a sentence.

family_tbl
id (INT) PK
name (VARCHAR)
location (VARCHAR)

person_tbl
id (INT) PK
name (VARCHAR)
family_id (INT) FK

apple_tbl
id (INT) PK
location (VARCHAR)

grapes_tbl
id (INT) PK
location (VARCHAR)

banana_tbl
id (INT) PK
location (VARCHAR)

What I am trying to do is make a query that will give me:

person_tbl.name, family_tbl.name, apple_tbl.location, grapes_tbl.location, banana_tbl.location

What I am doing right now is I have a loop with 4 embedded mysql queries and when I do this for a table that has around 13,000 records and growing, it is very slow...

The example above is probably a pretty bad design but just trying to get the idea here if I use an embedded query, joins or group by's to do what I need. Any ideas here?

Thanks.

[Vishal1419]

Firstly I think you dont have enough relationship between the tables
Try to Create the relationships first and then you should go for a query.

For e.g.
If you have relation of Apple_tbl,Banana_tbl,Grapes_tbl with Person_tbl
then your query should look like :

SELECT person_tbl.name, family_tbl.name, apple_tbl.location, grapes_tbl.location, banana_tbl.location FROM ((((Person_tbl INNER JOIN Family_tbl ON Person_tbl.Family_ID = Family_tbl.ID) INNER JOIN Apple_tbl ON Apple_tbl.Person_ID = Person_tbl.ID) INNER JOIN Grapes_tbl ON Grapes_tbl.Person_ID = Person_tbl.ID) INNER JOIN Banana_tbl ON Banana_tbl.Person_ID = Person_tbl.ID)

Sorry to say but this query is not tested by me but I think it should work

[Martyr2]

You usually join up tables through the use of primary/foreign keys. For instance, to link up your family to person tables you can link the family ID (PK) to the person family_id (FK). This is saying link the people in the person table to the families in the family table based on the family id that person belongs to.

select person_tbl.name, family_tbl.name from person_tbl, family_tbl where family_tbl.id = person_tbl.family_id

So here we are selecting the person's name and family names through keys they both share. Lets say we wanted to know all those people in the family who has the id of 1.

select person_tbl.name, family_tbl.name from person_tbl, family_tbl where family_tbl.id = person_tbl.family_id and family_tbl.id = 1

This would give us all people in the person table who have keys that match the family with the id of 1. Try this query first to get the hang of it.

You will quickly realize that your other tables need foreign keys to some how link them to these other tables. In other words, you are missing some relationships. How does a record in the grapes table link to a family? Or do they link to a particular person? Does one person have multiple types of grapes? Stuff like that.

[scottyadam]

Ah multiple joins, ok sounds good.

as for the missing relationships, yes, this is a bad design I just threw this example together in a few seconds to illustrate my problem. Thanks for the answers guys, this helped.

Ah multiple joins, ok sounds good.

as for the missing relationships, yes, this is a bad design I just threw this example together in a few seconds to illustrate my problem. Thanks for the answers guys, this helped.