Prev: Casting to VARCHAR(MAX)
Next: using CLR that returns table
From: TheSQLGuru on 26 Nov 2009 11:00
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(a)uwe> wrote in message
news:9fa4864bc72e7(a)uwe...
> 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  | 
Pages: 1 2 3
Prev: Casting to VARCHAR(MAX)
Next: using CLR that returns table