SQL Query Across Multiple Tables

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 2005 Forums

Transact-SQL (2005)

SQL Query Across Multiple Tables

Author

Topic

mastergaurav
Starting Member

2 Posts

Posted - 2009-04-09 : 11:29:45

Hi,

This may be a simple one for the experts... but a little tough for me :)

Scenario:

There are 4 tables, say:

1. Entity1
( uid int Identity PK, value nvarchar )
2. Entity2
( uid int Identity PK, valur nvarchar )
3. Entity1Entity2Map
(uid int Identity PK, Entity1ID int, Entity2ID int)
4. Entity1Entity2Usage
(uid int Identity PK, Entity1ID int, Entity2ID int, Count int)

What I want is this:

- I have a value (uid) for Entity1 table
- Entries will exist for the mapping in table Entity1Entity2Map
- I want a list of (uid) all for entries in Entity2 table where:
a) If entry exists in Entity1Entity2Usage, give also the value of Count
b) If entry does not exist in Entity1Entity2Usage, give a value of 0

What I wrote is as follows:

SELECT ETwo.*
FROM Entity2 As ETwo
JOIN Entity1Entity2Map As E1E2Map
ON E1E2Map.Entity2ID = ETwo.uid
JOIN Entity1 As EOne
ON E1E2Map.Entity1ID = EOne.uid
JOIN Entity1Entity2Usage As E1E2Usage
ON E1E2Usage.Entity1ID = EOne.uid AND E1E2Usage.Entity2ID = ETwo.uid
WHERE EOne.[Value] = 'some value'
ORDER BY E1E2Usage.[Count]

However the issue is if there is no entry in Entity1Entity2Usage table, I do not get any result.

Not sure, but I suspect that I may have to use "UNION"... but how?

Thanks in advance.

-Gaurav
[url]twitter.com/mastergaurav[/url]

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-04-09 : 11:56:55

Try LEFT JOIN and ISNULL (Or COALESCE) :)

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

mastergaurav
Starting Member

2 Posts

Posted - 2009-04-09 : 13:46:10

quote:
Originally posted by DonAtWork

Try LEFT JOIN and ISNULL (Or COALESCE) :)

Thanks a ton!
Mission accomplished using LEFT JOIN!

-Gaurav
[url]http://twitter.com/mastergaurav[/url]

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-04-09 : 14:21:50

Welcome. Make sure you are dealing with any NULLs that you get.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources