Ok - this is a good one for you SQL gurus out there.

I have 2 tables with data like such...



Now what I want to do is list ALL of the items from table1 and *IF* they have a blue color - note that in an extra field, but if they do not have a blue color, just leave that field blank.

Final output should look like :
1 - Square - Blue
2 - Triangle - Blue
3 - Circle

I figture there's a LEFT JOIN in there somewhere...But a straight LEFT JOIN will give me the Red & Yellow items, if I add a WHERE color='blue' then I loose the Circle...

Is there a SQL solution to this? There HAS to be but I can't see it...

I think if you move the "color='blue'" condition into the join, instead of the where, you'll be ok... so something like:

Isn't this what you want?

If I use this bob - won't the "AND color = 'blue'" omit the non-blue circle from my list?

It should come back with a "NULL" value associated with it.

BOOYAH:!:

That did it!!!!!!

W()()T!

Bob - next time I see ya I owe you a beer.

Bob - what is the *= doing - I'm not familiar with that is that a shorcut for a join?

THIS is why I LOOOOVE SQL - that one line of code is saving me about 20 DB round trips PER page view now...

Many many developers out there stiff themselves by only learning the ins/outs of the scripting language they're using... without ever giving a thought to how it interacts with other applications/services. By allowing SQL to do all that work for you you're saving a TON of time...

SQLServer is amazingly powerful... I love working with it.

Yes that is another way of writing a left join, I believe it only works in SQL Server though[/quote]

^^^ I learned something new today

Dunno if you all noticed, but we now have a MySQL 4.1 server.

I knew there would have to be a LEFT JOIN in there somewhere. I just couldn't figure out how to get the correct clauses in there to get the right output.

In my final SQL Statement I ended up chucking the GROUP BY and added another condition - and the whole thing that WAS taking up about 12 lines of code, and extra loop and 20 or so DB hits is now actually not adding anymore code since it just replaced my initial SELECT, and I am only hitting the DB 1 time.

I know this is a really old post but I came up with a solution I think is a little more simple and can handle more situations like where you would want to join on different fields conditionally (what I was researching when I found this forum) and that is to build a couple case statements into your where clause.

WHERE
CASE
WHEN x=y THEN TableA.col1
ELSE TableA.col2
END
=
CASE
WHEN x=y THEN TableB.col1
ELSE TableB.col2
END


I'm not even sure if this would resolve the problem originally submitted, but hopefully will help someone having the problem I was having.