Prev: Casting to VARCHAR(MAX)
Next: using CLR that returns table
From: Plamen Ratchev on 24 Nov 2009 10:40
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;


--
Plamen Ratchev
http://www.SQLStudio.com
From: roma_victa on 24 Nov 2009 11:23
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 wrote:
>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;
>

From: Plamen Ratchev on 24 Nov 2009 13:13
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
From: TheSQLGuru on 24 Nov 2009 18:22
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(a)uwe> wrote in message news:9f99cbbe1474e(a)uwe...
> 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 wrote:
>>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;
>>
>


From: roma_victa via SQLMonster.com on 25 Nov 2009 07:52
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

First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Casting to VARCHAR(MAX)
Next: using CLR that returns table