SQL Query to Find Duped Items - Help

[idaBigA]

Can someone write me this query?

I want it for the Mir 3 program I am slowly making (see here > > http://www.lomcn.co.uk/forum/showthread.php?t=59554)

Firstly, because I am not good at using any other than basic SQL queries, I would personally fetch all items from all 4 tables and then use my VB6 program to find the duplicates..

I assume there is an easier way of finding the duplicates in these 4 tables by using a single SQL Query????? What I am after is to find each instance where the MAKEINDEX is found in more than one place in any of the tables..

TBL_SavedItem=FLD_CHARACTER,FLD_MAKEINDEX,FLD_INDEX

TBL_Item=FLD_CHARACTER,FLD_MAKEINDEX,FLD_INDEX

TBL_DHLBOX=FLD_FROMWHO,FLD_ITEM_MAKEINDEX,FLD_ITEM_INDEX (WHERE FLD_READCHECK=0 & FLD_CANCEL = 0)

TBL_ItemMarket=FLD_SELLWHO,FLD_ITEM_MAKEINDEX,FLD_ITEM_INDEX (WHERE FLD_SELLOK=0)

So.. can it be done, can someone do it for me?

/Mick

 

[kyeron]

duno to answer to ur question but i think the items can be duplicated only at reboot if 1 player send's a msg to some1 else and he doesnt open's it , but i think the msg needs to be send in 5-10 min's befour the reboot or it wount be duplicated , so u better dont tell players when u reboot the server or u just say a "rebooting" and close the gate's , i quess its easyer to prevent them then to find them :P , if any1 knows another way to duplicate em feel free to say it

 

[idaBigA]

I just want to add it to my program as another tool.. I will keep posting updates to the program, and anything people want adding I will add to make it do whatever anyone wants. I have already written on for Sting that does everything I want it to including stuff that other people wouldn't need, so I am doing this one so that server owners get a tool that will make is so that they will never have to run a query again, just use this easy to use tool.

 

[Kaori]

SEARCH! I think I posted before... anyway.. here they are...
1. item vs item
2. saveditem vs saveditem
3. item vs saveditem

you'll have to do the other combinations... stditems table is an EI table.. not sure if it's similar in holley.

SELECT DISTINCT s.name, i1.*
  FROM TBL_ITEM i1, TBL_ITEM i2, STDITEMS s
 WHERE i1.FLD_MAKEINDEX=i2.FLD_MAKEINDEX
   AND i1.FLD_CHARACTER<>i2.FLD_CHARACTER
   AND i1.FLD_INDEX = i2.FLD_INDEX
   AND s.Idx=i1.FLD_INDEX-1
 ORDER BY i1.FLD_MAKEINDEX

SELECT DISTINCT s.name, i1.*
  FROM TBL_SAVEDITEM i1, TBL_SAVEDITEM i2, STDITEMS s
 WHERE i1.FLD_MAKEINDEX=i2.FLD_MAKEINDEX
   AND i1.FLD_CHARACTER<>i2.FLD_CHARACTER
   AND i1.FLD_INDEX = i2.FLD_INDEX
   AND s.Idx=i1.FLD_INDEX-1
 ORDER BY i1.FLD_MAKEINDEX

SELECT s.Name Name, i1.*
  FROM TBL_ITEM i1
 INNER JOIN TBL_SAVEDITEM i2
    ON i1.FLD_MAKEINDEX = i2.FLD_MAKEINDEX
   AND i1.FLD_INDEX = i2.FLD_INDEX
 INNER JOIN Stditems s
    ON i1.FLD_INDEX - 1 = s.Idx
 ORDER BY i1.FLD_MAKEINDEX

 

[idaBigA]

Thanks Kaori, I did search lol..

Searched for "duped" and "dupe" and "items" "sql query" and "duplicate"

Dunno what else to search for.. I would have infracted myself if I hadn't

 

[Kaori]

anyway.. hope those helps..

 

[idaBigA]

Very helpful, I didn't quite get how to use the JOIN bit..

I will try them when I get to work tomorrow.. my tool is coming along nicely now.

 

[idaBigA]

Question.. Would it be better to run the 10 Queries..

TBL_SavedItem v TBL_SavedItem
TBL_Item v TBL_Item
TBL_DHLBOX v TBL_DHLBOX
TBL_ItemMarket v TBL_ItemMarket
TBL_SavedItem v TBL_Item
TBL_SavedItem v TBL_DHLBOX
TBL_SavedItem v TBL_ItemMarket
TBL_Item v TBL_DHLBOX
TBL_Item v TBL_ItemMarket
TBL_DHLBOX v TBL_ItemMarket

OR

Just load up every item and let my VB6 to do the sorting? Which means I will only be making 4 queries to the server?

 

[zedina]

simple query to find in different player store same item

select t1.fld_character as h1, t2.fld_character as h2, t1.fld_makeindex as fld_makeindex, t1.fld_index as fld_index
from tbl_saveditem t1
left join tbl_saveditem t2 on t1.fld_makeindex = t2.fld_makeindex and t1.fld_character != t2.fld_character 
where t2.fld_character is not null

 

[Dazz]

Or just export your data to microsoft excel, theirs a button called delete duplicates choose the field you want. Save it, copy it to access import it back in

/Daz - thats what i did with 2.3

 

[idaBigA]

I am doing an automated program, I want it to run as fast as possible.

Which will probably mean running all of the queries, rather than letting VB do the work, just wanted to ask the question.

 

[Mukai]

idaBigA , you create program for clon items?
o_o

 

[idaBigA]

It will find them yet, but removing them would mean some investigation on your part, as you don't know which one is the "clone"