Have Geek Brain

The question about SQL subqueries

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 tblProperty.ID it is looking at. I don't 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 Query:
The query only selects properties that have rental units where tenants live.

0


a source to share


6 answers 


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


Must work.

+3


a source


Query: Select only properties that have rental units where tenants live.

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 join allows you to explicitly not select any unreferenced entries, which means it only selects properties that have rental units that have tenants.

I'm not sure why your tblTenant

links are for tblProperty

. It looks like it wasn't necessary as the link seems to be coming from the property of the tenant-> rental unit->.

+2


a source


Add the following to the end of your request. This assumes that you don't want anything to return if the counter is 1 or 0.

HAVING COUNT(tblProperty.ID) > 1
+1


a source


GROUP BY clause perhaps? SELECT To temp table and then SELECT from #tmp if easier.

0


a source


how about changing the start before SELECT tblProperty.ID

and add at the end HAVING COUNT(tblProperty.ID) > 1

? Although I admit that I do not understand your suggestions AS

- they seem completely redundant to me, each of them ...

0


a source


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)
0


a source






More articles:


All Articles