Select Query Problem
in [Programming SQL]
|
From: roma_victa on 24 Nov 2009 09:23 hi all The table have lots of duplicate rows except for the last column skills The skills has diffrent values some rows 'Middle East' and the other rows 'Resident Engineer', 'planner' etc When the user enters the name and the skills set, he wants to see thouse who has all the skills set Table sample Name Address salary skills samy d Dubai 1000 manager samy d Dubai 1000 Resident Engineer samy r Abu Dhabi 2000 manager samy r Abu Dhabi 2000 Software here i want to retry records of which has the name "samy" and skills Resident Engineer and manager There is lots of records with the name samy but i want to retry only the samy with the skills manager and resident Engineer Please help
From: TheSQLGuru on 24 Nov 2009 09:42 1) what do you mean "retry"? can you explain the objective a bit better? 2) if possible, I recommend moving the person information to one table with a primary key. then put the skills (0-n of them) in a separate table that has the PK from the person table. this is basic data normalization, which is something you probably should start learning about. 3) may I ask - is this a homework assignment? -- Kevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net "roma_victa" <u56444(a)uwe> wrote in message news:9f98bf2271a92(a)uwe... > hi all > > The table have lots of duplicate rows except for the last column skills > The skills has diffrent values some rows 'Middle East' and the other rows > 'Resident Engineer', 'planner' etc > > When the user enters the name and the skills set, he wants to see thouse > who > has all the skills set > > Table sample > > Name Address salary skills > samy d Dubai 1000 manager > samy d Dubai 1000 Resident Engineer > samy r Abu Dhabi 2000 manager > samy r Abu Dhabi 2000 Software > > > > here i want to retry records of which has the name "samy" > and skills Resident Engineer and manager > > There is lots of records with the name samy but i want to retry only the > samy > with the skills manager and resident Engineer > > > > Please help >
From: roma_victa on 24 Nov 2009 09:55 hi this is the view that is given to me the problem here is I cant do any alteration here in this the user enter the user name and skills. There are thousands of records in the view There are lots of duplicated records in there with the exception of column "skills" for excample a record with "samy" "dubai" "1000" is there like 20 times but only the column ""skills" differs. I need to a query which retrives the samy with only the skills specified by the user. and no its not a school assignment TheSQLGuru wrote: >1) what do you mean "retry"? can you explain the objective a bit better? > >2) if possible, I recommend moving the person information to one table with >a primary key. then put the skills (0-n of them) in a separate table that >has the PK from the person table. this is basic data normalization, which >is something you probably should start learning about. > >3) may I ask - is this a homework assignment? > >> hi all >> >[quoted text clipped - 22 lines] >> >> Please help
From: Plamen Ratchev on 24 Nov 2009 10:11 Perhaps something like this: SELECT name, skills FROM Table WHERE name = 'samy' AND skills IN ('Resident Engineer', 'manager'); -- Plamen Ratchev http://www.SQLStudio.com
From: roma_victa on 24 Nov 2009 10:19
this is excatly what i have done but this gives all the "samy" with the which has skills either 'Resident Engineer' or "manager" There are lots of rows and as i said there is lots of rows duplicate except for the column skills i cant use select * from table where skills = 'Resident Engineer' AND Skills = 'manager' Because skills is one column Using OR will fech the same results as you said Plamen Ratchev wrote: >Perhaps something like this: > >SELECT name, skills >FROM Table >WHERE name = 'samy' > AND skills IN ('Resident Engineer', 'manager'); > |