need help to correctly select data from 2 tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

need help to correctly select data from 2 tables

Author

Topic

greenEmerald
Starting Member

4 Posts

Posted - 2013-09-10 : 15:31:24

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
Aged Yak Warrior

550 Posts

Posted - 2013-09-10 : 16:46:08

[code]

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

[/code]

greenEmerald
Starting Member

4 Posts

Posted - 2013-09-11 : 10:42:02

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.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-09-11 : 11:01:09

[code]

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

[/code]

greenEmerald
Starting Member

4 Posts

Posted - 2013-09-11 : 11:26:35

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.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-09-11 : 12:14:23

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

greenEmerald
Starting Member

4 Posts

Posted - 2013-09-12 : 11:53:45

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources