vincej Posted March 21, 2012 Share Posted March 21, 2012 HI ! The query I need is beyond my skills to execute. Ihave tried and tried all day and failed miserably. I do know very well what I need: I have 2 tables, 'Locations' and 'Dates', see below for samples. My user can be only in 1 location on 1 date. Need a query which marries up locations with dates so I can populate an HTML table looking at bit like this: Location ID Location Date 1 Date 2 Date 3 Date 4 Notice the dates are presented horizontally. In order to accomplish this the dates field will need to go though a Group_concat and be ordered by date. There will only ever be 4 dates. Each date needs to be held in a column for searchability and identifiable by row so that it uniquely can be amended to through he HTML table. The Pickup ID's (puid) also need to be selected by GROUP_CONCAT and also ordered by Date so that the order is the same as the dates field. There will be Many locations. MANY THANKS to anyone who can help me with this - I have tried all kinds of select / Join / Group_concat statements and they all have syntax errors and so I hav realised I need superiour skills than my own ! Locations Table locationid location 1 Banff 2 Varsity 3 Canmore 4 Collingwood Dates Table Sample puid locationid dates 1 1 1331668779 2 2 1331746700 3 3 1331746641 4 4 1331746641 5 1 1381668779 6 2 1321746641 7 3 1321746641 8 4 1321746641 Quote Link to comment Share on other sites More sharing options...
vincej Posted March 21, 2012 Author Share Posted March 21, 2012 I meant to add that I am using MySqL 5.5 , cheers ! Quote Link to comment Share on other sites More sharing options...
vincej Posted March 21, 2012 Author Share Posted March 21, 2012 Ok , I'm making some progress ! However, my query is still not right. My query is giving me a concatenated string of Dates and PUID's But it is only doing it for 1 location, when I want all the locations. SELECT location, l.locationid, GROUP_CONCAT(p.puid ORDER BY p.dates) AS TheID, GROUP_CONCAT( p.dates ORDER BY p.dates) AS TheDates FROM locations l, pudates p WHERE l.locationid = p.locationid; The output I get is: location locationid TheID TheDates Collingwood 4 5,1,2,6,3,7,4,8 1331704800,1331704800,1333704800,1334704800,133570... Many Many Thanks for all your Help ! Quote Link to comment Share on other sites More sharing options...
fenway Posted March 25, 2012 Share Posted March 25, 2012 Where's your GROUP BY clause? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.