i need some help to buil a sql quey to check for multiclass character, im not good on sql syntax but here is the idea Code: for each user_data.char_id for each subjob_id if skill_id not //(skill list) //remove that skill this is just the main idea, im gonna try to build this query, but any help will be appreciated. thx
Try to explain with words what you want to do, the "if skill_id not -> remove that skill" makes no sense...
alright, well it need to be something like 1- select the character 2-check subjob1 3-check every skill from that subclass, and remove those are not in the list. 4-check subjob2 5-check every skill from that subclass, and remove those are not in the list. 6-check subjob3 7-check every skill from that subclass, and remove those are not in the list. 8-select the next character 9-go back to step 1. basically this, but as i said i don't know how to use sql syntax yet i tried some ideas but so far i will need some time until i have it working edit: this is some i have tested so far and well i need to manually add each class id and all the skill list yet . but i think this probably will work as i want. Code: /* - Check multiclass Skills - */ USE [lin2world] GO SELECT lin2world.dbo.user_data.char_name , lin2world.dbo.user_skill.skill_id, lin2world.dbo.user_skill.subjob_id FROM lin2world.dbo.user_data INNER JOIN lin2world.dbo.user_skill ON lin2world.dbo.user_data.char_id = lin2world.dbo.user_skill.char_id WHERE lin2world.dbo.user_data.subjob0_class = 90 and lin2world.dbo.user_skill.subjob_id = 0 /* subjob_id -- must use 0 -> subjob0, 1 -> subjob1, 2 -> subjob2, 3 -> subjob3!!!*/ /* here goes the list of the skills that each class can have, any skills that isn`t on the list must be deleted */ and not user_skill.skill_id in ('0','2') group by lin2world.dbo.user_data.char_name , lin2world.dbo.user_skill.skill_id, lin2world.dbo.user_skill.subjob_id order by char_name GO well with that i can select, those things i want but i don't know yet how can i delete those.
I think you are going by a terrible hard way, i'm not sure what are u trying to do but probably a minor query will do it. The list of skills... is a predeterminated list? is always the same list for all chars and subjobs ?
the skill list are those from skillaquiredata the thing is, one of the advext updates had a crash and some guys have 2 class stuck, so i need to fix that. as i said i have no idea of sql, so im trying to do what i can
I did a php script long ago to check exactly that, skills that are not suppoused to have, but first of all you need a database with all skills/levels etc (mainly skillacquire parsed and imported to db), you can't do it just like that...
yeah i already have the list, although i was thinking exactly on a php for parse the skillaquire and make the list for me, but it is kind of the same deal. i can get the skill list from user_skill, just get some clean characters (w/o multiclass ) and it is done, well either way im not on such a hurry right now, so i can make one class at the time
so im trying to do the query like this: Code: USE [lin2world] GO SELECT char_id FROM user_skill WHERE subjob0_class = my_class_id AND skill_id != my_skill_id but i keep getting this error: Msg 4104, Level 16, State 1, Line 2 The multi-part identifier "my_class_id" could not be bound. maybe the sql syntax is wrong, idk, if any of you can give me a hand on this will be aswome
yes i know that, i made one table called, user_skill_check wich have my_class_id, my_skill_id inside of it, i put all the skills from skillacquire.txt, the thing is i don't know how to use it, i mean, i tried that query but it give me that error, so i guess i need to fix the sql syntax, or something else. but since im not good at all at sql, any help will be great
Yeah... also subjob0_class isn't a field of user_skill table... you need to use INNER/LEFT JOIN to use more than one table, why you don't try to learn first... magic doesn't exist in sql. Also... the way you are approaching this is really bad... if you don't know SQL good enought try for example helping you in PHP if you know that better... so you can do basic querys and do all the checks and for/while/whatever in php. A basic idea how to perform this in PHP: - Generate a tree array with skillacquire this way -> $skills_tree[$class_id][$skill_id][$skill_lev] = $min_level_to_learn - Generate a parent class relation table - Go for the entire database and check each characeter. - For each character check each sub - For each sub check each skill (you need to get also the level of each subclass in order to compare) - Use both arrays to check if the skill is valid or not, based on the current class, the skill id and skill level if the array don't return a value you know is not valid skill, and even if return a value after that you need to compare the min level to current level. GIFT: Parent class relation array. Code: // Parent class list $parent_classes = array( 0=>-1,1=>0,2=>1,3=>1,4=>0,5=>4,6=>4,7=>0,8=>7,9=>7,10=>-1,11=>10,12=>11,13=>11,14=>11,15=>10,16=>15, 17=>15,18=>-1,19=>18,20=>19,21=>19,22=>18,23=>22,24=>22,25=>-1,26=>25,27=>26,28=>26,29=>25,30=>29,31=>-1, 32=>31,33=>32,34=>32,35=>31,36=>35,37=>35,38=>-1,39=>38,40=>39,41=>39,42=>38,43=>42,44=>-1,45=>44,46=>45, 47=>44,48=>47,49=>-1,50=>49,51=>50,52=>50,53=>-1,54=>53,55=>54,56=>53,57=>56,88=>2,89=>3,90=>5,91=>6, 92=>9,93=>8,94=>12,95=>13,96=>14,97=>16,98=>17,99=>20,100=>21,101=>23,102=>24,103=>27,104=>28,105=>30, 106=>33,107=>34,108=>36,109=>37,110=>40,111=>41,112=>43,113=>46,114=>48,115=>51,116=>52,117=>55,118=>57 );
and what do you think im doing here? when you get stuck at learning process, usually, you ask for help. anyway i finish it so here it is Code: USE [lin2world] GO --- Only Select --- SELECT user_data.char_name , user_skill.skill_id, user_skill.subjob_id FROM user_data INNER JOIN user_skill ON user_data.char_id = user_skill.char_id -- PS. this is for main class only, just change the subjob0_class to subjob1_class, 2, 3 to test he subclass too, but since it doesn't check the skill level yet than some skills can be missed. WHERE user_skill.skill_id NOT IN (SELECT my_skill_id FROM user_skill_check WHERE user_skill_check.my_class_id = user_data.subjob0_class or user_skill_check.my_class_id = -1 )/* nobless and any other skill that can be used at any class will have my_class_id = -1*/ ORDER BY user_data.char_name, user_skill.skill_id --------------- --- Delete the skills --- DECLARE @CheckMulticlass CURSOR DECLARE @char_id INT DECLARE @skill_id INT DECLARE @subjob_id INT SET @CheckMulticlass = CURSOR FAST_FORWARD FOR SELECT user_data.char_id , user_skill.skill_id, user_skill.subjob_id FROM user_data INNER JOIN user_skill ON user_data.char_id = user_skill.char_id -- PS. this is for main class only, just change the subjob0_class to subjob1_class, 2, 3 to test he subclass too, but since it doesn't check the skill level yet than some skills can be missed. WHERE user_skill.skill_id NOT IN (SELECT my_skill_id FROM user_skill_check WHERE user_skill_check.my_class_id = user_data.subjob0_class or user_skill_check.my_class_id = -1 )/* nobless and any other skill that can be used at any class will have my_class_id = -1*/ OPEN @CheckMulticlass FETCH FROM @CheckMulticlass INTO @char_id, @skill_id, @subjob_id WHILE @@FETCH_STATUS = 0 BEGIN DELETE FROM user_skill WHERE char_id = @char_id AND skill_id = @skill_id AND ISNULL(subjob_id, 0) = @subjob_id FETCH NEXT FROM @CheckMulticlass INTO @char_id, @skill_id, @subjob_id END CLOSE @CheckMulticlass DEALLOCATE @CheckMulticlass all you need now is a table, user_skill_check, with my_class_id, my_skill_id or any name you like thx for those who helped me, apreciated well my script will not check everything, it will only check if you have any skill that isn't for your class, i will work on a better version to check skill level and player level as well
You know that way you are not considering parent class skills... or maybe yes (depend how you insert data in that table), but if you input skillacquire like it is... you need to look also for parent classes or you are going to delete skills that is OK.
yeah i know that, this version don't check the skill_level yet, neither character level, and i have done just the 3th class skills on my table yet, for now it will work for what i need, but when i finish the skillaquire parser than i will have a better script