SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 need help to correctly select data from 2 tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

greenEmerald
Starting Member

4 Posts

Posted - 09/10/2013 :  15:31:24  Show Profile  Reply with Quote
Hello all, I am no sql pro, and need some assistance with an issue involving two tables for a select statement.

Here are the tables along with their columns(for simplicity, tables will be called 1 and 2):

table 1

- AccountID
- ID

table 2
- ID
- HomeDate (if this value IS NULL, that means the owner DOES NOT have a home on that lot)
- LotNumber

table 1 and 2 are linked by the ID column. This select statement if suppose to display all accounts that DO NOT have a home, yet own lot/s.

Here is my statement:
SELECT 1.AccountID, (SELECT COUNT(2.LotNumber)) AS lotCount
FROM 1
LEFT JOIN 2
ON 1.ID = 2.ID
AND 2.HomeDate IS NULL
GROUP BY 1.AccountID

For the owners that do not have a home, yet own a lot/s, I want their accountID displayed, along with how many lots they own.
Yet for the life of me I CANNOT get it to work :(
I know its something small but I been working on this for half the day and its so frustrating.

For those that take the time to help me out, I thank you greatly.

ScottPletcher
Constraint Violating Yak Guru

USA
412 Posts

Posted - 09/10/2013 :  16:46:08  Show Profile  Reply with Quote


SELECT 1.AccountID, COUNT(2.ID) AS LotCount
FROM 1
LEFT JOIN 2
ON 1.ID = 2.ID 
AND 2.HomeDate IS NULL
GROUP BY 1.AccountID
HAVING COUNT(2.ID) >= 1 --list those who own at least one lot


Edited by - ScottPletcher on 09/10/2013 16:47:18
Go to Top of Page

greenEmerald
Starting Member

4 Posts

Posted - 09/11/2013 :  10:42:02  Show Profile  Reply with Quote
quote:
Originally posted by ScottPletcher



SELECT 1.AccountID, COUNT(2.ID) AS LotCount
FROM 1
LEFT JOIN 2
ON 1.ID = 2.ID 
AND 2.HomeDate IS NULL
GROUP BY 1.AccountID
HAVING COUNT(2.ID) >= 1 --list those who own at least one lot




javascript:insertsmilie('')
Thanks so much for your help! It has helped me, im like 90% complete with this issue. However, accounts that have a homedate value(example: 05/22/1999) are showing up in my result set. And I want ALL accounts that have a HomeDate value to be excluded from the query,i.e. I want my result set to only show accounts that have atleast one or more lots, and if an account has 4 lots yet one of the lots has a homedate value, to exclude that account from the result set. Hope this makes sense.
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
412 Posts

Posted - 09/11/2013 :  11:01:09  Show Profile  Reply with Quote


SELECT 1.AccountID, COUNT(2.ID) AS LotCount
FROM 1
LEFT JOIN 2
ON 1.ID = 2.ID 
AND 2.HomeDate IS NULL
GROUP BY 1.AccountID
HAVING COUNT(2.HomeDate) = 0 --list only those who don't have a home

Go to Top of Page

greenEmerald
Starting Member

4 Posts

Posted - 09/11/2013 :  11:26:35  Show Profile  Reply with Quote
quote:
Originally posted by ScottPletcher



SELECT 1.AccountID, COUNT(2.ID) AS LotCount
FROM 1
LEFT JOIN 2
ON 1.ID = 2.ID 
AND 2.HomeDate IS NULL
GROUP BY 1.AccountID
HAVING COUNT(2.HomeDate) = 0 --list only those who don't have a home





Seriously thank you for your effort mate, your awesome for actually responding back to my post more than once. But its still not working right. Dont get it twisted though accounts who DO NOT have a home but own lots are displaying correctly. BUT, accounts that have a home are still showing up in my result set.
Example:
Acount # 12345
Lots owned : 3
Homedate values for each lot in sequential order:
NULL
NULL
NULL
01/07/1989

So the query is correctly counting how many lots each account owns with no homeDate value, but its still including accounts that have a HomeDate value, and I need the query to exclude any account that actually has a HomeDate value,regardless of how many lots they own. If they have a homedate value, they should NOT show up in the result set.
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
412 Posts

Posted - 09/11/2013 :  12:14:23  Show Profile  Reply with Quote
DOH, sorry, I overlooked something rather obvious. Please try the corrected query below:


SELECT 1.AccountID, COUNT(2.ID) AS LotCount
FROM 1
LEFT JOIN 2
ON 1.ID = 2.ID
--AND t2.HomeDate IS NULL
GROUP BY 1.AccountID
HAVING COUNT(2.HomeDate) = 0 --list only those who don't have a home

Go to Top of Page

greenEmerald
Starting Member

4 Posts

Posted - 09/12/2013 :  11:53:45  Show Profile  Reply with Quote
Thank you for all your effort mate! I finally understood why it wasnt working. Once i switched id = id with lotnumber= lotnumber everything lined up perfectly. Thanks again for all your effort in my issue as you helped me reach the end of my nightmare that was this issue.You are awesome.

Edited by - greenEmerald on 09/12/2013 12:01:54
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000