joining a table 3 times?

krotkruton

I'm trying to get information that can exist up to 3 times in a table, but get it in one row. Kinda hard to explain, so I'll try to give an example of what I have and then what I want it to be.

2 tables:

barlist with fields: id, bar_name
destinations with fields: barid, userid, date (in form of date("Ymd"))

//Let's pretend I have the following data in my tables:
-barlist----------
- id -- bar_name -
- 1  --  first   -
- 2  --  second  -
- 3  --  third   -
------------------

-destinations-----------------------
- barid ------ userid ---- date ----
-   1     --    1      -- 20080206 -
-   2     --    1      -- 20080206 -
-   3     --    1      -- 20080206 -
-   1     --    1      -- 20080207 -
-   3     --    1      -- 20080207 -
-   2     --    1      -- 20080208 -
------------------------------------

//What I want is a query that will join those tables with this result:
-result------------------------------------------------------------
- userid - date ----- id1 - name1 -- id2 -- name2 -- id3 -- name3 -
-   1    - 20080206 -  1  - first  - 2    - second -  3   - third -
-   1    - 20080207 -  1  - first  - 3    - third  - NULL - NULL  -
-   1    - 20080208 -  2  - second - NULL - NULL   - NULL - NULL  -
-------------------------------------------------------------------

So I'm trying to combine the ids and names of bars that a user visits on the same date. I know that there will never be more than 3 barids in the destinations table that have the same userid and date (or in other words, users can't visit more than 3 bars on the same date), so I don't have to worry about getting a 4th bar.

As a starting point, I made this query to get me all the information I need:

"SELECT up.email, up.first_name, up.last_name,
		d.barid, d.userid, d.date,
		b.id as bar_id, b.bar_name as bar_name
	FROM userprofiles up, destinations d
	LEFT JOIN barlist b ON d.barid = b.id
	WHERE up.id = '$userid' AND d.userid = up.id
	ORDER BY d.date DESC";

This query gets me the necessary information, but it doesn't group the bars together by date, so trying to convert it to a table where all the bars a user visits on a given date are in one row has been pretty difficult. Any suggestions?

rulian

try

ORDER BY d.date, b.bar_name

that should sort each bar by date

bogu

You can do it in php, I don't know if you can do it in one query ...

dkruythoff

Sorry, stared @ this one, but I can't see how to do it in one query.
You see, you'd need to join the bars table to the destinations table based on fields that don't reappear in the resultset (barid). In your result, the bar joins are somehow grouped per time & user, but in SQL they can only be joined to one row in the destinations table.
Can certainly be done in PHP tho.