help on a Join Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

help on a Join Query

Author

Topic

rocker86
Starting Member

1 Post

Posted - 2009-08-20 : 10:39:36

Hi, I have 2 tables table1 & table2::

TABLE1:
________________
INDEX....VALUE

1........Apple
2........Banana
3........Peaches
4........Orange
----------------

TABLE2:
________________
INDEX....COLOR

1........Red
2........Yellow
----------------

Now, I want to give the user a view of fruits with their respective colors. For this I want to give the user 3 options based on color:
1)show fruits with RED color
2)show fruits with Yellow color
3)show fruits with any color

I tried writing the following query:

select A.VALUE,B.COLOR
from TABLE1 as A
LEFT JOIN
TABLE2 as B
ON A.INDEX=B.INDEX
WHERE B.COLOR LIKE <variable>

my <variable> is assigned the value 1)'Red' for the first scenario,'yellow' for the 2nd & '%' for the 3rd. But with '%' I only get the following output:

________________
VALUE.....COLOR

Apple.....Red
Banana....Yellow
-----------------

While this is understandable why I am getting this output, can anybody help me in correcting the query to get the following o/p for the 3rd scenario:

_________________
VALUE.....COLOR

Apple.....Red
Banana....Yellow
Peaches
Orange
-----------------

would really appreciate any help,
thanks

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-08-20 : 11:00:06

http://blog.sqlauthority.com/2009/04/06/sql-server-logical-query-processing-phases-order-of-statement-execution/

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-21 : 00:35:45

select A.VALUE,B.COLOR
from TABLE1 as A
LEFT JOIN
TABLE2 as B
ON A.INDEX=B.INDEX
--WHERE B.COLOR LIKE <variable> remove this condition in where clause
if u want the specific color mention the condition in on clause only

left join table2 as b on a.index = b.index and b.color like <variable>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources