Select Query Problem

[roma_victa]

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

[TheSQLGuru]

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@uwe> wrote in message news:9f98bf2271a92@uwe...

[roma_victa]

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

[Plamen Ratchev]

Perhaps something like this:

SELECT name, skills
FROM Table
WHERE name = 'samy'
AND skills IN ('Resident Engineer', 'manager');

--
Plamen Ratchev
http://www.SQLStudio.com

[roma_victa]

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]

If you want to select all rows for names that have both skills, then this is a relational division problem and you would
some it like this:

SELECT name
FROM Table
WHERE skills IN ('Resident Engineer', 'manager')
GROUP BY name
HAVING COUNT(DISTINCT skills) = 2;

[roma_victa]

hi,

Thanks for the reply

I done think i explained the problem very well

i have the following records

name address experiance skills
roma arrora Dubai 3 manager
roma arrora Dubai 3 Engineer
roma arrora Dubai 3 Planner

roma victa Dubai 4 manager
roma victa Dubai 3 Software

first roma has three skills manager, engineer, planner

The second roma has 2 skills manager, software

when the user inputs roma and select the skills manager, and Engineer as
inputs

it should be roma arroras records that should come not roma victa

we cant use select * from table where name like "roma%" where skills
="manager" OR skills= "engineer"

because it fetches both same with the case with your query as well

we cannot use AND because it is multiple columns

[Plamen Ratchev]

Did you try the query I posted? Here it is again updated with the predicate for name:

SELECT name
FROM Table
WHERE skills IN ('Resident Engineer', 'manager')

AND name LIKE 'roma%'

GROUP BY name
HAVING COUNT(DISTINCT skills) = 2;

--
Plamen Ratchev
http://www.SQLStudio.com

[TheSQLGuru]

how about multiple EXISTS clauses?

select *
from table t1
where name like 'roma%'
and exists (select * from table t2 where t2.name = t1.name and t2.address =
t1.address and t2.skills = 'manager')
and exists (select * from table t2 where t2.name = t1.name and t2.address =
t1.address and t2.skills = 'engineer')

I note that that query would be simple if you had a surrogate key such as an
identity column instead of having to use name and address to match up the
EXISTS clauses.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

"roma_victa" <u56444@uwe> wrote in message news:9f99cbbe1474e@uwe...

[roma_victa via SQLMonster.com]

hi i think i found the answer

select distinct ID,Name,Address,[Home Tel],[Mobile tel],[work tel],Email,
[Birth date],Rate,Notice,Created,Modified,[last cv],Deleted,[q comments],
[last typed],Recruiter from [dbo].[vItrisRepository] where (name like 'samy%'
OR name like 'samy%' OR name like 'samy%' OR name like 'samy%')AND skills
='Masters'

INTERSECT

select distinct ID,Name,Address,[Home Tel],[Mobile tel],[work tel],Email,
[Birth date],Rate,Notice,Created,Modified,[last cv],Deleted,[q comments],
[last typed],Recruiter from [dbo].[vItrisRepository] where (name like 'samy%'
OR name like 'samy%' OR name like 'samy%' OR name like 'samy%')AND skills
='Mechanical Engineer'

TheSQLGuru wrote:
>how about multiple EXISTS clauses?
>
>select *
>from table t1
>where name like 'roma%'
>and exists (select * from table t2 where t2.name = t1.name and t2.address =
>t1.address and t2.skills = 'manager')
>and exists (select * from table t2 where t2.name = t1.name and t2.address =
>t1.address and t2.skills = 'engineer')
>
>I note that that query would be simple if you had a surrogate key such as an
>identity column instead of having to use name and address to match up the
>EXISTS clauses.
>

>> hi,
>>
>[quoted text clipped - 37 lines]

>>>GROUP BY name
>>>HAVING COUNT(DISTINCT skills) = 2;

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200911/1

[TheSQLGuru]

Not sure why you need 4 name like 'same%' iterations. I also believe the 2
exists I did is equivalent to the intersect. :-)

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

"roma_victa via SQLMonster.com" <u56444@uwe> wrote in message
news:9fa4864bc72e7@uwe...