duplicate rows

Question

lorraine1000 0 Newbie Poster

18 Years Ago

Hi
i having a few problems what i need to do is find duplicate rows in a mssql database i can do from one table but i need it to run across 2 tables

this is the code i useing to do one table

SELECT COUNT(*), sID1, sID2, sID3 FROM ITEM_T
WHERE (sItemID = 760) OR
(sItemID = 761) OR
(sItemID = 762)
GROUP BY sID1, sID2, sID3
HAVING COUNT(*)>1;

this is the other table i need to include

SELECT COUNT(*), sID1, sID2, sID3 FROM BANKITEM_T

the cloums within the tables are the same

many thanks

lorraine1000

mssql

2 Contributors
13 Replies
107 Views
13 Hours Discussion Span
Latest Post 18 Years Ago Latest Post by lorraine1000

hollystyles 113 Veteran Poster

18 Years Ago

Try the UNION keyword, if the columns are all the same (inclusing datatype) it should work.

SELECT COUNT(*), sID1, sID2, sID3 FROM ITEM_T
WHERE (sItemID = 760) OR
(sItemID = 761) OR
(sItemID = 762)
GROUP BY sID1, sID2, sID3
HAVING COUNT(*)>1;
UNION
SELECT COUNT(*), sID1, sID2, sID3 FROM BANKITEM_T
WHERE (sItemID = 760) OR
(sItemID = 761) OR
(sItemID = 762)
GROUP BY sID1, sID2, sID3
HAVING COUNT(*)>1;

hollystyles 113 Veteran Poster

18 Years Ago

Ditch the semi-colons

SELECT COUNT(*), sID1, sID2, sID3 FROM ITEM_T
WHERE (sItemID = 760) OR
(sItemID = 761) OR
(sItemID = 762)
GROUP BY sID1, sID2, sID3
HAVING COUNT(*)>1
UNION
SELECT COUNT(*), sID1, sID2, sID3 FROM BANKITEM_T
WHERE (sItemID = 760) OR
(sItemID = 761) OR
(sItemID = 762)
GROUP BY sID1, sID2, sID3
HAVING COUNT(*)>1

Semi-colon is a statement terminator and we need this to be one statement.

hollystyles 113 Veteran Poster

18 Years Ago

Ok you need also to read about the relational model (just google those terms and look for F Codd) as well as SQL syntax. Duplicates should NEVER be allowed, and databases designed with duplicate rows are bad designs and are exactly the cause of difficult query design you are now having to come up with. That's some preventative medicine for you to consider in the future when you are fully fledged database developer/administrator.

Now I'm not too sure what you are asking for. Are you saying sItemID 760 - 762 are allowed to be duplicated and ALL other sItemID's are not ?

So you want to select all duplicated sItemID's that AREN'T 760,761 or 762 ?

hollystyles 113 Veteran Poster

18 Years Ago

Oh dear sorry I'm still not getting it. What do want in the select results?

400 - 429 and 706 - 749 ? or 720 - 723 etc...?

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

lorraine1000 0 Newbie Poster · Answer 1 · 2006-08-02T14:22:35+00:00

Try the UNION keyword, if the columns are all the same (inclusing datatype) it should work.

SELECT COUNT(*), sID1, sID2, sID3 FROM ITEM_T
WHERE (sItemID = 760) OR
(sItemID = 761) OR
(sItemID = 762)
GROUP BY sID1, sID2, sID3
HAVING COUNT(*)>1;
UNION
SELECT COUNT(*), sID1, sID2, sID3 FROM BANKITEM_T
WHERE (sItemID = 760) OR
(sItemID = 761) OR
(sItemID = 762)
GROUP BY sID1, sID2, sID3
HAVING COUNT(*)>1;

i have tryed this already and i get this error

Server: Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'UNION'.

lorraine1000 0 Newbie Poster · Answer 2 · 2006-08-02T15:22:00+00:00

Ditch the semi-colons

SELECT COUNT(*), sID1, sID2, sID3 FROM ITEM_T
WHERE (sItemID = 760) OR
(sItemID = 761) OR
(sItemID = 762)
GROUP BY sID1, sID2, sID3
HAVING COUNT(*)>1
UNION
SELECT COUNT(*), sID1, sID2, sID3 FROM BANKITEM_T
WHERE (sItemID = 760) OR
(sItemID = 761) OR
(sItemID = 762)
GROUP BY sID1, sID2, sID3
HAVING COUNT(*)>1

Semi-colon is a statement terminator and we need this to be one statement.

Thank you this works fine thank you all for your help

lorraine1000

lorraine1000 0 Newbie Poster · Answer 3 · 2006-08-02T15:44:19+00:00

hi i was wondering if you could help me a little more with this same code. where i have this

WHERE (sItemID = 760) OR
(sItemID = 761) OR
(sItemID = 762)

it would be easyer to do a full list not just some items
but the problem is
i have a list of 2000 items in the sItemID colum
but some of these are meant to be dpulicate rows they should have the same sID now there is about 20 sItemID that this applys to if i could do it so these would not show up but show the rest it would be much better is there a way this can be done

i am just really getting to know sql so really need the help

lorraine1000 0 Newbie Poster · Answer 4 · 2006-08-02T19:19:22+00:00

Ok you need also to read about the relational model (just google those terms and look for F Codd) as well as SQL syntax. Duplicates should NEVER be allowed, and databases designed with duplicate rows are bad designs and are exactly the cause of difficult query design you are now having to come up with. That's some preventative medicine for you to consider in the future when you are fully fledged database developer/administrator.
Now I'm not too sure what you are asking for. Are you saying sItemID 760 - 762 are allowed to be duplicated and ALL other sItemID's are not ?
So you want to select all duplicated sItemID's that AREN'T 760,761 or 762 ?

nope 760 761 762 should not be allowed :(
the IDs that should be allowed are these

400 thought to 429
706 thought to 749

with 720 to 723 missing
and 734 to 736 missing
and 739 to 744 missing

yes i know this is going to be very hard work but doing each item one by one is going to be very long :(

lorraine1000

lorraine1000 0 Newbie Poster · Answer 5 · 2006-08-02T19:40:37+00:00

Oh dear sorry I'm still not getting it. What do want in the select results?
400 - 429 and 706 - 749 ? or 720 - 723 etc...?

ok this is going to be a bit long but i try and explain better

400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
425
426
427
428
428
706
707
708
709
710
711
712
713
714
715
716
717
718
719
724
725
726
727
728
729
730
731
732
733
737
738
745
746

all these sItemID are allowed to be dupes in the database they must have the same ID as they are bounded to the char

all the other sItemID are not allowed and these are the ones i need to find so they can be deleted
there are 2000 sItemID in total so the easy way would be to pull a list of all others but these ones i hope you understand this

many thanks for your help i dont know what i would do with this mess :(

lorraine1000 0 Newbie Poster · Answer 6 · 2006-08-02T20:37:01+00:00

SELECT COUNT(*), sID1, sID2, sID3 FROM ITEM_T
WHERE sItemID NOT IN (400, 401, 402, 403, 404, 405, 406, 407, 408, 409, 410, 411, 412, 413, 414, 415, 416, 417, 418,
419, 420, 421, 422, 423, 425, 426, 427, 428, 428, 706,707, 708, 709, 710, 711, 712, 713, 714, 715, 716, 717, 718,
719, 724, 725, 726, 727, 728, 729, 730, 731, 732, 733, 737, 738, 745, 746)
GROUP BY sID1, sID2, sID3
HAVING COUNT(*)>1
UNION
SELECT COUNT(*), sID1, sID2, sID3 FROM BANKITEM_T
WHERE sItemID NOT IN (400, 401, 402, 403, 404, 405, 406, 407, 408, 409, 410, 411, 412, 413, 414, 415, 416, 417, 418,
419, 420, 421, 422, 423, 425, 426, 427, 428, 428, 706,707, 708, 709, 710, 711, 712, 713, 714, 715, 716, 717, 718,
719, 724, 725, 726, 727, 728, 729, 730, 731, 732, 733, 737, 738, 745, 746)
GROUP BY sID1, sID2, sID3
HAVING COUNT(*)>1

ok this seems to work but i still need to check 100%

i dont think this is the best way

lorraine1000

lorraine1000 0 Newbie Poster · Answer 7 · 2006-08-02T21:42:25+00:00

hollystyles i want to thank you for all your help

lorraine1000

hollystyles 113 Veteran Poster · Answer 8 · 2006-08-02T21:47:12+00:00

That's exactly what I was going to suggest, as soon as I had established which list of items it needed to be.

The only other way is to add the list of ID's that you want into another table on their own and join on it.

lorraine1000 0 Newbie Poster · Answer 9 · 2006-08-02T22:01:57+00:00

i think this was the easyes way of the 2 options but it does work

thank you once again

lorraine1000