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  SQL Server 2000 Forums  Transact-SQL (2000)  Inner join

Author  Topic  vreede Starting Member 8 Posts Posted - 2001-10-23 : 09:03:54 Please help I am desperate. I have one table containing all addresses to deliver Christmas cards to, and the users they come from. I need to separate this data in two sets. 1 card to 1 address and 1 card to 1 address from more users. To do this I use the following syntax using a count: SELECT [Company Name], [First Name], [Last name], Title, [Street/Post box:], Number, Postcode, City, Country, COUNT([User code]) AS amount FROM dbo.Kerst GROUP BY [Company Name], [First Name], [Last name], Title, [Street/Post box:], Number, Postcode, City, Country HAVING (COUNT([User code]) < 2) The problem is: how do I get the counted users related to the adresses again???? I used the following innerjoin: select * from kerst, dbo.onecard_one where dbo.Kerst.[Company Name] = dbo.onecard_one.[Company Name] and dbo.Kerst.[First Name] = dbo.onecard_one.[First Name] and dbo.Kerst.[last name] = dbo.onecard_one.[last Name] and dbo.Kerst.[Street/Post box:] = dbo.onecard_one.[Street/Post box:] and dbo.Kerst.[City] = dbo.onecard_one.[City] I come close but it is not the correct amount of records as the first query. I need to deliver this on friday!!!