Help needed to create a specific query!!!!

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 needed to create a specific query!!!!

Author

Topic

Raoulh79
Starting Member

24 Posts

Posted - 2007-11-05 : 08:12:47

Dear All

,

The question I want to ask is a little bit complicated, I will try to explain it as simple as possible.

I have a table which has as primary key the column product_id. The product_id can be an internal code like 10001 or a barcode 2000000034453. The second column distinguish the type of the product_id (if it is 1 = internal code, if it is 2 = barcode). If the product_id is a barcode then this barcode is connected to an internal code in the column linked_item. In the same table there are both internal code and the barcodes.
For example the format of the table is like this

Product_id Ref_type Linked_Item
10001 1 0
10002 1 0
200000000435 2 10001

I want to find the items with product_id like 10001 (internal codes) that does not have connected a barcode to them. In the specific table I want to find items like 10002. And the table does not have any other field which can help the query to make it easier. The table has more then 8000 entries which are both internal codes and barcodes.

I run this but it return all the internal codes which have linked_item=0, which is not what I want, I need to find the ITEMS that are Internal Code and does not have connected barcodes to them.

select * from items
where
REF_TYPE = 1 and
Product_id not in
(select Product_id from items where
N0_REF_TYPE = 2
AND
Linked_item_id = product_id)

Any help will be really appreciated; I hope I was clear in my description….

Thanks in advance!!!!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-05 : 08:20:41

[code]-- Prepare sample data
DECLARE @Sample TABLE (Product_id BIGINT, Ref_type TINYINT, Linked_Item BIGINT)

INSERT @Sample
SELECT 10001, 1, 0 UNION ALL
SELECT 10002, 1, 0 UNION ALL
SELECT 200000000435, 2, 10001

-- Peso 1
SELECT s1.Product_ID
FROM @Sample AS s1
WHERE s1.Ref_Type = 1
AND NOT EXISTS (SELECT * FROM @Sample AS s2 WHERE s2.Ref_Type = 2 AND s2.Linked_item = s1.Product_ID)

-- Peso 2
SELECT s1.Product_ID
FROM @Sample AS s1
LEFT JOIN @Sample AS s2 ON s2.Linked_item = s1.Product_ID
AND s2.Ref_Type = 2
WHERE s1.Ref_Type = 1
AND s2.Linked_item IS NULL[/code]

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

Raoulh79
Starting Member

24 Posts

Posted - 2007-11-05 : 08:33:19

First of all PESO i want to thank you for your quick reply

,

In your reply you mean that i need to create a sample table where i need to transfer all raws inside the new table? and then run your 2 queries in order to get my results?????

Thanks in advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-05 : 08:35:02

nope. You only need to run either one of the query (Peso 1 or 2). Both will give u the same result.

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-05 : 09:01:35

quote:
Originally posted by Raoulh79

First of all PESO i want to thank you for your quick reply,

In your reply you mean that i need to create a sample table where i need to transfer all raws inside the new table? and then run your 2 queries in order to get my results?????

Thanks in advance

Use one of the queries and replace @Sample by items

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources