Filter by computed column and Join problem

I got this sql statement
SELECT ItemName, ItemId, ItemLocation,
(SELECT COUNT(*)
FROM Table2 t2
Where t1.ItemId = t2.ItemId
) AS ItemCount
FROM ItemTable t1
Is it possible so that I can filter the resultset using ItemCount? (WHERE
ItemCount >5)
I got two tables namely Item and ItemLocation
Item table is a table holding goods and ItemLocation holding goods that sold
at the location.
Item Table: ItemId [PK], ItemName ...
ItemLocation: LocationId [PK] , ItemId [PK]
I issue a SQL statement
SELECT i.ItemId, i.ItemName,
(SELECT il.LocationId
FROM ItemLocation il
WHERE il.ItemId = i.ItemId
AND il.LocationId = 2
) AS LocationId
FROM Item
This T-SQL will returns me with all the Item in ItemTable and LocationId
will be null if it is not in ItemLocation table.
What is the equivalent join statement to produce the same result without
using sub-query?
I tried LEFT JOIN but it not correct, it eliminate NULLs because of a
filter.
SELECT i.ItemId, i.ItemName,
il.LocationId
FROM Item i
LEFT JOIN ItemLocation il
WHERE il.LocationId = 2On Thu, 10 Mar 2005 23:10:33 +0800, Joel Leong wrote:

>I got this sql statement
>SELECT ItemName, ItemId, ItemLocation,
> (SELECT COUNT(*)
> FROM Table2 t2
> Where t1.ItemId = t2.ItemId
> ) AS ItemCount
>FROM ItemTable t1
>Is it possible so that I can filter the resultset using ItemCount? (WHERE
>ItemCount >5)
Hi Joel,
Method 1: repeat the subquery
WHERE (SELECT ...) > 5
Method 2: Use a derived table
SELECT ItemName, ItemId, ItemLocation, ItemCount
FROM (SELECT ItemName, ItemId, ItemLocation,
(SELECT ...) AS ItemCount
FROM ItemTable AS t1) AS derived
WHERE ItemCount > 5
Method 3: Use a join and group by instead of a subquery:
SELECT t1.ItemName, t1.ItemId, t1.ItemLocation,
COUNT(*) AS ItemCount
FROM ItemTable AS t1
INNER JOIN ItemTable AS t2
ON t2.ItemID = t1.ItemID
GROUP BY t1.ItemName, t1.ItemId, t1.ItemLocation
HAVING COUNT(*) > 5
(snip)
>What is the equivalent join statement to produce the same result without
>using sub-query?
>I tried LEFT JOIN but it not correct, it eliminate NULLs because of a
>filter.
>SELECT i.ItemId, i.ItemName,
> il.LocationId
> FROM Item i
>LEFT JOIN ItemLocation il
>WHERE il.LocationId = 2
Try
SELECT i.ItemId, i.ItemName, il.LocationId
FROM Item AS i
LEFT JOIN ItemLocation AS il
ON il.ItemId = i.ItemId
AND il.LocationId = 2
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Try,
SELECT
i.ItemId,
i.ItemName,
il.LocationId
FROM
Item i
LEFT JOIN
ItemLocation il
on i.ItemId = il.ItemId and il.LocationId = 2
AMB
"Joel Leong" wrote:

> I got this sql statement
> SELECT ItemName, ItemId, ItemLocation,
> (SELECT COUNT(*)
> FROM Table2 t2
> Where t1.ItemId = t2.ItemId
> ) AS ItemCount
> FROM ItemTable t1
> Is it possible so that I can filter the resultset using ItemCount? (WHERE
> ItemCount >5)
>
> I got two tables namely Item and ItemLocation
> Item table is a table holding goods and ItemLocation holding goods that so
ld
> at the location.
> Item Table: ItemId [PK], ItemName ...
> ItemLocation: LocationId [PK] , ItemId [PK]
> I issue a SQL statement
> SELECT i.ItemId, i.ItemName,
> (SELECT il.LocationId
> FROM ItemLocation il
> WHERE il.ItemId = i.ItemId
> AND il.LocationId = 2
> ) AS LocationId
> FROM Item
> This T-SQL will returns me with all the Item in ItemTable and LocationId
> will be null if it is not in ItemLocation table.
> What is the equivalent join statement to produce the same result without
> using sub-query?
> I tried LEFT JOIN but it not correct, it eliminate NULLs because of a
> filter.
> SELECT i.ItemId, i.ItemName,
> il.LocationId
> FROM Item i
> LEFT JOIN ItemLocation il
> WHERE il.LocationId = 2
>
>
>
>|||But the result for
>SELECT i.ItemId, i.ItemName, il.LocationId
>FROM Item AS i
>LEFT JOIN ItemLocation AS il
> ON il.ItemId = i.ItemId
> AND il.LocationId = 2
is incorrect because it eliminates all the null row
because of il.Location=2 filter.

>--Original Message--
>On Thu, 10 Mar 2005 23:10:33 +0800, Joel Leong wrote:
>
ItemCount? (WHERE
>Hi Joel,
>Method 1: repeat the subquery
> WHERE (SELECT ...) > 5
>Method 2: Use a derived table
> SELECT ItemName, ItemId, ItemLocation, ItemCount
> FROM (SELECT ItemName, ItemId, ItemLocation,
> (SELECT ...) AS ItemCount
> FROM ItemTable AS t1) AS derived
> WHERE ItemCount > 5
>Method 3: Use a join and group by instead of a subquery:
> SELECT t1.ItemName, t1.ItemId, t1.ItemLocation,
> COUNT(*) AS ItemCount
> FROM ItemTable AS t1
> INNER JOIN ItemTable AS t2
> ON t2.ItemID = t1.ItemID
> GROUP BY t1.ItemName, t1.ItemId, t1.ItemLocation
> HAVING COUNT(*) > 5
>
>(snip)
same result without
NULLs because of a
>Try
>SELECT i.ItemId, i.ItemName, il.LocationId
>FROM Item AS i
>LEFT JOIN ItemLocation AS il
> ON il.ItemId = i.ItemId
> AND il.LocationId = 2
>Best, Hugo
>--
>(Remove _NO_ and _SPAM_ to get my e-mail address)
>.
>|||On Thu, 10 Mar 2005 16:47:29 -0800, Joel Leong wrote:

>But the result for
>is incorrect because it eliminates all the null row
>because of il.Location=2 filter.
(snip)
Hi Joel,
Did you test this? It should not eliminate the null row, because I moved
the test for il.LocationId = 2 from the WHERE clause to the JOIN clause.
If you did test this and found expected output rows to be eliminated,
then please post a repro script, so that I can investigate what's wrong.
A repro script consists of
* CREATE TABLE statements, including all constraints but excluding
irrelevant columns;
* INSERT statements for a few rows of sample data (just include enough
rows to demonstratie the problem and the expected output);
* The actual statement you executed.
Apart from the repro script, you should also include the output you got
(when run against the sample data included in the repro script), the
output you expected and the result of
SELECT @.@.VERSION
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)