469,602 Members | 1,826 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,602 developers. It's quick & easy.

Sort data under 1 table but with different data

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.
Sep 10 '07 #1
6 1127
r035198x
13,262 8TB
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

Expand|Select|Wrap|Line Numbers
  1. select * from tableName where Warehouse = 070 and Item not in (select Item from tableName where Warehouse <> 070) 
Sep 10 '07 #2
Hi and welcome to TSDN.
Try

Expand|Select|Wrap|Line Numbers
  1. 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.
Sep 10 '07 #3
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 :
Expand|Select|Wrap|Line Numbers
  1.  SELECT * FROM tableName WHERE Warehouse = 070 AND Item NOT IN (SELECT Item FROM tableName WHERE Warehouse = 073)
  2.  
So, it should be ok now.. THanks a lot for the guidelines.... ^_^
Sep 10 '07 #4
r035198x
13,262 8TB
Hi...

I try to change some of the code and found below code work :
Expand|Select|Wrap|Line Numbers
  1.  SELECT * FROM tableName WHERE Warehouse = 070 AND Item NOT IN (SELECT Item FROM tableName WHERE Warehouse = 073)
  2.  
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?
Sep 10 '07 #5
amitpatel66
2,367 Expert 2GB
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT e.* FROM <table_name> e WHERE e.warehouse = '070' AND 1 = (SELECT COUNT(warehouse) FROM <table_name> WHERE item = e.item)
  2.  
Sep 10 '07 #6
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..
Sep 11 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by web developer | last post: by
9 posts views Thread by Raymond Lewallen | last post: by
25 posts views Thread by Rainmaker | last post: by
1 post views Thread by Lyners | last post: by
6 posts views Thread by weetat.yeo | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.