|
View previous topic :: View next topic
|
| Author |
Message |
suresh1624
New User
Joined: 21 Nov 2007
Posts: 28
Location: chennai
|
|
|
|
Hi experts,
I have table where it has the following columns with the data given below
Dept , item , qty
208 , 12 , 30
208 , 12 , 60
208 , 12 , 90
208 , 13 , 30
208 , 13 , 55
208 , 13 , 65
The output of the query should be as follows. The unmatched quantities should apply for each item within the dept.
item , qty
12 , 55
12 , 65
13 , 60
13 , 90 |
|
| Back to top |
|
 |
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
| What are you tryign to achieve here ? wht is the purpose of this o/p ? |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
| Code: |
SELECT DISTINCT C.ITEM,D.QUANTITY FROM
(
SELECT * FROM TABLE A
WHERE QUANTITY NOT IN
(SELECT QUANTITY FROM TABLE B
WHERE ITEM <> A.ITEM)) AS C
INNER JOIN
(
SELECT * FROM TABLE A
WHERE QUANTITY NOT IN
(SELECT QUANTITY FROM TABLE B
WHERE ITEM <> A.ITEM)) AS D
ON C.DEPT = D.DEPT
AND C.ITEM <> D.ITEM ;
|
Try this code ... May work ... |
|
| Back to top |
|
|
Ketan Varhade
Active User
Joined: 29 Jun 2009
Posts: 197
Location: Mumbai
|
|
|
|
Hi Suresh,
Please the requirement in details . |
|
| Back to top |
|
|
suresh1624
New User
Joined: 21 Nov 2007
Posts: 28
Location: chennai
|
|
|
|
Hi Ashimer,
I need to sync both the items quantities. If get the data in that form i will be able to directly insert into the the table.
Looks like you are thinking table a and table b are different.but both are same. so the above query might not work. i ll confirm you |
|
| Back to top |
|
|
Bharath Bhat
Active User
Joined: 20 Mar 2008
Posts: 283
Location: chennai
|
|
|
|
| Ashimer is also thinking that both tables are same. Please replace TABLE by your table name in the query given by Ashimer and see if it works for you. You may need to tweak the query to make it work. If you face any difficulty, post the problems you encountered. Someone will be here to help you. |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
| Suresh, i have used only one table here ... if the correlation is confusing you , you may change the correlation names ... Run the query and let us know .. actually your requirement is getting data from different rows into a single row ... it might require a lil bit of going out of the way ... |
|
| Back to top |
|
|
suresh1624
New User
Joined: 21 Nov 2007
Posts: 28
Location: chennai
|
|
|
|
| Thanks alot ashimer. I ll let you know soon whether it worked or not. |
|
| Back to top |
|
|
|
|
