Select Statement in SQL Server

KeithF

Hi Folks, I have a quick question in relation to how i select data from a table, as seen in the table below i want to get rows where Person1 & Person2 are both on the same row, so in the table below i would just want Rows 1 & 2. Table: ID | Person1 | Person2 | . . . 1 | ABC | DEF | . . . 2 | DEF | ABC | . . . 3 | ABC | GHI | . . . . . . I have tried the following:

Select * From MyTable Where Person1 = 'ABC' OR Person1 = 'DEF AND Person2 = 'ABC' OR Person2 = 'DEF'

This does not have the desired effect however. Where am i going wrong here?

KeithF

Nevermind figured it out, was missing brackets in the SQL statement

KeithF wrote:

Select * From MyTable Where (Person1 = 'ABC' OR Person1 = 'DEF) AND (Person2 = 'ABC' OR Person2 = 'DEF')

GuyThiebaut

This is another way of getting the same result:

Select * From MyTable Where Person1 in('ABC','DEF') AND Person2 in('ABC','DEF')

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

KeithF

Thanks GuyThiebaut, that is cleaner code.

Richard Deeming

KeithF wrote:

Where (Person1 = 'ABC' OR Person1 = 'DEF) AND (Person2 = 'ABC' OR Person2 = 'DEF')

Your query will also return rows where Person1 and Person2 are the same value. It's not obvious from your description whether that would be a problem, or even if it's possible in your database. If you only want rows which have both people on the row, try:

WHERE 'ABC' IN (Person1, Person2) And 'DEF' In (Person1, Person2)

or the slightly longer version:

WHERE (Person1 = 'ABC' And Person2 = 'DEF') Or (Person1 = 'DEF' And Person2 = 'ABC')

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

KeithF

Thanks Richard, It would not be possible for ABC to exist in Person1 and Person2 in the same row, but its no harm using the syntax you describe to circumvent it in case it were to happen.