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! Quote Link to comment 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 Quote Link to comment 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. Quote Link to comment 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? Quote Link to comment 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 Quote Link to comment Share on other sites More sharing options...
toplay Posted August 31, 2008 Share Posted August 31, 2008 Well done cooldude832! A+ Quote Link to comment 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 Quote Link to comment 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. Quote Link to comment 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 Quote Link to comment 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. 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.