Comparing Three tables - SQL Server Forums

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

Comparing Three tables

Author

Topic

Kotti
Posting Yak Master

129 Posts

Posted - 2009-05-22 : 10:41:46

Hi Friends

I had three tables

Users,Transactions ,TransactionContact

User Table Has
Userid Company
1 AAA
2 BBB
3 CCC
Transaction Table has

TransactionId Transaction
1 asdf
2 zxcv
3 qwer

Transactioncontact table has

TcId TransactionId Userid
1 2 1
2 3 3
3 1 2

when i search the company AAA ,its Userid is 1 ,and i take the
userid 1 and check in Transactioncontact table, i can get Transactionid as 2 ,so i need to display the transactionid 2 details from transaction table.

Please help me how to get the result.

Thanks in Advance

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-05-22 : 11:17:57

SELECT Transaction
FROM Transaction t
JOIN TransactionContact tc ON tc.TransactionID = t.TransactionID
JOIN User u on u.Userid = tc.UserID
WHERE u.Company = @searchedcompany

Some days you're the dog, and some days you're the fire hydrant.

Kotti
Posting Yak Master

129 Posts

Posted - 2009-05-22 : 11:26:35

Hi Skorch
Thank you for your reply.
I am getting Wrong Answer when i use your query.

Please help me regarding this

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-05-22 : 11:29:28

What answer are you getting? And what are you expected results?

Some days you're the dog, and some days you're the fire hydrant.

Kotti
Posting Yak Master

129 Posts

Posted - 2009-05-22 : 11:34:06

I am getting No Results

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-05-22 : 11:38:50

[code]DECLARE @Users TABLE (UserID int, Company varchar(30))
INSERT INTO @Users
SELECT 1, 'AAA' UNION ALL
SELECT 2, 'BBB' UNION ALL
SELECT 3, 'CCC'

DECLARE @Transaction TABLE (TransactionID int, [Transaction] varchar(30))
INSERT INTO @Transaction
SELECT 1, 'asdf' UNION ALL
SELECT 2, 'zxcv' UNION ALL
SELECT 3, 'qwer'

DECLARE @TransactionContact TABLE (TCID int, TransactionID int, UserID int)
INSERT INTO @TransactionContact
SELECT 1, 2, 1 UNION ALL
SELECT 2, 3, 3 UNION ALL
SELECT 3, 1, 2

SELECT [Transaction]
FROM @Transaction t
LEFT JOIN @TransactionContact tc ON tc.TransactionID = t.TransactionID
JOIN @Users u on u.Userid = tc.UserID
WHERE u.Company = 'AAA'[/code]

See if this helps.

Some days you're the dog, and some days you're the fire hydrant.

Kotti
Posting Yak Master

129 Posts

Posted - 2009-05-22 : 11:56:13

Now also , i am getting no results.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-22 : 12:10:56

If you run the code given by Skorch you do not get any results? The code that is posted shoudl work:

SELECT [Transaction]
FROM @Transaction t
INNER JOIN @TransactionContact tc ON tc.TransactionID = t.TransactionID
JOIN @Users u on u.Userid = tc.UserID
WHERE u.Company = 'AAA'

Is there something else going on with your data or did you not present the whole issue?

EDIT: I changed the LEFT JOIN to an INNER JOIN because the restriction turns it into an inner join.

Kotti
Posting Yak Master

129 Posts

Posted - 2009-05-22 : 13:09:29

Hi Lamprey

I used your query too ,but there is no result.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-05-22 : 13:13:29

You're obviously leaving out some data or part of your table structure. If you run the query I posted it runs just fine and returns 'zxcv'.

Some days you're the dog, and some days you're the fire hydrant.

Kotti
Posting Yak Master

129 Posts

Posted - 2009-05-23 : 00:42:26

Sorry Friends

I just missed one thing,Now i am getting results.

Thank you for all your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources