May 21, 2009 at 9:01 am
Hi there. I need help with a bit of T-SQL that I'm trying to use. It's intended function is that it joins a few tables in so that I can use some table identity fields...
Anywho, heres the T-SQL.
SELECT tblMedia.MediaNo, tblMedia.MediaTitle, tblRooms.RoomNo, tblRooms.RoomID, tblLocation.Location, tblLocation.LocationID, tblSubLocation.SubLocation, tblSubLocation.SublocationID
FROM tblLocation
INNER JOIN tblRooms ON tblLocation.RoomID = tblRooms.RoomID
INNER JOIN tblSublocation ON tblLocation.LocationID = tblSublocation.LocationID
INNER JOIN tblMedia ON tblRooms.RoomNo = tblMedia.Room
AND tblLocation.Location = tblMedia.Location
AND tblSubLocation.SubLocation = tblMedia.SubLocation
WHERE tblMedia.MediaNo = @MediaNo
This works perfectly when there is a value in Room, Location and SubLocation. However once a null is used in either Location or SubLocation, it returns nothing!!! Is there any way to at least retrieve the data that is there, regardless of nulls??? I am stumped.
Table Layouts.
tblMedia
=======
1 - MediaNo
2 - MediaTitle
3 - Room
4 - Location
5 - SubLocation
tblRooms
=======
1 - RoomNo (tblMedia.Room)
2 - RoomID
tblLocation
========
1 - RoomID (tblRooms.RoomID)
2 - Location (tblMedia.Location)
3 - LocationID
tblSubLocation
===========
1 - LocationID (tblLocation.LocationID)
2 - SubLocation (tblMedia.SubLocation)
3 - SubLocationID
As you can see, the layout of the room/location/sublocation is such that there is a "cascade" of information, and as the room is selected, you can filter to only the locations in the room. The goal of this part is to return the media no, title, room, roomID, location, LocationID, sublocation and sublocationID. I thought some joins would do it but I was obviously wrong!!!!!!
Many thanks!!!!!!
Tel
May 21, 2009 at 9:04 am
Try using LEFT OUTER JOIN rather than INNER JOIN. Inner join only return rows where there is a match. Left outer join returns all the rows from the table on the left and matching rows (or nulls) from the table on the right.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 21, 2009 at 9:06 am
Sorry, re-reading that I may not have been clear enough.
When you have a value in Room, Location and SubLocation in tblMedia the query runs without fault, however there is the possibility of nulls in Location and SubLocation. When there is any data missing from either, the query returns no rows (without error). Is there any way to make it return the data that is there ignoring the null?
Cheers
Tel
May 21, 2009 at 9:12 am
Can you post table definitions (as create table statements) and some sample data (as insert statements)?
Read this to see the best way to post this. http://qa.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 21, 2009 at 9:22 am
GilaMonster (5/21/2009)
Try using LEFT OUTER JOIN rather than INNER JOIN. Inner join only return rows where there is a match. Left outer join returns all the rows from the table on the left and matching rows (or nulls) from the table on the right.
My saviour!!! Worked without a hitch, many many many thanks!!!!
May 22, 2009 at 2:29 am
Ahh, slight problem I found. I re-organised the tables and the joins and have cocked it all up. Using the original statement above, I needed RIGHT OUTER JOIN as the "main" table was placed last. But if there was a null in Location, it would return a null for room as well! I re-jigged the order but the "link" between all the tables to make sure only one row is returned I don't know where to put it.
It now reads
SELECT tblMedia.MediaNo, tblMedia.MediaTitle, tblRooms.RoomNo, tblRooms.RoomID, tblLocation.Location, tblLocation.LocationID, tblSubLocation.SubLocation, tblSubLocation.SublocationID
FROM tblMedia
LEFT OUTER JOIN tblRooms ON tblMedia.Room = tblRooms.RoomNo
LEFT OUTER JOIN tblLocation ON tblRooms.RoomID = tblLocation.RoomID
LEFT OUTER JOIN tblSubLocation ON tblLocation.LocationID = tblSubLocation.LocationID
WHERE tblMedia.MediaNo = @MediaNo
However the "tie" can't just be re-inserted as it'll use tblMedia twice.
INNER JOIN tblMedia ON tblRooms.RoomNo = tblMedia.Room
AND tblLocation.Location = tblMedia.Location
AND tblSubLocation.SubLocation = tblMedia.SubLocation
As you can see, this makes sure that it doesn't return many rows for one media number
I apologise if this seems basic. I can't seem to get this right no matter where I put it!
Cheers
Tel
May 22, 2009 at 3:02 am
If you still need help (not quite sure if the problem's fixed or not), please post table definitions, sample data and expected results. See the article I referenced earlier for the best way to do that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply