By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,267 Members | 1,861 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,267 IT Pros & Developers. It's quick & easy.

Join results from multiple tables into 1 row

P: 5
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
Jul 2 '08 #1
Share this Question
Share on Google+
10 Replies


10K+
P: 13,264
In your example output above, what are the headings for those columns (i.e what are those numbers in columns 2, 3, ... )?
Jul 3 '08 #2

P: 5
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
Jul 3 '08 #3

10K+
P: 13,264
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
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?
Jul 3 '08 #4

P: 5
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.
Jul 3 '08 #5

10K+
P: 13,264
Yes, this is just the job for a front end language. Which language are you using for the front end.
Jul 3 '08 #6

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

Thanks
Jul 3 '08 #7

10K+
P: 13,264
Which language are you using for the front end: PHP.

Thanks
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.
Jul 3 '08 #8

P: 5
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.
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
Jul 3 '08 #9

10K+
P: 13,264
If you want to use one query, then you can use

Expand|Select|Wrap|Line Numbers
  1. select u.id, u.firstname, u.lastname, t.topic from topics t , user u
  2.  
  3. where t.fkUser = u.id
  4. order by u.id
  5.  
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>
Jul 4 '08 #10

pbmods
Expert 5K+
P: 5,821
Heya, Phillipe.

Assuming your IDs are ordered (see previous post), you should be able to do something like this:
Expand|Select|Wrap|Line Numbers
  1. $lastID = -1;
  2. foreach( $resultset as $row )
  3. {
  4.   if( $row->id != $lastID )
  5.   {
  6.     $lastID = $row->id;
  7.     // Output the name.
  8.   }
  9.  
  10.   // Output the rest of the data.
  11. }
  12.  
Jul 7 '08 #11

Post your reply

Sign in to post your reply or Sign up for a free account.