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.
| Author |
Topic |
|
joe1985
Starting Member
2 Posts |
 Posted - 2009-02-23 : 12:11:37
|
I want to apologize off the get go. I couldn't find anything related to my question mostly because I am not sure what to look for.I've got two tablesTable 1 - Users PK - UserId (int)with 10 Core columnsTable 2 - UsersValuesPK - Id (int)KeyId (int)UserId (int)Value (sql_variant)I am trying to allow reporting against the UsersValues. I want to be able to get all the Users who have specific UsersValues. If they have UsersValues.Value = 'hello' and UsersValues.Value = 'test'.The problem is that if I try doing that I get zero results. Because I am using INNER JOIN UsersValues on UsersValues.UserId = Users.UserId WHERE UsersValues.Value = 'hello' and UsersValues.Value = 'test' which can't work. I need to get a collection of the UsersValues and select only the users from the User table that have both of those values in the UserValues table. I don't need exact query spelled out, just how I would go about doing this would be extremely helpful as right now I am lost. I didn't even know what to put for the title  Thanks for any feedback. |
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2009-02-23 : 13:22:52
|
try this,select * from users where exists (select 1 from UsersValues where UserId =users.UserId and Value = 'hello' )and exists (select 1 from UsersValues where UserId =users.UserId and Value = 'test' ) |
 |
|
|
joe1985
Starting Member
2 Posts |
Posted - 2009-02-23 : 14:32:19
|
| Thank you so much! That worked perfectly, and it is very elegant compared to what I was trying to come up with. |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2009-02-23 : 15:06:07
|
| np |
|
|
|
|
|
|
Multiple Selects