Desired rows not returned because of null values

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)

Desired rows not returned because of null values

Author

Topic

steve48
Starting Member

6 Posts

Posted - 2010-09-26 : 19:46:09

I have a property table and a propertyimage table.
I want this query to return all distinct properties and a thumbnail imgid from the propertyimage table. However some properties don't have thumbnail images and they
don't get returned by the query. If the imgid is null I still want to return the property. Not sure of how to do this. Thanks

ALTER PROCEDURE dbo.procGetPropertiesSelect2
AS
SELECT        tblProperty.PropertyID, tblProperty.SubmitPersonKey, tblProperty.SubmitDate, tblProperty.Active, tblProperty.PropName, tblProperty.StreetAddress1, tblProperty.Storage, 
           tblPropertyImages.ImgID
FROM            tblProperty INNER JOIN
                         tblPropertyImages ON tblProperty.PropertyID = tblPropertyImages.PropertyKey
WHERE        (tblProperty.PropName IN
                             ((SELECT DISTINCT PropName
                                 FROM            tblProperty AS tblProperty_1))) 
                                 AND (tblPropertyImages.ImgID IN
                             (SELECT   TOP   1   ImgID
                               FROM            tblPropertyImages AS tblPropertyImages_1
                               WHERE        (UseForThumb = '1')))

Steven Greenbaum

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-26 : 20:51:56

Just switch it to a LEFT JOIN (which is an outer join)

...
FROM tblProperty
LEFT JOIN tblPropertyImages
...

Your WHERE clause seems a bit screwy though as you shouldn't be hitting the tblProperty table so many times. What are you trying to do with it? Perhaps we can optimize it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

steve48
Starting Member

6 Posts

Posted - 2010-09-26 : 21:37:19

Hi,
Thanks for responding. I tried the LEFT Join but it didn't solve problem. Only 1 row was returned when I want all (3).

Without the
'screwy' where clause I was getting multiple rows for the same property. I only want one row for each property. This way I got one row for each distinct property name.

Steven Greenbaum

steve48
Starting Member

6 Posts

Posted - 2010-09-26 : 22:10:46

I tried the following which almost worked.
I now get all (3) properties, even the ones wity null imgIDs.
However, The property that has an non-null imgid gets returned 3 times so I'm getting 5 rows back.

SELECT        tblProperty.PropertyID, tblProperty.SubmitPersonKey, tblProperty.SubmitDate, tblProperty.Active, tblProperty.PropName, tblProperty.StreetAddress1, 
                         tblProperty.StreetAddress2, tblProperty.CityKey, tblProperty.StateKey, tblProperty.CountryKey, tblProperty.PostalCode, tblProperty.PropType, 
                         tblProperty.YearBuilt, tblProperty.NeighborhoodKey, tblProperty.BoroughKey, tblProperty.PropertyPrivate, tblProperty.Area, tblProperty.AreaUnits, 
                         tblProperty.Bed, tblProperty.Bath, tblProperty.CoOpShares, tblProperty.CoOpPctInterest, tblProperty.CoOpMaintenance, tblProperty.CondoCC, 
                         tblProperty.CondoRET, tblProperty.FlipTax, tblProperty.Assessments, tblProperty.PropertyMinReqdPctDown, tblProperty.Storage, tblPropertyImages.ImgID, 
                         tblCountry.Country, tblState.State, tblCity2Distinct.City,
                             (SELECT        TOP (1) ImgID
                               FROM            tblPropertyImages AS tblPropertyImages_1
                               WHERE        (tblProperty.PropertyID = tblPropertyImages.PropertyKey) AND (UseForThumb = '1')) AS ImgID
FROM            tblProperty LEFT OUTER JOIN
                         tblPropertyImages ON tblProperty.PropertyID = tblPropertyImages.PropertyKey INNER JOIN
                         tblCountry ON tblProperty.CountryKey = tblCountry.CountryID INNER JOIN
                         tblState ON tblProperty.StateKey = tblState.StateID INNER JOIN
                         tblCity2Distinct ON tblProperty.CityKey = tblCity2Distinct.CityID
WHERE        (tblProperty.PropName IN
                             (SELECT DISTINCT PropName
                               FROM            tblProperty AS tblProperty_1))

Steven Greenbaum

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-26 : 22:23:11

You are going to need to post some sample data as we can't just look at your queries and know what you want them to do. Please post the table structure for the tables involved in the query, sample data for each of the tables including linking and non-linking data, and the expected result set of your query using the sample data that you provided.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

steve48
Starting Member

6 Posts

Posted - 2010-09-26 : 22:33:22

I have simplified to the following.
The only remaining problem is that one property is returning 3 rows.
I only want 1 row per propname.

ALTER PROCEDURE dbo.procGetPropertiesSelect3
AS
SELECT        tblProperty.PropertyID, tblProperty.SubmitPersonKey, tblProperty.SubmitDate, tblProperty.Active, tblProperty.PropName,  tblProperty.Storage, tblCountry.Country, 
                         tblState.State, tblCity2Distinct.City,
                             (SELECT        TOP (1) tblPropertyImages.ImgID
                               FROM            tblPropertyImages AS tblPropertyImages_1
                               WHERE        (tblProperty.PropertyID = tblPropertyImages.PropertyKey) AND (tblPropertyImages.UseForThumb = '1')) AS ImgID
FROM            tblProperty LEFT OUTER JOIN
                         tblPropertyImages ON tblProperty.PropertyID = tblPropertyImages.PropertyKey INNER JOIN
                         tblCountry ON tblProperty.CountryKey = tblCountry.CountryID INNER JOIN
                         tblState ON tblProperty.StateKey = tblState.StateID INNER JOIN
                         tblCity2Distinct ON tblProperty.CityKey = tblCity2Distinct.CityID

Steven Greenbaum

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-26 : 23:16:24

You can use the ROW_NUMBER() function for this. If you need specific help, please provide the information that I requested.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources