Queries should return same results... but don't........

Hi guys.

I have this query:
SELECT count(*)
FROM component
WHERE manufacturerid = 13
AND deleted is null

Which returns 7, and is the correct answer. This tells me the number
of components made by manufacturer 13.

I then have:

SELECT count(*) from useraircraftcomponent uac
INNER JOIN component c ON c.id = uac.componentid
WHERE c.manufacturerid =13
AND uac.deleted is null

This returns me 8, which again, is correct. It shows that 8 users are
using products used by manufacturer 13.

However, here's the query I need to use to return both answers, plus
some other goodies. But the 3rd and 4th are the ones I am worried
about. ComponentCount should be 7, as above. and usedcount should be
8, as above.

SELECT m.id, m.description,
m.deleted,
COUNT(c.manufacturerid) as componentcount,
COUNT(uac.id) AS usedcount,
m.usercreatedid,
m.userupdatedid,
u.username AS updateuser,
u2.username AS createduser,
m.url
FROM dbo.[manufacturer] m
LEFT JOIN dbo.component c ON c.manufacturerid = m.id
AND c.[Deleted] IS NULL
RIGHT JOIN dbo.UserAircraftComponent uac ON uac.componentid = c.id
AND uac.deleted IS NULL
INNER JOIN dbo.[User] u ON u.id = m.userupdatedid
INNER JOIN dbo.[User] u2 ON u2.id = m.usercreatedid
WHERE m.id = @id
GROUP BY m.id, m.description,
m.deleted,
m.usercreatedid,
m.userupdatedid,
u.username,
u2.username,
m.url
ORDER BY m.[description]

But I am getting 8 and 8.... I think my joins are stuffed, but not
sure what to do...

Can anyone assist or spy something wrong?

Try

SELECT m.id, m.description,
m.deleted,
(SELECT COUNT(*) FROM dbo.component c WHERE c.manufacturerid = m.id) as
componentcount,
(SELECT COUNT(*) FROM UserAircraftComponent uac
INNER JOIN component c ON c.id = uac.componentid
WHERE c.manufacturerid =m.id AND uac.deleted is null) AS usedcount,
m.usercreatedid,
m.userupdatedid,
u.username AS updateuser,
u2.username AS createduser,
m.url
FROM dbo.[manufacturer] m
INNER JOIN dbo.[User] u ON u.id = m.userupdatedid
INNER JOIN dbo.[User] u2 ON u2.id = m.usercreatedid
WHERE m.id = @id
ORDER BY m.[description]

Tom

"Cralis" wrote in message
news:24b8dd54-4d49-463f-8eda-f2eccf39cc75-at-25g2000prz.googlegroups.com...
> Hi guys.
>
> I have this query:
> SELECT count(*)
> FROM component
> WHERE manufacturerid = 13
> AND deleted is null
>
> Which returns 7, and is the correct answer. This tells me the number
> of components made by manufacturer 13.
>
> I then have:
>
> SELECT count(*) from useraircraftcomponent uac
> INNER JOIN component c ON c.id = uac.componentid
> WHERE c.manufacturerid =13
> AND uac.deleted is null
>
> This returns me 8, which again, is correct. It shows that 8 users are
> using products used by manufacturer 13.
>
> However, here's the query I need to use to return both answers, plus
> some other goodies. But the 3rd and 4th are the ones I am worried
> about. ComponentCount should be 7, as above. and usedcount should be
> 8, as above.
>
> SELECT m.id, m.description,
> m.deleted,
> COUNT(c.manufacturerid) as componentcount,
> COUNT(uac.id) AS usedcount,
> m.usercreatedid,
> m.userupdatedid,
> u.username AS updateuser,
> u2.username AS createduser,
> m.url
> FROM dbo.[manufacturer] m
> LEFT JOIN dbo.component c ON c.manufacturerid = m.id
> AND c.[Deleted] IS NULL
> RIGHT JOIN dbo.UserAircraftComponent uac ON uac.componentid = c.id
> AND uac.deleted IS NULL
> INNER JOIN dbo.[User] u ON u.id = m.userupdatedid
> INNER JOIN dbo.[User] u2 ON u2.id = m.usercreatedid
> WHERE m.id = @id
> GROUP BY m.id, m.description,
> m.deleted,
> m.usercreatedid,
> m.userupdatedid,
> u.username,
> u2.username,
> m.url
> ORDER BY m.[description]
>
> But I am getting 8 and 8.... I think my joins are stuffed, but not
> sure what to do...
>
> Can anyone assist or spy something wrong?

Thanks Tom,

That does indeed work. Is that an acceptable way to do it though? I
ask, as I have (possibly incorrectly) been told to avoid sub-
queries...
Don't get me wrong, I have applied your query, and it's happy, but, am
I under the wrong impression, and this use of sub queries are OK?

Like most thing, "it depends". There are some arguments against using
correlated subqueries (primarily performance arguments). In some cases,
performance is better writing them as joins. However, in many cases there
is no difference in performance (see
http://msdn.microsoft.com/en-us/libr...5(SQL.90).aspx for a
discussion of this).

In theory, correlated subqueries would be slower because for each row in the
outer query, the subquery must be executed. However, the Query Optimizer is
very good at optimizing this sort of thing (most of the time). But in this
case, since I presume there is only one row returned in the outer query (I
am assuming the column id is unique in the manufacturing table and the
column named id is unique in the User table), so even if the optimizer does
do the subquery once for every row, that's a total of one time so there is
no performance problem anyway.

But I would not support a rule like you should always avoid subqueries. Use
them where they are appropriate, and don't use them when they are not.

Tom

"Cralis" wrote in message
news:046e21a4-d684-4346-b233-c00f3656b070-at-i20g2000prf.googlegroups.com...
> Thanks Tom,
>
> That does indeed work. Is that an acceptable way to do it though? I
> ask, as I have (possibly incorrectly) been told to avoid sub-
> queries...
> Don't get me wrong, I have applied your query, and it's happy, but, am
> I under the wrong impression, and this use of sub queries are OK?

Crails
If you are using SQL Server 2005 try using a common table expression to
count the data and later join with otger tables.
I'm sure that Tom's query does it work , just make sure that it does not
hit performance of the whole query.






"Cralis" wrote in message
news:24b8dd54-4d49-463f-8eda-f2eccf39cc75-at-25g2000prz.googlegroups.com...
> Hi guys.
>
> I have this query:
> SELECT count(*)
> FROM component
> WHERE manufacturerid = 13
> AND deleted is null
>
> Which returns 7, and is the correct answer. This tells me the number
> of components made by manufacturer 13.
>
> I then have:
>
> SELECT count(*) from useraircraftcomponent uac
> INNER JOIN component c ON c.id = uac.componentid
> WHERE c.manufacturerid =13
> AND uac.deleted is null
>
> This returns me 8, which again, is correct. It shows that 8 users are
> using products used by manufacturer 13.
>
> However, here's the query I need to use to return both answers, plus
> some other goodies. But the 3rd and 4th are the ones I am worried
> about. ComponentCount should be 7, as above. and usedcount should be
> 8, as above.
>
> SELECT m.id, m.description,
> m.deleted,
> COUNT(c.manufacturerid) as componentcount,
> COUNT(uac.id) AS usedcount,
> m.usercreatedid,
> m.userupdatedid,
> u.username AS updateuser,
> u2.username AS createduser,
> m.url
> FROM dbo.[manufacturer] m
> LEFT JOIN dbo.component c ON c.manufacturerid = m.id
> AND c.[Deleted] IS NULL
> RIGHT JOIN dbo.UserAircraftComponent uac ON uac.componentid = c.id
> AND uac.deleted IS NULL
> INNER JOIN dbo.[User] u ON u.id = m.userupdatedid
> INNER JOIN dbo.[User] u2 ON u2.id = m.usercreatedid
> WHERE m.id = @id
> GROUP BY m.id, m.description,
> m.deleted,
> m.usercreatedid,
> m.userupdatedid,
> u.username,
> u2.username,
> m.url
> ORDER BY m.[description]
>
> But I am getting 8 and 8.... I think my joins are stuffed, but not
> sure what to do...
>
> Can anyone assist or spy something wrong?

Thanks Tom.
Reading that discussion now.
Uri, can you explain what you mean by a common table expression?

CTE is a new feature introduced in SQL Server 2005. If you are familiar with
'derived table' in SQL Server 2000 , it is pretty the same

Use AdventureWorks
GO
WITH
EmpOrdersCTE(EmployeeID, Cnt)
AS
(
SELECT EmployeeID, COUNT(*)
FROM Purchasing.PurchaseOrderHeader

GROUP BY EmployeeID
),
MinMaxCTE(MN, MX, Diff)
AS
(
SELECT MIN(Cnt), MAX(Cnt), MAX(Cnt)-MIN(Cnt)
FROM EmpOrdersCTE
)
SELECT * FROM MinMaxCTE




"Cralis" wrote in message
news:74aee3aa-888f-4f56-9d02-5a8997124293-at-r35g2000prm.googlegroups.com...
> Thanks Tom.
> Reading that discussion now.
> Uri, can you explain what you mean by a common table expression?

What version are you running? You should always state that right off.

CTEs are a new SQL 2005 construct, that allow you to define a subquery at
the top of a query, and then use it multiple times in the query itself.

Also, the notion that subqueries are to be avoided sounds like something
from the 4.2 days of the product. The optimizer has some incredible ways of
dealing with subqueries and many times they are the best way to do what you
need to. In fact, subqueries can be used almost anywhere in your query.. in
the SELECT list, in the WHERE clause, in the FROM clause (where they are
known as Derived Tables) and now with CTEs, before the query starts.

--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
www.SQLTuners.com


"Cralis" wrote in message
news:74aee3aa-888f-4f56-9d02-5a8997124293-at-r35g2000prm.googlegroups.com...
> Thanks Tom.
> Reading that discussion now.
> Uri, can you explain what you mean by a common table expression?

Thanks Kalen,

I am using SS 2005.

I'm trying to lookup a basic discussion on CTEs. They sounds extremely
interesting... but still trying to work out exactly what they do.
Thanks for the input, guys!

I just did a google search for "SQL Server" 2005 "common table expressions"
and got over 13000 hits. There is a lot of information out there.
You could start with the basic product documentation, Books Online (BOL):

http://msdn.microsoft.com/en-us/libr...6(SQL.90).aspx

--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
www.SQLTuners.com


"Cralis" wrote in message
news:a67f3dc4-4ed8-4a4e-995c-85fae4d4db7c-at-n33g2000pri.googlegroups.com...
> Thanks Kalen,
>
> I am using SS 2005.
>
> I'm trying to lookup a basic discussion on CTEs. They sounds extremely
> interesting... but still trying to work out exactly what they do.
> Thanks for the input, guys!