kak sostavit' SQL zapros ...

FarStringer
Дата: 12.10.2000 23:51:08
Est' tablichka s 2mja poljami
Customer| Project
-----------------
User1 | Project1
User1 | Project2
User2 | Project3
User3 | Project1
User3 | Project2
User3 | Project3

kak vybrat' vseh Customers kotorye imejut Project1 AND Project2 ?

Thanx, Far
SergSuper
Дата: 13.10.2000 05:28:48
create table #tbl(
Customer varchar(30), Project varchar(30))


insert #tbl select 'User1','Project1'
insert #tbl select 'User1','Project2'
insert #tbl select 'User2','Project3'
insert #tbl select 'User3','Project1'
insert #tbl select 'User3','Project2'
insert #tbl select 'User3','Project3'

select distinct t1.Customer
from #tbl t1,#tbl t2
where t1.Customer=t2.Customer
and t1.Project='Project1'
and t2.Project='Project2'

Годиться?
Vasily
Дата: 13.10.2000 05:35:35
select customer from (
select customer from Table1
where project = 'project1' or project = 'project2') tmp
group by customer
having count(customer) !< 2
--На случай повторяющихся записей. Если повроряющихся нет, то можно =2 или вложенном запросе написать distinct.
Ольга
Дата: 13.10.2000 05:59:04
1.
select distinct Customer
from Table1 t1, Table1 t2
where t1.Customer=t2.Customer
and t1.Project='Project1'
and t2.Project='Project2'

2.
select Customer
from Table1 t1
where Project='Project1' and
exists(select * from Table1 t2
where t2.Project='Project2' and
t1.Customer=t2.Customer)
Far+Stringer
Дата: 13.10.2000 17:27:34
Spasibo bol'shushee, problema nemnogo projasnilas', NO est' nekotorye detali ..
Delo v tom chto chislo Projectov kotorye dolzhy vhodit' v zapros var'jiruet,
eto mozhet byt' zapros najti Customers kotorye imejut 1, 2 .. ili 100 konkretnyh proektov,
sama tablichka Customer_Project ochen' bol'shaja, sozdovat' stol'ko kopii tablichki Customer_Project,
skol'ko Project's v zaprose - nakladno, da i zapros poluchitsja ogromnyj s ogromnum kolichestvom vlozhennyh podzaprosov.
Net li bolee universal'nogo reshenija etoj problemy, tipa Select Customers FROM Customer_Project WHERE Project IN("Project1", "Project2", ... "ProjectN") no podsatvit' mezdu imenami proektov AND ?
SergSuper
Дата: 16.10.2000 05:30:02
Ну барин, ты и задачки ставишь :)

create table #tbl(
Customer varchar(30), Project varchar(30))

insert #tbl select 'User1','Project1'
insert #tbl select 'User1','Project2'
insert #tbl select 'User2','Project3'
insert #tbl select 'User3','Project1'
insert #tbl select 'User3','Project2'
insert #tbl select 'User3','Project3'

--#s - это таблица с нужными проектами
create table #s(Project varchar(30))
insert #s select 'Project1'
insert #s select 'Project2'


declare @num_project int
select @num_project=count(*) from #s
select Customer
from #tbl t, #s s
where t.Project=s.Project
group by Customer
having count(*)=@num_project

Смысл, надеюсь, понятен.

С приветом Сергей
sergsuper@mail.ru