Left Join, Count, WHERE clause, Headache

Question

Post reply

Left Join, Count, WHERE clause, Headache

cawleymj

SSC-Addicted

Points: 402
More actions
April 13, 2010 at 5:17 am

#233871

Hi I am just trying to do a relatively simple Count that includes a Left Join and a WHERE clause.
I'm trying to break what I'm actually doing down into a simpler problem but still can't work things out.
Say there is a table of OWNERs and PETs as follows:
OWNER
Owner_id (primary key)
Owner_Name
PET
Pet_id (primary key)
Pet_Name
Pet_Age
Owner_id (foreign_key)
I need the query to output:
1) ALL the OWNERS
(so that's where the LEFT JOIN comes into play i think)
2) Count how many pets they all have that are Age > 4
So this must display 0 values for anyone who either
a) doesn't have any pets in the PET table
(P.S. Don't ask why they are an owner if they don't have a pet, this is only a simplified example)
b) has pets in the PETS table but they are NOT aged > 4
I hope that makes sense. I am sure I am overlooking the obvious in trying to do this.
I think i need to create a view but I'm so confused now I need medicine or something stronger!
Thanks
Matt

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Luzi62 Ten Centuries Points: 1218 More actions · Answer 1

Luzi62

Ten Centuries

Points: 1218

April 13, 2010 at 5:31 am

#1150621

what is exactly the problem?

the subselect?

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

Hi Buddy,how about you going through this following article and helping us help you?? 🙂

CLICK HERE FOR FORUM POSTING ETIQUETTES - JEFF MODEN[/url]

When u do so, i am sure a lot of us will help u instantly...

So please post

1. Table Structures - CREATE TABLE scripts

2. Constraints or Indexes in the table, - CREATE SCRIPTS

3. SAMPLE DATA - INSERT INTO TABLE scripts

4. Desired output - some visual representation of this.

Cheers!

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 3

Does this help..

SELECTO.OwnerID, O.Owner_Name, COUNT( P.Pet_id ) [Count]

FROMOWNER O

LEFT OUTER JOINPET P

ON O.Owner_id = P.Owner_id

AND P.Pet_Age > 4

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 4

Hey buddy, i really had free time, so i created all the DLLs and some sample rows..

AND, the QUERY for you as wel..

DDLs

--===== CREATE TABLES

IF OBJECT_ID('OWNERS') IS NOT NULL

DROP TABLE OWNERS

CREATE TABLE OWNERS

(

OWNER_ID INT IDENTITY(1,1) CONSTRAINT PK_OWNER_ID_OWNERS PRIMARY KEY,

OWNER_NAME VARCHAR(100)

)

IF OBJECT_ID('PETS') IS NOT NULL

DROP TABLE PETS

CREATE TABLE PETS

(

PET_ID INT IDENTITY(1,1),

PET_NAME VARCHAR(100),

PET_AGE SMALLINT,

OWNER_ID INT CONSTRAINT FK_OWNER_ID_PETS_OWNER_ID_OWNERS

FOREIGN KEY REFERENCES OWNERS(OWNER_ID)

)

--===== INSERT SOME SAMPLE DATA

INSERT INTO OWNERS

SELECT 'SACHIN'

UNION ALL

SELECT 'TIGER WOODS'

UNION ALL

SELECT 'ROGER FEDERER'

--===== INSERT SOME SAMPLE DATA

INSERT INTO PETS

SELECT 'SACHIN_PET1' , 1 , 1

UNION ALL

SELECT 'SACHIN_PET2' , 5 , 1

UNION ALL

SELECT 'SACHIN_PET3' , 4 , 1

UNION ALL

SELECT 'TIGER WOODS_PET1' , 9 , 2

UNION ALL

SELECT 'TIGER WOODS_PET2' , 8 , 2

UNION ALL

SELECT 'TIGER WOODS_PET3' , 2 , 2

--===== SAMPLE ROWS CHECK

SELECT * FROM OWNERS

SELECT * FROM PETS

THE QUERY

--===== THE QUERY TO GET THE COUNT

SELECT

O.OWNER_ID

,O.OWNER_NAME

,COUNT(CASE WHEN P.PET_AGE >= 4 THEN P.PET_AGE END ) PET_COUNT

FROM

OWNERS O

LEFT JOIN

PETS P

ON

O.OWNER_ID = P.OWNER_ID

GROUP BY

O.OWNER_ID, O.OWNER_NAME

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

cawleymj (4/13/2010)
Hi I am just trying to do a relatively simple Count that includes a Left Join and a WHERE clause.
I'm trying to break what I'm actually doing down into a simpler problem but still can't work things out.
Thanks
Matt

-- sample data

DROP TABLE #OWNER

CREATE TABLE #OWNER (Owner_id INT IDENTITY (1,1), Owner_Name VARCHAR(20))

INSERT INTO #OWNER (Owner_Name)

SELECT 'Roy Hattersley' UNION ALL

SELECT 'Paris Hilton' UNION ALL

SELECT 'Ranulph Fiennes'

DROP TABLE #PET

CREATE TABLE #PET (Pet_id INT IDENTITY (1,1), Pet_Name VARCHAR(20), Pet_Age INT, Owner_id INT)

INSERT INTO #PET (Pet_Name, Pet_Age, Owner_id)

SELECT 'Charley', 2, 1 UNION ALL

SELECT 'Farley', 4, 1 UNION ALL

SELECT 'Barley', 4, 1 UNION ALL

SELECT 'Tinky', 4, 2 UNION ALL

SELECT 'Winky', 4, 2 UNION ALL

SELECT 'Woo', 4, 2

-- /sample data

-- query

SELECT

o.Owner_id,

o.Owner_Name,

OldMuttCount = COUNT(p.Pet_id)

FROM #OWNER o

LEFT JOIN #PET p ON p.Owner_id = o.Owner_id AND p.Pet_Age = 4

GROUP BY o.Owner_id, o.Owner_Name

ORDER BY COUNT(Pet_id) DESC

Looks like you're just starting out with TSQL Matt. Check out COUNT() and aggregates, and LEFT JOIN in Books On Line (BOL). If you are unsure how and why the count works, just ask.

EDIT: corrected age filter

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 6

Kingston Dhasian (4/13/2010)
Does this help..
SELECTO.OwnerID, O.Owner_Name, COUNT( P.Pet_id ) [Count]
FROMOWNER O
LEFT OUTER JOINPET P
ON O.Owner_id = P.Owner_id
AND P.Pet_Age > 4

Kingston's code will also work but for a small change..you will have to Group By some columns... guess kingston is not near his SSMS and have not compiled the code :hehe:..

Here is the changed code ; (Look out for the comments if u want to know where i have changes )

SELECT

O.OWNER_ID

,O.OWNER_NAME

,COUNT(P.PET_ID) PET_COUNT

FROM

OWNERS O

LEFT JOIN

PETS P

ON

O.OWNER_ID = P.OWNER_ID AND P.PET_AGE >= 4

--== HERE IS WHERE KINGSTON MISSED OUT ;)

GROUP BY

O.OWNER_ID, O.OWNER_NAME

Pulivarthi Sasidhar SSCertifiable Points: 6706 More actions · Answer 7

hi,

SELECT owname,CASE WHEN age>4 Then PetCount else 0 END as PtCoun from

( SELECT O.owname,O.owner_id,age, COUNT(Pet_id) PetCOunt from owners O LEFT JOIN pets P on O.owner_id=P.owner_id

GROUP BY O.owname,O.owner_id,age)A

or

SELECT O.owname,O.owner_id,age, COUNT(Pet_id) PetCOunt from owners O LEFT JOIN pets P on O.owner_id=P.owner_id AND age>4

GROUP BY O.owname,O.owner_id,age

:-):-)

cawleymj SSC-Addicted Points: 402 More actions · Answer 8

Thanks all, yeah Kingston's code has done it for me.

Never knew you could do the filter as part of the join. That was something I never even considered! So that's news to me and a lesson learnt.

Makes sense though I guess - Making all rows that are Not (Age > 4) NULLs. THEN doing the count... haha bingo

Knew it was something simple

Thanks again. Sure I will be back with more questions in the future!

Matt

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 9

Hmmm.. cool.. but before u could post question, i hope that u had looked over that link i specified in my earlier post on how to post question to get fast and tested code back...

Cheers!!

cawleymj SSC-Addicted Points: 402 More actions · Answer 10

COldCoffee (4/13/2010)
Hmmm.. cool.. but before u could post question, i hope that u had looked over that link i specified in my earlier post on how to post question to get fast and tested code back...
Cheers!!

Yeah sure, good policy, I will do that for more complicated posts. I knew this was something relatively simple and that I could explain the problem in such a way that someone would be able to post a quick reply.

I'm working on another one now, this one can't really be simplified so I might have to post the full DDL for this next one unless I can sort it out quickly myself! :w00t:

cawleymj SSC-Addicted Points: 402 More actions · Answer 11

COldCoffee (4/13/2010)
Hmmm.. cool.. but before u could post question, i hope that u had looked over that link i specified in my earlier post on how to post question to get fast and tested code back...
Cheers!!

Yeah sure, good policy, I will do that for more complicated posts. I knew this was something relatively simple and that I could explain the problem in such a way that someone would be able to post a quick reply.

I'm working on another one now, this one can't really be simplified so I might have to post the full DDL for this next one unless I can sort it out quickly myself! :w00t:

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 12

COldCoffee (4/13/2010)
Kingston's code will also work but for a small change..you will have to Group By some columns... guess kingston is not near his SSMS and have not compiled the code ..:hehe:

Yes, I didn't compile the code. This happens most of the times with me. SQL Server has provided so many self explanatory error messages that i just feel lazy to check the syntax sometimes. 🙂

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/