0

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.

Improve this question
3
  • Why are you aliasing all your tables with the same name they already have? Isn't that somewhat besides the point? :) Commented May 13, 2009 at 20:33
  • When you ask these types of questions, it may be nice to know how your tables and relationships are set up, because just a quick glance at this I would say you aren't going to get more than one result because you're limiting it to a single property id (X). More so...why would you not just include the PropertyID when it only returns a single result? It seems like you are trying to make this query way too complicated. Commented May 13, 2009 at 20:34
  • @Tomalak I didn't do that, Crystal Reports did. I just left it that way. Commented May 13, 2009 at 20:35

6 Answers 6

Reset to default
3 
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

If I understand it correctly, you need a group by then. You want only the groups (groupd by tblProperty.id) that have a count of more than 1.
Actually I found a much simpler solution. Posting it in next comment
SELECT p.ID FROM tblProperty AS p LEFT OUTER JOIN tblTenant AS t ON t.PropertyID = p.ID WHERE (t.UnitID IS NOT NULL)
I think if you change the HAVEING line to > 0 you would get the proper results.
whoops, my bad, it works according to how I described the problem.
|
2

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.

Improve this answer

Comments

1

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
Improve this answer

Comments

0

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

Improve this answer

Comments

0

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

Improve this answer

1 Comment

except the Count one, crystal reports did that for me and I never took them out.
0

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)
Improve this answer

5 Comments

Could you check if my query also works? And give an answer to the question I have about your data layout? Thanks :)
Didn't give answer because you weren't the first. But I did give you a deserved upvote :)
You understand that this is bad solution, right? If you use a left join you then completely negate it by putting a refernce to that table inthe where clause unless you are asking for instances where the id is null (which gives you records in the first table and not the second). So an inner join would be better for performance as well as simpler. Also do you really have tenants living in more than one property? If not you don't need to waste time with a distinct either.
@Malfist: I'm at my 200 rep limit for today already, the up-vote did not count anymore. ;-) But why does the tenant table reference the property table? This seems like a normalization error to me...
It is, and I'm talking to my boss about that right now. It makes no sense to me. There's a few other things like this too. The project was outsourced overseas and brought back when they couldn't do it, so I got stuck with it. There are more than a few...odd things about it. And no comments.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.