Filtering different conditions in the same field

cokelite

Hi. I'm developing a reporting application (.NET & MsSqlServer 2000) that automatically generates the sql script for the report's data source based on the parameters passed to it.

Let's say I have a table CONCERTS:
Artist varchar(50)
PerformanceCity varchar(50)
DateOfPerformance DateTime

Now I want a report displaying only the artists who have at some time performed in London AND in Paris AND during this year also in Helsinki.

I know it's possible to get the desired result with something like this:

SELECT
Artist
FROM
CONCERTS c, CONCERTS c2, CONCERTS c3
WHERE
c.Artist=c2.Artist AND c2.Artist=c3.Artist AND
c.PerformanceCity='London' AND
c2.PerformanceCity='Paris' AND
(c3.PerformanceCity='Helsinki' AND YEAR(DateOfPerformance)=2008)

The problem with this kind of query is that generating it programmatically becomes pretty complex in a real world situation where there are lots of fields, query parameters, left outer joins and what else.

So, is there any easier way to achieve this seemingly pretty mundane result?

ck9663

Quote:

Originally Posted by cokelite

Hi. I'm developing a reporting application (.NET & MsSqlServer 2000) that automatically generates the sql script for the report's data source based on the parameters passed to it.

Let's say I have a table CONCERTS:
Artist varchar(50)
PerformanceCity varchar(50)
DateOfPerformance DateTime

Now I want a report displaying only the artists who have at some time performed in London AND in Paris AND during this year also in Helsinki.

I know it's possible to get the desired result with something like this:

SELECT
Artist
FROM
CONCERTS c, CONCERTS c2, CONCERTS c3
WHERE
c.Artist=c2.Artist AND c2.Artist=c3.Artist AND
c.PerformanceCity='London' AND
c2.PerformanceCity='Paris' AND
(c3.PerformanceCity='Helsinki' AND YEAR(DateOfPerformance)=2008)

The problem with this kind of query is that generating it programmatically becomes pretty complex in a real world situation where there are lots of fields, query parameters, left outer joins and what else.

So, is there any easier way to achieve this seemingly pretty mundane result?

try:

Expand|Select|Wrap|Line Numbers

  
SELECT

  Artist

FROM CONCERTS

where (PerformanceCity = 'London' or PerformanceCity = 'Paris' or PerformanceCity = 'Helsinki') and year(DateOfPerformance) = 2008

Happy coding

-- CK

se

cokelite

My point is, that I only want to know the artists who have visited in each of these cities.

ck9663

You mean who visited all these cities? Do you insert a record for each city they visited?

-- CK

cokelite

Yup. I want the query to return only the artists who visited all these cities.

Uncle Dickie

can you try something like:

Expand|Select|Wrap|Line Numbers

 SELECT Artist

FROM Concerts

GROUP BY Artist

HAVING    (sum(case when PerformanceCity='London' then 1 else 0 end)) > 1

    AND    (sum(case when PerformanceCity='Paris' then 1 else 0 end)) > 1

    AND (sum(case when PerformanceCity='Helsinki' AND YEAR(DateOfPerformance)=2008) then 1 else 0 end)) > 1

ORDER BY Artist

cokelite

Thanks uncle, your solution seems to be just what i was looking for :)

Uncle Dickie

Glad to be of help - just check the 'HAVING' conditions as I think it should probably be >0 (or >=1) rather than >1

Filtering different conditions in the same field

re: Filtering different conditions in the same field

re: Filtering different conditions in the same field

re: Filtering different conditions in the same field

re: Filtering different conditions in the same field

re: Filtering different conditions in the same field

re: Filtering different conditions in the same field

re: Filtering different conditions in the same field

Similar Microsoft SQL Server bytes

/bytes/development

/bytes/it

/bytes/about