November 15, 2009 at 10:16 pm
Hi, I'm a newbie on SQL programming
I want to asking about how to display data which have different code in same table
CODE NUMBER QTY
C10 1111911101 10
C10 112789930K 20
M10 1111911101 10
M10 1128222101 30
I want to display data which have a only one code (Bold font)
It use to check data which contain only one process code and correct data must have 2 code...
that's only sample, but if data is more than 10 million records 😀
thank you
November 16, 2009 at 12:21 am
The bold font you mentioned didn't show up.
Please show us your expected result together with what you've tried so far.
November 16, 2009 at 12:37 am
mamet_bundar (11/15/2009)
Hi, I'm a newbie on SQL programmingI want to asking about how to display data which have different code in same table
CODE NUMBER QTY
C10 1111911101 10
C10 112789930K 20
M10 1111911101 10
M10 1128222101 30
I want to display data which have a only one code (Bold font)
It use to check data which contain only one process code and correct data must have 2 code...
that's only sample, but if data is more than 10 million records 😀
thank you
This?
select
ct.CODE,
ct.NUMBER,
ct.QTY
from
dbo.CodeTable ct
inner join (select
ct1.NUMBER
from
dbo.CodeTable ct1
group by
ct1.NUMBER
having
count(ct1.NUMBER) = 1) dt
ct.NUMBER = dt.NUMBER
Untested as I'm not really sure what you are looking for. Please read the first article I reference in my signature block below on "Asking for assistance." If you follow the instructions in that article you will quicker, better answers as well as test code.
November 16, 2009 at 1:01 am
CODENumberQty
M10 S0001100
E10S0001100
M10S0002200
E30S0002200
M10S0003300
Z20S0003300
M10 S90011000
Z20 S90021000
E10 S90031000
E30 S90041000
SELECT stockIoData.*
FROM stockIoData INNER JOIN
(SELECT REEL_NO
FROM stockIoData
WHERE (DATA_CODE IN ('Z20', 'M10'))
GROUP BY REEL_NO
HAVING (COUNT(REEL_NO) = 1)) A ON stockIoData.REEL_NO = A.REEL_NO
WHERE (stockIoData.DATA_CODE IN ('Z20', 'M10'))
UNION
SELECT stockIoData.*
FROM stockIoData INNER JOIN
(SELECT REEL_NO
FROM stockIoData
WHERE (DATA_CODE IN ('E30', 'M10'))
GROUP BY REEL_NO
HAVING (COUNT(REEL_NO) = 1)) A ON stockIoData.REEL_NO = A.REEL_NO
WHERE (stockIoData.DATA_CODE IN ('E30', 'M10'))
UNION
SELECT stockIoData.*
FROM stockIoData INNER JOIN
(SELECT REEL_NO
FROM stockIoData
WHERE (DATA_CODE IN ('E10', 'M10'))
GROUP BY REEL_NO
HAVING (COUNT(REEL_NO) = 1)) A ON stockIoData.REEL_NO = A.REEL_NO
WHERE (stockIoData.DATA_CODE IN ('E10', 'M10'))
M10 must have one of E10, E30, or Z20,
vice versa
so It must display
M10 which not have one of E10, E30, or Z20 vice versa
result:
M10 S90011000
Z20 S90021000
E10 S90031000
E30 S90041000
thank you for your reply
November 16, 2009 at 1:05 am
@ lyn, thank you,
I have tried your query, and it work, but i have another problem if it have to check among 3 code...
November 16, 2009 at 6:52 am
Not sure what you are talking about at this point. Please show what you mean by three codes. If you mean you have 2 codes and you need three, change the HAVING from = 1 to <= 2.
November 16, 2009 at 8:09 am
3 codes, I mean for checking by three codes
M10 which not have one of E10, E30, or Z20 vice versa
so I need to select data Which have one number
CODE Number Qty
M10 S0001 100
E10 S0001 100
M10 S0002 200
E30 S0002 200
M10 S0003 300
Z20 S0003 300
M10 S9001 1000
Z20 S9002 1000
E10 S9003 1000
E30 S9004 1000
I want to display data on the bold font
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply