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]

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:

 
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:

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