Including multiple values in where clause

Guys

I'm building a reporting application where users can create bespoke reports, one area I'm having an issue with is having multiple values for a particular column. It is possible to say in english "select all contacts from 'houston' and 'dallas' ", the expected result would be all contacts who have offices in both houston and dallas. In SQL server this is a little harder to make work as a location can't equal both houston and dallas (just on or the other and subsquently returns nothing). An "or" will return either dallas or houston, one or the other, or both.

I'm starting to think this may not be possible, but just can't believe that that's the case.

Hope this makes some sort of sense, as I would like to put this issue to bed once and for all.

Duncanselect <columns> from <table> where <city> in ('houston','dallas')|||

Something like this could work. (Of course, since you didn't provide the table DDL or sample data, it is impossible to 'get it right' for you.)

Code Snippet

SELECT *
FROM Contacts
WHERE ContactID EXIST (SELECT ContactID
FROM Contacts

WHERE Location IN ( 'Dallas', 'Houston' )

GROUP BY ContactID

HAVING count( ContactID ) > 1

)

|||

Duncan,

How about

select

<contact columns>

from <table>

where <city> = 'houston'

intersect

select

<contact columns>

from <table>

where <city> = 'dallas'

That way you only get the contacts with offices in both cities.

Dan

P.S. I think INTERSECT is only available in SQL Server 2005.

|||

( Arnie: give you code another look. )

(Also, won't INTERSECT result in zero rows returned in all cases? Do you mean Union?)

|||

INTERSECT should work if no <city> information is in the selected columns.

He wants entries that match BOTH the Dallas and Houston criteria.

Dan

|||I suppose your query is amorphous enough that it isn't clear whether or not you are including the LOCATION column, but If both of the queries that compose the INTERSECT include the LOCATION column -- 'Dallas' in one case and 'Houston' in the other -- won't the INTERSECT will exclude them? In fact won't the INTERSECT operator exclude the rows if there is any variation between them?|||

Thanks Kent -it was early...

This will work for you in both SQL 2000 and SQL 2005.

Code Snippet

DECLARE @.MyContacts table
( ContactID int,
City varchar(20)
)

SET NOCOUNT ON

INSERT INTO @.MyContacts VALUES ( 1, 'Dallas' )
INSERT INTO @.MyContacts VALUES ( 2, 'Houston' )
INSERT INTO @.MyContacts VALUES ( 2, 'Dallas' )
INSERT INTO @.MyContacts VALUES ( 1, 'Austin' )
INSERT INTO @.MyContacts VALUES ( 3, 'Houston' )
INSERT INTO @.MyContacts VALUES ( 3, 'Beaumont' )

SELECT *
FROM @.MyContacts
WHERE ContactID IN ( SELECT ContactID
FROM @.MyContacts
WHERE City IN ( 'Dallas', 'Houston' )
GROUP BY ContactID
HAVING count( DISTINCT City) > 1
)

ContactID City
-- --
2 Houston
2 Dallas

|||

Dan:

I just got done kicking myself for giving you a bit of "business" -- and you sure don't deserve it. I sometimes have problems with "pet" dislikes. I didn't realize that EXCLUDE and INTERSECT might be among them but I am certainly behaving that way. Yes, the truth is to some extent I try to avoid EXCLUDE and I feel that sometimes INTERSECT can get you in trouble too. I am sorry that I gave you a bit of stuff over this. Please forgive me.

Kent

|||

DanR1 wrote:

INTERSECT should work if no <city> information is in the selected columns.

He wants entries that match BOTH the Dallas and Houston criteria.

Dan

However, if he does indeed want the <city> information included in the resultset, INTERSECT will not work for him without wrapping in an outer query.

|||

Kent,

I also admit that my post to which you responded did not make it clear that "city-related" information must be excluded in order for the query to work. I presumed that the initial post in this thread was from someone who would realize that. (Sometimes I like to say only enough to help someone along, until I realize that I should post more details.)

I like to think of INTERSECT in terms of Venn diagrams. I think it is an incredibly useful set-theoretic capability to have in SQL Server.

I also see EXCEPT as a very useful function, e.g., to use when comparing a new dataset (table) received to see if it differs from the existing dataset. (I used it a lot with ORACLE, in the form of MINUS.)

You are certainly forgiven, but I don't think you did anything that required you to ask for forgiveness.

Cheers.

Dan

|||Thanks, Dan. Cheers. :-)|||Arnie, your queries has a typical mistake - what about a contact who has two offices in Dallas and none in Houston?

However, it can be easily fixed - replace HAVING() line in your query with this one:

having count(distinct city) > 1|||

Ennor,

Right after I hit the [Post] button, I actually thought about that. But from the OP, I assumed that wouldn't be an issue -and if if was, it would come up and we would 'fix' it.

However, your suggested alteration does indeed clear up the situation and make it a more robust solution.

Thanks for mentioning it.

|||

Guys

Thanks for all the replies, using the "where in" works a treat, wasn't sure that would work for this but, seems to.