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 |
|
sparrow37
Posting Yak Master
148 Posts |
 Posted - 2010-06-21 : 14:18:46
|
| Hi All:I have two tables. Users and users history. User history has many records for a user and date and type of users. I want to get users between two dates from history table having type = 4 and usernames which are in primary user table. When I use inner join or outer join, I get multiple rows with same username (from user) and different dates and types (from history table). How Can I get one username, one type and one date ( probably top 1) from history table for each user. There are other tables iunvolved in query but have left joins with user table based on user id.select username,userhistory.id,type,date from usersinner join userhistoryon user.userid = userhistory.useridleft join table 1 left join table 2 left join table 3Regards,Asif Hameed |
|
|
tosscrosby
Aged Yak Warrior
676 Posts |
Posted - 2010-06-21 : 16:18:31
|
| You do not have where/on conditions on your left joins, so there is no telling what you'll get. At a minimum, all history data where a user exists. Hard to give a solution without DDL, sample data and expected results based on the sample data.Terry-- A word to the wise ain't necessary - it's the stupid ones that need the advice. -- Bill Cosby |
 |
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-06-21 : 16:23:02
|
Also, this condition seems to be missing from the query. Not sure if you have it in your original query, but this could be a reason for multiple rows. quote:
users between two dates from history table having type = 4
|
|
|
|
|
|
|
how to remove duplication