By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,265 Members | 1,333 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,265 IT Pros & Developers. It's quick & easy.

Filtering different conditions in the same field

P: 6
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
Share this Question
Share on Google+
7 Replies


ck9663
Expert 2.5K+
P: 2,878
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
  1.  
  2. SELECT
  3.   Artist
  4. FROM CONCERTS
  5. where (PerformanceCity = 'London' or PerformanceCity = 'Paris' or PerformanceCity = 'Helsinki') and year(DateOfPerformance) = 2008
  6.  
  7.  
Happy coding

-- CK


se
Nov 21 '08 #2

P: 6
My point is, that I only want to know the artists who have visited in each of these cities.
Nov 24 '08 #3

ck9663
Expert 2.5K+
P: 2,878
You mean who visited all these cities? Do you insert a record for each city they visited?

-- CK
Nov 24 '08 #4

P: 6
Yup. I want the query to return only the artists who visited all these cities.
Nov 25 '08 #5

Uncle Dickie
P: 67
can you try something like:

Expand|Select|Wrap|Line Numbers
  1. SELECT Artist
  2. FROM Concerts
  3. GROUP BY Artist
  4. HAVING    (sum(case when PerformanceCity='London' then 1 else 0 end)) > 1
  5.     AND    (sum(case when PerformanceCity='Paris' then 1 else 0 end)) > 1
  6.     AND (sum(case when PerformanceCity='Helsinki' AND YEAR(DateOfPerformance)=2008) then 1 else 0 end)) > 1
  7. ORDER BY Artist
Nov 25 '08 #6

P: 6
Thanks uncle, your solution seems to be just what i was looking for :)
Nov 25 '08 #7

Uncle Dickie
P: 67
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

Post your reply

Sign in to post your reply or Sign up for a free account.