SQL subqueries question

Question

I want to Select tblProperty.ID only when this query returns greater than 0

SELECT     
    COUNT(tblProperty.ID) AS count
FROM         
    tblTenant AS tblTenant 
    INNER JOIN tblRentalUnit
        ON tblTenant.UnitID = tblRentalUnit.ID 
    INNER JOIN tblProperty
        ON tblTenant.PropertyID = tblProperty.ID 
        AND tblRentalUnit.PropertyID = tblProperty.ID
WHERE tblProperty.ID = x

Where x is equal to the parent's tblProperty.ID that it is looking at. I do not know what 'x' is.

How can I do this?

Database Structure:
tblTenant:
  ID
  PropertyID <--foreign key to tblProperty
  UnitID     <--foreign key to tblRentalUnit
  Other Data
tblProperty:
  ID
  Other Data
tblRentalUnit:
  ID
  PropertyID <--foreign key to tblProperty
  Other Data

Explanation of the query:
The query is select only the properties that have rental units that have tenants living in them.

Answer 1

SELECT     
   tblProperty.ID
FROM         
    tblTenant AS tblTenant 
    INNER JOIN tblRentalUnit AS tblRentalUnit 
        ON tblTenant.UnitID = tblRentalUnit.ID 
    INNER JOIN tblProperty AS tblProperty 
        ON tblTenant.PropertyID = tblProperty.ID 
        AND tblRentalUnit.PropertyID = tblProperty.ID
GROUP BY tblProperty.ID
HAVING COUNT(tblProperty.ID) > 1

Should work.

Answer 2

GROUP BY clause, perhaps? SELECT INTO a temp table & then SELECT from #tmp, if that's easier.

Answer 3

Add the following to the end of the query. This assumes that you do not want anything to return if the count is 1 or 0.

HAVING COUNT(tblProperty.ID) > 1

Answer 4

what about changing the start to SELECT tblProperty.ID and add at the end HAVING COUNT(tblProperty.ID) > 1? Though I admit I don't understand your AS clauses -- they seem totally redundant to me, each and every one...

Answer 5

The query is: select only the properties that have rental units that have tenants living in them.

SELECT
  p.ID
FROM
  tblProperty              AS p
  INNER JOIN tblRentalUnit AS u ON u.PropertyID = p.ID
  INNER JOIN tblTenant     AS t ON t.UnitID     = u.ID
GROUP BY
  p.ID

This should do it. The inner joins to explicitly not select any unreferenced records, that means this selects only those properties that have rental units that have tenants.

I'm not sure why your tblTenant links to the tblProperty. It looks as though this was not necessary, since the link seems to go from tenant->rental unit->property.

Answer 6

Actually, this works:

SELECT DISTINCT
     p.ID
FROM         tblProperty AS p LEFT OUTER JOIN
                      tblTenant AS t ON t.PropertyID = p.ID
WHERE     (t.UnitID IS NOT NULL)