Page 1 of 1

I don't know how to do this and have trouble explaining it:)

Posted: Mon Apr 16, 2007 10:59 am
by themaxtor
Anyone suggest an effecient way to do this - I'm not sure what I'm supposed to try next:

I have a query that looks more or less like:

SELECT * FROM mytable WHERE primary_id IN ('3,4,7')

this select is generated from a select and the 3, 4 and 7 are generated dynamically based on the actual ids chose from the select list. Anyhow, I then query all the other tables based on this id as the foreign key.

Now here is the problem, how the heck do I display this data.
I know I can just loop through to show the contents of the query for "mytable" but I want to display the data like this:

mydata->name1
othertable->info1
othertable->info2
othertable->info3

mydata->name2
othertable->info1
othertable->info2
othertable->info3


So I want to display the title for example from my main table and then all the detail info from the other tables. This would be straight forward with a single query but since I have queried as above, I really don't know what I'm supposed to do to match the data properly

Can anyone help....

Posted: Mon Apr 16, 2007 11:07 am
by Begby
SQL JOINs are you friend, learn them, use them, love them.

You should be able to do this in one query and get all of your data at once, then loop through that single record set to display your stuff.

Posted: Mon Apr 16, 2007 11:42 am
by RobertGonzalez
You can do this with with two queries as well... one query for the parents, the other for the children.

Loop the first result set (parents) and for each iteration, loop the second result set and match the ids in the second set that match the id of the parent.