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?
|