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?

Nov 21 '08 #1

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:

Happy coding

-- CK


se

Nov 21 '08 #2

cokelite

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

Nov 24 '08 #3

ck9663

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

-- CK

Nov 24 '08 #4

cokelite

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

Nov 25 '08 #5

Uncle Dickie

can you try something like:

Nov 25 '08 #6

cokelite

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

Nov 25 '08 #7

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

Nov 25 '08 #8