Need help with query
-
Hi, I"m having some trouble getting the result desired.
I'd like to display all the results from TableA and specially mark the rows that exist from TableB
The search would include a reference to a specific UserID (ex. 100)
TableA
rowID Data
---------------------
1 Red
2 Blue
3 Green
4 Black
TableB
UserID ColorID
------------------------
100 2
100 3
101 2
101 4
102 1
The desired result would look similar to this
(NULL values are not required for the rows in which the data does not match, 0 or some other value is ok too)
rowID Data UserID
-------------------------
1 Red NULL
2 Blue 100
3 Green 100
4 Black NULL
Any help would be greatly appreciated
Rick Hooker
-
Use LEFT JOIN
Rajesh
-
I'm thinking you can do something like this....
select a.rowid, a.data, b.userid
from tablea a
left join tableb on b.colorid=a.rowid and b.userid=100
..you could pass in a variable for the 100
-
Scott & Rajesh
Thanks for your input.
I almost had it after your comment Rajesh, but I was still a few steps away.
Scott, thanks for your post, I was able to get the desired result quickly
Thank you both for putting me on the right track
Rick Hooker
-
Try this, not sure this is what you wanted.
Select a.RowId, A.data,
case
when A.UserId <> 100 then 0
else UserId
end as UserId
from (
Select a.RowId,A.Data,b.userId from TableA a
inner Join TableB b On a.rowID = b.ColorID
where b.UserId = 100
Union all
Select a.RowId,A.Data,b.userId from TableA a
Inner Join TableB b On a.rowID = b.ColorID
where b.UserId <> 100 and A.Data Not in (
Select A.Data from TableA a
inner Join TableB b On a.rowID = b.ColorID
where b.UserId = 100 )
) a order by 1
Rajesh
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply