|
| ||
| duplicate rows 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 |
| ||
| Re: duplicate rows Try the UNION keyword, if the columns are all the same (inclusing datatype) it should work. SELECT COUNT(*), sID1, sID2, sID3 FROM ITEM_T |
| ||
| Re: duplicate rows Quote:
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'. |
| ||
| Re: duplicate rows Ditch the semi-colons SELECT COUNT(*), sID1, sID2, sID3 FROM ITEM_T Semi-colon is a statement terminator and we need this to be one statement. |
| ||
| Re: duplicate rows Quote:
Thank you this works fine thank you all for your help lorraine1000 |
| ||
| Re: duplicate rows 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 |
| ||
| Re: duplicate rows 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 ? |
| ||
| Re: duplicate rows Quote:
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 |
| ||
| Re: duplicate rows 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...? |
| ||
| Re: duplicate rows Quote:
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 :( |
| All times are GMT -4. The time now is 9:47 pm. |
Forum system based on vBulletin Copyright ©2000 - 2008, Jelsoft Enterprises Ltd.
©2003 - 2008 DaniWeb® LLC