i wrote: > hi > how do u create a query to select two diff values of > the same column from another table using a left > join? > i know i'd retrieve them with aliases but where do i > set which alias is for which value? > > here's a simplified version of the tables > > table1: colours > id colour > > table2: choices > id old new > > old and new are both ids in the 'colours' table. > what > i wanna do is something like: > select id, colours.colour as old1, colours.colour as > new1 from choices left join colours on colours.id = > old... > now here, can an and clause be used so it reads on > as... and colours.id = new > or do i put another left join? > > either ways, how do i specify which join / equality > is > which alias or whateva? > > i don't wanna use two queries...as far as possible. >
lol @ me. i decided to use my brains for a change. didn't realise it was staring me in the face. use aliases...for tables too! here's what i've come up with, and works... select choices.id, a.colour as new, b.colour as old from choices left join colours a on a.id = choices.new left join colours b on b.id = choices.old if i put choices.id as just id, i get id is ambiguous (after making all changes to real table and column names) ;-). if 'from' is 'choices', shouldn't mysql assume it's from that itself and not one of the joined tables? abs ps: anyone with an answer to that song question? :P ________________________________________________________________________ Yahoo! Messenger - Communicate instantly..."Ping" your friends today! Download Messenger Now http://uk.messenger.yahoo.com/download/index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
