Join results from multiple tables into 1 row

Hi, hope you can help with this query as it's had me stuck for quite a while.

I have two tables:

user table
-----------------
id (primary key)
firstname
lastname

topics table
-----------------
id
fkUser
topic

Each entry in the profile table can have multiple entries in the topics table. The 'fkUser' field is linked to the profile table 'id' field.

If I run the following query, and the user has more than 1 entry in the topics table, looping through the results repeats the user details as many times as there are entries in the topics table. So if user 'John Smith' has 4 entries in the topics table, his name is printed 4 times.

Select user.firstname, user.lastname, topics.topic
FROM user
LEFT JOIN topics ON topics.fkUser = user.id

The question is, how could I return a result set which gives me 1 row per user, containing all of the topics they have entries for. Each results row would look like:

John | Smith | 3 | 9 | 2 | 4
David | White | 2 | 5
Peter | Brown | 1 |

Hope someone can help, many thanks.

P

re: Join results from multiple tables into 1 row

In your example output above, what are the headings for those columns (i.e what are those numbers in columns 2, 3, ... )?

re: Join results from multiple tables into 1 row

Thanks for your response! The numbers represent entries in the 'topic' field.

Here's an example of the user table:

id | firstname | lastname
1 | John | Smith
2 | David | White
3 | Peter | Brown

This is how the 'topics' table would look based on the numbers I specified:

id | fkUser | topic
1 | 1 | 3
2 | 1 | 9
3 | 1 | 2
4 | 1 | 4
5 | 2 | 2
6 | 2 | 5
7 | 3 | 1

re: Join results from multiple tables into 1 row

So you want to return rows as columns? I don't think that's possible.
e.g What would be the number of columns returned?

re: Join results from multiple tables into 1 row

What would be the number of columns returned? That would be different for every user. Using my previous query, i would want it to return 6 columns for John, 4 for David and 3 for Peter.

If this 1 row results set isn't possible, is there another work around? I'll explain how I want to present the results using html below and maybe there's another way?

Currently I loop through my results and print in a HTML list, based on my query, the results look like :

John Smith
John Smith
John Smith
John Smith
David White
David white
Peter Brown

I only want 'John Smith' and 'David White' to be printed once, rather than the number of times they have entries in the topic table.

My alternative, to get the results I want is to query the user table and loop through the results. Then on every loop, query the topics table to return a results set for each user. That doesn't seem to efficient as I could end up running a query for every user.

Hope that makes sense? Many thanks.

re: Join results from multiple tables into 1 row

Yes, this is just the job for a front end language. Which language are you using for the front end.

re: Join results from multiple tables into 1 row

Which language are you using for the front end: PHP.

Thanks

re: Join results from multiple tables into 1 row

I'll move this thread to the PHP forum then.

My .02 cents on it say :
Create a function called getTopics($user_Id) that returns an array of topics given a user id.
Your function that creates the table then simply loops through all the users, gets their topics and displays them. I'm sure you can try some code for it now.

re: Join results from multiple tables into 1 row

If I have understood correctly - you are saying that for every user you call the function getTopics and that function runs a query against the database to bring back the topics for that user? If so, then the system would make 1 query to get all users, then say there were 100 users, it would make 100 queries - 1 for each user?
I was hoping to make 1 query to bring back all the data I needed.

Thanks

re: Join results from multiple tables into 1 row

If you want to use one query, then you can use

You then handle the display part using PHP.
You would need to grab all the results and store them into some structure. A class will do nicely but you can also store them into an array of arrays. Your create table then loops through the structure and
for each row:

• get the user id.

• determine if that user id has been retrieved before (might need another array of processed ids for this).
• If the id has not been processed, create a new <TR> otherwise just update the current <TR>

re: Join results from multiple tables into 1 row

Heya, Phillipe.

Assuming your IDs are ordered (see previous post), you should be able to do something like this: