467,134 Members | 947 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,134 developers. It's quick & easy.

Filtering different conditions in the same field

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
  • viewed: 1881
Share:
7 Replies
ck9663
Expert 2GB
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
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 2GB
You mean who visited all these cities? Do you insert a record for each city they visited?

-- CK
Nov 24 '08 #4
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:

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

Post your reply

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

Similar topics

1 post views Thread by diskoduro | last post: by
1 post views Thread by aww | last post: by
3 posts views Thread by Damon Grieves | last post: by
17 posts views Thread by romixnews@googlemail.com | last post: by
3 posts views Thread by Shawn Ramirez | last post: by
2 posts views Thread by =?Utf-8?B?amV6MTIzNDU2?= | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.