Sort data under 1 table but with different data

[erosche]

Hi...

I'm the new comer in this scripts network. Also quite new in SQL coding.
I would like to ask some sql scripts.

Eg. I have a table as belows :
Item Warehouse
---------------------------------
BTN001 070
BTN001 073
BTN002 070
BTN003 073
BTN004 070
BTN004 073

Then the result I want is only for item BTN002.
That means I only want to sort out item which is existed in 070, but not exist in 073.
But not the other way. I don't need item which is existed in 073, but not exist in 070.
So, how is the sql scripts for above ? Kindly help.

Thanks a lot.

[r035198x]

Hi...

I'm the new comer in this scripts network. Also quite new in SQL coding.
I would like to ask some sql scripts.

Eg. I have a table as belows :
Item Warehouse
---------------------------------
BTN001 070
BTN001 073
BTN002 070
BTN003 073
BTN004 070
BTN004 073

Then the result I want is only for item BTN002.
That means I only want to sort out item which is existed in 070, but not exist in 073.
But not the other way. I don't need item which is existed in 073, but not exist in 070.
So, how is the sql scripts for above ? Kindly help.

Thanks a lot.

Hi and welcome to TSDN.
Try

select * from tableName where Warehouse = 070 and Item not in (select Item from tableName where Warehouse <> 070) 

[erosche]

Hi and welcome to TSDN.
Try

select * from tableName where Warehouse = 070 and Item not in (select Item from tableName where Warehouse <> 070) 

Hi...

Thanks a lot for the quick reply. I just try it with my winSQL, but it just didn't work.
THere's no data sort out. Could you please advise furthermore ?

Thanks a lot.

[erosche]

Hi...

Thanks a lot for the quick reply. I just try it with my winSQL, but it just didn't work.
THere's no data sort out. Could you please advise furthermore ?

Thanks a lot.

Hi...

I try to change some of the code and found below code work :

 SELECT * FROM tableName WHERE Warehouse = 070 AND Item NOT IN (SELECT Item FROM tableName WHERE Warehouse = 073)
 

So, it should be ok now.. THanks a lot for the guidelines.... ^_^

[r035198x]

Hi...

I try to change some of the code and found below code work :

 SELECT * FROM tableName WHERE Warehouse = 070 AND Item NOT IN (SELECT Item FROM tableName WHERE Warehouse = 073)
 

So, it should be ok now.. THanks a lot for the guidelines.... ^_^

What if you have a third warehouse, say 074 and want the items only in 070 ?

I think you should try to get the statement with not equal to 070 to work. What error did you get when you tried it?

[amitpatel66]

Hi...

I'm the new comer in this scripts network. Also quite new in SQL coding.
I would like to ask some sql scripts.

Eg. I have a table as belows :
Item Warehouse
---------------------------------
BTN001 070
BTN001 073
BTN002 070
BTN003 073
BTN004 070
BTN004 073

Then the result I want is only for item BTN002.
That means I only want to sort out item which is existed in 070, but not exist in 073.
But not the other way. I don't need item which is existed in 073, but not exist in 070.
So, how is the sql scripts for above ? Kindly help.

Thanks a lot.

SELECT e.* FROM <table_name> e WHERE e.warehouse = '070' AND 1 = (SELECT COUNT(warehouse) FROM <table_name> WHERE item = e.item)
 

[erosche]

What if you have a third warehouse, say 074 and want the items only in 070 ?

I think you should try to get the statement with not equal to 070 to work. What error did you get when you tried it?

Hi...

Actually the original data has more than 1 warehouse.. And by using the modification sql, it works also. So it's ok already now.
BY using the 1st sql, I didn't get any error msg, only there's no data sort out.
But using the 2nd sql that I've modified from yours, success...
So, thanks a lot..