SQL Query Not Quite Right - 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

SQL Server 2008 Forums

Transact-SQL (2008)

SQL Query Not Quite Right

Author

Topic

CGrady
Starting Member

2 Posts

Posted - 2011-06-21 : 07:08:51

Hi there,

This has been a very annoying problem for me as I can see that I am doing something wrong but can't quite see how to correct it. Any help would be very gratefully received.

The best way for me to describe it is to give an example.

The screenshot below shows the results of a SQL query using MS Management Studio on SQL Server 2008.

There are four tables: Properties, Garages, Gardens and Ponds.

In my query Property is joined to Garage, Garage is joined to Garden and Garden is joined to Pond using full outer join.

Garage includes the foreign key for Property, Garden includes the foreign key for Garage.

The problem is that a Pond may be connected to a Garden, a Garage or a Property. The pond table contains the foregin keys for each of them. Using the current query (because the Pond table is joined to the garden table I can only see a pond when it is connected to a garden. However I have a pond in that table that is connected to a property that isn't showing and I can't get it to work properly.

Can anyone help me show a pond when it is connected to any of the other three tables? Anything I've tried so far leads to inaccurate results.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-21 : 07:16:03

select p.name, grge.name, gdn.name, coalesce(p1.name,p2.name)
from property p
left join Garage grge
on grge.propertyID = p.propertyID
left join Garden gdn
on gdn.propertyID = p.propertyID
left join Pond p1
on p1.propertyID = p.propertyID
left join Pond p2
on p2.GardenID = p.GardenID

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-21 : 07:17:28

You can join Pond 3 times with different aliases on the different key columns.
In the select list you can use COALESCE(a.PondCol,b.PondCol,c.PondCol,'nothing') as Pond

If that isn't clear to you then please provide table structure and sample data.

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-21 : 07:18:04

Arrgh