Joining 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

Joining three tables

Author

Topic

mnolting
Starting Member

36 Posts

Posted - 2007-06-11 : 19:27:42

I am having trouble building a query that joins three tables. The tables are:

Table------------key fields
Organizations---org_KEY (all fields start with org_)
Locations-------LOC_KEY--LOC_ORG_KEY (all fields start with loc_)
Users-----------USR_ORG_KEY----USR_LOC_KEY (all fields start with usr_)

Every location record is tied to a record in the organization table. (Via loc_key=org_key)
Each user record belongs to one organization and one location. (usr_org_key=org_key and usr_loc_key=loc_key)
Not all locations have a user. So if there is no user for any location ,I need the address info.

I need a result that is of all organizations, Locations and users
Not all locations have a user but they all have address information eg (loc_address1, loc_city, etc....)

The result will look like this:

Org_id, org_name, loc_id, loc_name, loc_address1,loc_city,usr_id,usr_name

grouped by organization, then location, then user

Thanks for all help

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-11 : 23:39:48

post sample data for your 3 tables and required output u want.

--------------------------------------------------
S.Ahamed

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-12 : 00:17:51

Something like this:

Select
o.Org_id, O.org_name, l.loc_id, l.loc_name, l.loc_address1, l.loc_city, Coalesce(u.usr_id, '') as Usr_id, Coalesce(u.usr_name, '') as usr_name
From Organizations o Join Locations l on o.org_KEY = l.org_KEY
Left Join Users u on u.USR_LOC_KEY = o.org_KEY
Order by o.Org_id, l.loc_id, u.usr_id

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

mnolting
Starting Member

36 Posts

Posted - 2007-06-15 : 22:42:39

I have not tried Harsh's solution (I am not at work right now), however here is a sample of data and output

Organizations

Org_key---Org_name
1---------Microsoft
2---------Digital Research

Locations

Loc_org_key---Loc_key----Loc_name-------loc_address
1-------------1---------ABC Software----123 main st
1-------------2---------DEF Software-----2000 Long st
1-------------3---------BOBs Software----3000 short ave
2-------------4---------Sams of Utah-----654 maple ave
2-------------5---------Dallas Ram-------999 leaf ct

Users

usr_org_key----usr_loc_key---usr_key----usr_name
1--------------1-------------1--------Bill Gates
1--------------1--------------2-------Mary Smith
1--------------1--------------3-------Mike Smith
1--------------2--------------4-------Ken Wagon
1--------------2--------------5-------Dave Spade
1--------------2--------------6-------John Denver
2--------------4--------------8-------Amy Grant
2--------------5--------------9-------Lee Grant

The report :
Microsoft

ABC Software--123 main st bill gates--mary smith--mike smith (each name in seperate records)
DEF Software--2000 long st--ken wagon---dave spade--john denver

Bobs software---3000 short ave----(no names, no one belongs to location 3)

________________________________________________________________________

Digital Research
sams of utah---654 maple ave---amy grant

dallas ram--999 leaf ct----lee grant

Of course each name is in seperate records, I cant space out the "report" here.
Again, the grouping is Organizations, then locations, then users.
Thanks
Mike

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources