A complicated query type (complicated for me!!!)

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)

A complicated query type (complicated for me!!!)

Author

Topic

Raoulh79
Starting Member

24 Posts

Posted - 2007-11-09 : 09:42:59

Dear ALL,

I am really amazed from the quick response time PESO, really good JOB.
I want to post another question again about the same query but i want to add some information from another table too.
The right query is :

select count(t1.sz_item_ref_no) from PLU as t1
where
t1.n0_lkup_data_set=3 and
t1.N0_REF_TYPE = 1 and not exists
(select * from plu as t2
where
t2.N0_REF_TYPE = 2 and
t2.SZ_LINK_ITEM_NO = t1.sz_item_ref_no)

It functions OK, this query shows me the ITEMS (PLUs) where there are no barcode connected to them. Now i want to add also a the description of the item which is in the table DESCRIPTIONS. Both tables have in common the field n0_descr_no. I have tried many queries which are wrong because the result set i get is much bigger from the right one. Of course the descriptions table has also descriptions from other things.

Thanks in advance,

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-09 : 09:52:42

[code]SELECT d.description, COUNT(t1.sz_item_ref_no)
FROM PLU AS t1
INNER JOIN DESCRIPTIONS AS d ON t1.n0_descr_no = d.n0_descr_no
left JOIN PLU AS t2 ON t1.sz_item_ref_no = t2.sz_item_ref_no
AND t2.N0_REF_TYPE = 2
WHERE t1.n0_lkup_data_set = 3
AND t1.N0_REF_TYPE = 1
AND t2.sz_item_ref_no IS NULL
GROUP BY d.description[/code]

KH
[spoiler]Time is always against us[/spoiler]

Raoulh79
Starting Member

24 Posts

Posted - 2007-11-10 : 05:36:30

Dear KHTAN,

I tried your suggestion but i cant make it function. i will try to explain more in detailed what i want to do, because maybe i wasn't clear.
As i told in the previous mail PESO suggested me the way to see the ITEMS which don't have connected BARCODES to them.

select t1.sz_item_ref_no, t1.n0_ref_type, t1.n0_department_no,
t1.n2_item_price
from PLU as t1
where
t1.n0_lkup_data_set=3 and
t1.N0_REF_TYPE = 1 and not exists
(select * from plu as t2
where
t2.N0_REF_TYPE = 2 and
t2.SZ_LINK_ITEM_NO = t1.sz_item_ref_no)

And it is right as i checked, i want to add also the description of the ITEM, which is in another table called DESCRIPTIONS. I just want to add the related description to the ITEM. when i want to show the descriptions of the items of a specific store i run the following query:

select * from descriptions
where
n0_lkup_data_set=4 and
n0_area_code=310 and
no_language_code=30 and
n0_descr_appl_id=1 and
n0_descr_no in
(select n0_descr_no from plu
where
n0_lkup_data_set=4 and
len(sz_item_ref_no)=5 and
sz_item_ref_no between '10000' and '50000'

The fields that both tables have in common are the N0_LKUP_DATA_SET and N0_DESCR_NO
Running also your query returned more raws in the result set then the expected. Running the query of PESO and running the new query which will show the description of the ITEM also should be exactly the same result set.
A little help for the creation of the query????

Thanks in advance

!!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-10 : 07:12:15

maybe you can also post your table DDL, sample data and the expected result

KH
[spoiler]Time is always against us[/spoiler]

Raoulh79
Starting Member

24 Posts

Posted - 2007-11-11 : 09:43:20

Dear ALL,

Let me ask it in another way...
How can i modify the following query so that in the select line I add another field, which shows the description of the SZ_ITEM_REF_NO(which is the ITEM CODE), let's say that the field name is SZ_LONG_DESCR and it is in the table DESCRIPTIONS. THE TABLE PLU AND DESCRIPTIONS HAVE IN COMMON THE FIELDS N0_LKUP_DATA_SET AND N0_DESCR_NO. HOW SHOULD I MODIFY THE QUERY:

select t1.sz_item_ref_no, t1.n0_ref_type, t1.n0_department_no,
t1.n2_item_price
from PLU as t1
where
t1.n0_lkup_data_set=3 and
t1.N0_REF_TYPE = 1 and not exists
(select * from plu as t2
where
t2.N0_REF_TYPE = 2 and
t2.SZ_LINK_ITEM_NO = t1.sz_item_ref_no)

THANKS GUYS!!!
I really appreciate your help....

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-11 : 10:01:21

YOu would have to joins as in khtan's posted sample. Just make sure the joins are on the appropriate field.

Maybe this will help, note how the joins are set up.


Create Table #Desc (Desc_Key int not null,
		    Desc_Text varchar(10) not null)

Create Table #Prod (Prod_Key int not null,
		    Prod_Name char(6) not null)

Create Table #info (Prod_Key int not null,
		    Sale_Number int not null)


INSERT INTO #Desc (Desc_Key,Desc_Text)
Select 1,'Desc 1' UNION ALL
Select 2,'Desc 2' UNION ALL
Select 3,'Desc 3' UNION ALL
Select 4,'Desc 4' UNION ALL
Select 5,'Desc 5' 

Insert Into #Prod (Prod_Key,Prod_Name)
Select 1,'Prod 1' UNION ALL
Select 2,'Prod 2' UNION ALL
Select 3,'Prod 3' UNION ALL
Select 4,'Prod 4' UNION ALL
Select 5,'Prod 5'

Insert Into #info (Prod_Key,Sale_Number)
Select 1,1 UNION ALL
Select 1,2 UNION ALL
Select 1,3 UNION ALL
Select 1,4 UNION ALL
Select 1,5 UNION ALL
Select 2,1 UNION ALL
Select 2,2 UNION ALL
Select 2,3 UNION ALL
Select 2,4 UNION ALL
Select 2,5 

Select #Prod.Prod_Key, #Desc.Desc_Text,Count(#info.Sale_Number)
FROM #prod Left join #Desc on #prod.Prod_Key = #desc.Desc_Key
	   Left Join #info on #prod.Prod_Key = #info.Prod_Key
Where #prod.Prod_Key = 1
Group by #prod.Prod_Key,Desc_Text

Select #Prod.Prod_Key, #Desc.Desc_Text,Count(#info.Sale_Number)
FROM #prod Left join #Desc on #prod.Prod_Key = #desc.Desc_Key
	   Left Join #info on #prod.Prod_Key = #info.Prod_Key
Group by #prod.Prod_Key,Desc_Text
Order by Count(#info.sale_number) desc

Drop Table #prod
Drop Table #desc
Drop Table #info

Query Results:


Prod_key   Desc_Text  Count
1	    Desc 1	5


Prod_Key   DEsc_Text   Count
1	    Desc 1	5
2	    Desc 2	5
3	    Desc 3	0
4	    Desc 4	0
5	    Desc 5	0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources