Special SQL query on SQL Server 2005

I am trying to figure out a SQL query to do the following
I have a table T1 with primary key column C1 and other info column C2,C3
A second table T2 with D1 foreign key in T1 (C1), and other columns says D2,D3 ...
I want the query to return me something like this :

C1,C2,C3,N  where N is the number of rows in T2 with C1 = D1

There is a condition on T1 (where C2 = value1) and on T2 (where D2 = value2)

Thanks
LVL 1
LeTayAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Give this a whirl..
SELECT C1, C2, C3, Count(D1) as the_count
FROM T1
   LEFT JOIN T2 ON T1.C1 = T2.D1
WHERE T1.C2 = value1 AND T2.d2 = value2
GROUP BY C1, C2, C3

Open in new window

0
LeTayAuthor Commented:
Nice !
But nothing is returned for those C1 that have no row in T2 fullfilling the condition
Is there another type of join to get it ?
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>But nothing is returned for those C1 that have no row in T2 fullfilling the condition
the LEFT JOIN will return all rows on the LEFT side (aka T1), regardless if there is a right side (Aka T2) match.

So if what you mean is you want the WHERE ... T2 condition to not affect any of the T1 rows, then we'll throw it in a subquery like this..
SELECT C1, C2, C3, Count(D1) as the_count
FROM T1
   LEFT JOIN (SELECT D1 FROM T2 WHERE d2 = value2) T2 ON T1.C1 = T2.D1
WHERE T1.C2 = value1 
GROUP BY C1, C2, C3 

Open in new window

0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

LeTayAuthor Commented:
Well, in my case, D1 and C1 are the same name
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>Well, in my case, D1 and C1 are the same name
>A second table T2 with D1 foreign key in T1 (C1),
The above two statements appear to be in conflict.   Let me know when it gets straightened out.
0
LeTayAuthor Commented:
Second table has a column C1 foreign key in T1(C1)
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
So ... is the second table (T2) column name also C1?  If yes, that's an easy change, plus you have to prefix T1.C1 in the SELECT and GROUP BY, as there are two C1's and SQL needs to know which one you are referring to...
SELECT T1.C1, C2, C3, Count(D1) as the_count
FROM T1
   LEFT JOIN (SELECT C1 FROM T2 WHERE d2 = value2) T2 ON T1.C1 = T2.C1
WHERE T1.C2 = value1 
GROUP BY T1.C1, C2, C3 

Open in new window

0
LeTayAuthor Commented:
Hello,

So count(D1) become count(C1) and this gives an "ambiguous column name" error
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<correction>

SELECT T1.C1, C2, C3, Count(T2.C1) as the_count
FROM T1
   LEFT JOIN (SELECT C1 FROM T2 WHERE d2 = value2) T2 ON T1.C1 = T2.C1
WHERE T1.C2 = value1 
GROUP BY T1.C1, C2, C3  

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LeTayAuthor Commented:
Thanks it works fine !
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade.  Good luck with your project.  -Jim
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Languages and Standards

From novice to tech pro — start learning today.