Need Help - I Have Select Distict Problems

I'm having an issue with the following sql statement:

1SELECT Property.PropertyID, Property.ListingEndDate, Property.ListingName, Property.StreetAddress, Property.City, Property.SalePrice,2 Media.ThumbView3FROM PropertyLEFTOUTER JOIN4 MediaON Property.PropertyID = Media.PropertyID

I'd like to be able to select by the distinct Media.PropertyID instead of the Property.PropertyID.

Hi,

would you like to select only distinct Media.PropertyID only or distinct Media.PropertyID, Property.ListingEndDate, Property.ListingName, Property.StreetAddress, Property.City, Property.SalePrice, Media.ThumbView?

either way, just including a distinct keyword infront of your select query would be enough:

select distinct Media.PropertyID, Property.ListingEndDate, Property.ListingName, Property.StreetAddress, Property.City, Property.SalePrice, Media.ThumbView from ....

if propertyID is a repeating field you can use the GROUP BY clause to get what you want...

select Media.PropertyID, max(Property.ListingEndDate), max(Property.ListingName), max(Property.StreetAddress), max(Property.City) ...
from ...
GROUP BY Media.PropertyID

Hope this helps...

|||

I don't understand what I'm doing wrong with these select statements.

1SELECT2 Property.PropertyID,3COUNT(Property.PropertyID)AS PropertyCount,4SUM(CAST(Units.SpaceAvailableAS int))AS TotalSpace5FROM6 Property7INNERJOIN8 UnitsON Property.PropertyID = Units.PropertyID9GROUP BY10 Property.PropertyID

I get back:

PropertyID = 48

PropertyCount = 4<-- Not what I'm expect. I'm looking for: PropertyCount = 1

TotalSpace = 12500

What am I doing wrong here? 

|||

I take it I should have used a sub select statement.

- lot's to learn...

|||

eterry28:

I take it I should have used a sub select statement.

Hi,

im assuming you want only rows where PropertyCount = 1
you can simply use the HAVING clause:

SELECT
Property.PropertyID,
COUNT(Property.PropertyID)AS PropertyCount,
SUM(CAST(Units.SpaceAvailableAS int))AS TotalSpace
FROM
Property
INNERJOIN
UnitsON Property.PropertyID = Units.PropertyID
GROUP BY
Property.PropertyID
HAVING
COUNT(Property.PropertyID) = 1

Hope this helps...