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 Link to comment https://forums.phpfreaks.com/topic/259387-newbie-needs-help-with-complex-query/ 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 ! Link to comment https://forums.phpfreaks.com/topic/259387-newbie-needs-help-with-complex-query/#findComment-1329897 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 ! Link to comment https://forums.phpfreaks.com/topic/259387-newbie-needs-help-with-complex-query/#findComment-1329920 Share on other sites More sharing options...
fenway Posted March 25, 2012 Share Posted March 25, 2012 Where's your GROUP BY clause? Link to comment https://forums.phpfreaks.com/topic/259387-newbie-needs-help-with-complex-query/#findComment-1330969 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.