Select usernam that owe money and have credit

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)

Select usernam that owe money and have credit

Author

Topic

codewzrd
Starting Member

8 Posts

Posted - 2004-11-05 : 11:15:35

[code]
table1 table2
idxPK idxFK Type Date idxPK Name
----- ----- ---- ---- ----- --------
1 1 1001 10/02/04 1 John
2 1 1002 10/20/04 2 Mike
3 1 1002 10/14/04 3 Sam
4 2 1001 10/22/04
5 3 1001 09/29/04
6 1 1001 09/09/04
[/code]
The table1 has a foreign key to table2. Type represents owe (1001, customer owes company money) or credit (1002, company overcharged customer).

The query that I want to create is to extract the Name from table 2 where that name has both a owe (1001) and a credit (1002) in a certain date range (month of october).

[code]
SELECT DISTINCT Table2.Name
FROM Table2 INNER JOIN Table1 ON
Table2.idxPK = Table1.idxFK
WHERE ???
[/code]

Result should be John

I can't do an AND in the WHERE CLAUSE "WHERE Type = 1001 AND Type = 1002". That can never be. And if I do an OR "WHERE Type = 1001 OR Type = 1002", I get all Names.

Any help would be appreciated.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-11-05 : 11:25:33

select b.name from table2 b
inner join table1 a on b.idxPK = a.idxPK and a.type = 1001
inner join table1 c on b.idxPK = c.idxPK and c.type = 1002

codewzrd
Starting Member

8 Posts

Posted - 2004-11-05 : 12:22:32

Awesome! Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources