Help for the query !!! - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Help for the query !!!

Author

Topic

Raoulh79
Starting Member

24 Posts

Posted - 2008-03-26 : 16:52:07

Dear all,

in my db i have a multistore environment, and in a table a have the items of each store which are differentiated between them with the Store_No usage

Store_no ItemCode Descript Dept Price
1 1000 coca cola 10 150
2 1000 coca cola 5 200
1 1001 fanta 10 200
1 1002 sprite 10 200
2 1010 donuts 8 200

Stores between them have same products, but not all of them.
I want to create a query which will show me all the items that are in store 2 but not in store 1 (ex. ItemCode 1010)

Thanks in advance

nr
SQLTeam MVY

12543 Posts

Posted - 2008-03-26 : 16:55:41

select t2.itemcode
from (select * from tbl where store_no = 2) t2
left join (select * from tbl where store_no = 1) t1
on t1.itemcode = t2.itemcode
where t1.itemcode is null

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-27 : 15:44:30

select itemcode
from tbl
group by itemcode
having sum(case when Store_no=1 then 1 else 0 end)=0
and sum(case when Store_no=2 then 1 else 0 end)>0

Raoulh79
Starting Member

24 Posts

Posted - 2008-04-06 : 13:23:15

Hello guys,

Unfortunately i don't understand your queries and making some test i don't get the results i want, maybe i didnt explain correctly the output i want to get from the query.

the fields that i have in my table ITEMS are the following let's say: (n0_store_no, n0_item_code, n0_item_descr, n0_item_dept and n0_item_price)
Sample data on the table
(1, 1000, coca cola, 10, 3.00 ;
1, 1001, sprite, 10, 2.5;
2, 1000, coca cola, 1, 2.5;
2, 1003 fanta, 5, 1.8).

this is a sample of data from the table, you will see that there are items which are only on store 1, items that are both on store 1 and 2, and items which are only on store 2.

What i want to get are the items which are only on store 2.

Thanks in advance,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-06 : 13:37:34

Visakhs approach will do

select itemcode
from tbl
group by itemcode
having sum(case when Store_no = 2 then 0 else 1 end) = 0

E 12°55'05.25"
N 56°04'39.16"

Raoulh79
Starting Member

24 Posts

Posted - 2008-04-06 : 13:52:48

thanks peso,

if i have 4 stores, and i want to run the query the same query how should i change it. because as it is now it will show only the items that are on store 2, but if i want to compare, from a total of 4 stores, store 2 and store 1???

thanks in advance,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-06 : 15:04:21

SELECT ItemCode
FROM Tbl
GROUP BY ItemCode
HAVING MIN(Store_No) = MAX(Store_No)

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-07 : 03:54:48

[code]DECLARE @Sample TABLE (Store INT, Item INT, Descr VARCHAR(20), Dept INT, Price MONEY)

INSERT @Sample
SELECT 1, 1000, 'coca cola', 10, 3.00 UNION ALL
SELECT 1, 1001, 'sprite', 10, 2.5 UNION ALL
SELECT 2, 1000, 'coca cola', 1, 2.5 UNION ALL
SELECT 2, 1003, 'fanta', 5, 1.8

SELECT Item,
MIN(Store) AS Store
FROM @Sample
GROUP BY Item
HAVING MIN(Store) = MAX(Store)[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources