Filter, yet show all from right table

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

Filter, yet show all from right table

Author

Topic

lifemode
Starting Member

3 Posts

Posted - 2013-04-15 : 11:55:26

Hope someone can help...I have two tables: Table A is People, and Table B is Traits, listing a different trait with each row. I can join the two tables to get a list of people with brown eyes, but it does not include the rest of that person's traits. I would like to filter out people based on one trait (brown eyes), but have the results show all traits of that person. Can anyone help with this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-15 : 12:18:59

do you mean this?


SELECT a.*,b.*
FROM TableA a
INNER JOIN TableB b
ON b.col = a.col
INNER JOIN (SELECT col
            FROM TableB
            GROUP BY Col
            HAVING SUM(CASE WHEN trait=@yourTrait THEN 1 ELSE 0 END) >0
           )b1
ON b1.col = b.col

replace col with related columns and also @yourtrait with actually you want to filter (or use it as a parameter)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources