CODEWITHSUNDEEP
sql-servert-sql
Malfist
Malfist

Reputation: 31815

SQL subqueries 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.

Upvotes: 0

Views: 217

Answers (6)

Malfist
Malfist

Reputation: 31815

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)

Upvotes: 0

Tomalak
Tomalak

Reputation: 338416

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.

Upvotes: 2

pgb
pgb

Reputation: 25011

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.

Upvotes: 3

Alex Martelli
Alex Martelli

Reputation: 882781

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...

Upvotes: 0

dpmattingly
dpmattingly

Reputation: 1321

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

Upvotes: 1

Garrett
Garrett

Reputation: 1790

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

Upvotes: 0

Related Questions