itchyfeets Posted August 31, 2008 Share Posted August 31, 2008 Hi, I have had a hard time searching for clues on this, but maybe someone can help? I'm alright with basic MYSQL statements but this is beyond me: I have two tables I'd like to join but not in a straight forward manner. Table: spreads id (int, auto inc) thumb_asset (int) medium_asset (int) large_asset(int) Table: assets id (int, auto inc) path (varchar) So the idea is that a spread has three asset paths and I'd like to output a list of each spread's asset paths like so: spread 1: foo.gif, bar.jpg, moo.gif spread 2: bar.jpg, foo.gif, moo.gif But without doing loads of SELECTs in a PHP loop. It seems like someone who knows MYSQL would find this easy - please help! Link to comment https://forums.phpfreaks.com/topic/122063-solved-join-is-it-possible/ Share on other sites More sharing options...
cooldude832 Posted August 31, 2008 Share Posted August 31, 2008 something like this (Assuming the thumb_asset, medium_asset and large_asset are linking to the second table via Primary->foreign id relationship) SELECT spreads.id as row_id, thumb.path as thumb_path, medium.path as med_path, large.path as large_path FROM `spreads` LEFT JOIN `assets` as thumb ON(spreads.thumb_asset = thumb.id) LEFT JOIN `assets` as medium ON(spreads.medium_asset = medium.id) LEFT JOIN `assets` as large ON(spreads.large_asset = large.id) GROUP BY spreads.id Link to comment https://forums.phpfreaks.com/topic/122063-solved-join-is-it-possible/#findComment-630157 Share on other sites More sharing options...
fenway Posted August 31, 2008 Share Posted August 31, 2008 Um, you can't group by something and then retrieve a whole bunch of other fields. Link to comment https://forums.phpfreaks.com/topic/122063-solved-join-is-it-possible/#findComment-630494 Share on other sites More sharing options...
cooldude832 Posted August 31, 2008 Share Posted August 31, 2008 whats the problem with the query I wrote it should work fine? Link to comment https://forums.phpfreaks.com/topic/122063-solved-join-is-it-possible/#findComment-630504 Share on other sites More sharing options...
itchyfeets Posted August 31, 2008 Author Share Posted August 31, 2008 cooldude832 - that works like a dream! thankyou so much. you rock Link to comment https://forums.phpfreaks.com/topic/122063-solved-join-is-it-possible/#findComment-630586 Share on other sites More sharing options...
toplay Posted August 31, 2008 Share Posted August 31, 2008 Well done cooldude832! A+ Link to comment https://forums.phpfreaks.com/topic/122063-solved-join-is-it-possible/#findComment-630590 Share on other sites More sharing options...
cooldude832 Posted August 31, 2008 Share Posted August 31, 2008 Well done cooldude832! A+ Apparently Fenway says it doesn't work and I'm curious why Link to comment https://forums.phpfreaks.com/topic/122063-solved-join-is-it-possible/#findComment-630613 Share on other sites More sharing options...
fenway Posted September 1, 2008 Share Posted September 1, 2008 I don't see the purpose of the group by, since it's on an ID field, and will do nothing but slow down the query. And if it were on a non-unique field, the non-group'ed by fields would be meaningless. If you were running this in MySQL 5 with strict mode, mysql would complain. Link to comment https://forums.phpfreaks.com/topic/122063-solved-join-is-it-possible/#findComment-631207 Share on other sites More sharing options...
cooldude832 Posted September 1, 2008 Share Posted September 1, 2008 That is what you were complaining about now I see. I'm on mysql 4 mostly so I don't get the complaints Link to comment https://forums.phpfreaks.com/topic/122063-solved-join-is-it-possible/#findComment-631233 Share on other sites More sharing options...
fenway Posted September 1, 2008 Share Posted September 1, 2008 That is what you were complaining about now I see. I'm on mysql 4 mostly so I don't get the complaints Even so, you should set "ONLY_FULL_GROUP_BY" in the sql_mode because otherwise you'll end up with a lot of garbage. Link to comment https://forums.phpfreaks.com/topic/122063-solved-join-is-it-possible/#findComment-631238 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.