CODEWITHSUNDEEP
sqlsql-server
GibboK
GibboK

Reputation: 73918

How to use two where clauses in a subquery?

I need to query a set of data (first WHERE) and based on that result sub-query with another WHERE. Using the following code I get

**Incorrect syntax near the keyword 'where'.**

Could you tell me what am I doing wrong here?

 select * from [Analytics]
    WHERE
    DateCreated >= '2014-05-01'
    AND DateCreated < '2014-06-01'
    AND Identification = 'ElementFlow'
    where exists
    (
    SELECT *
    FROM [Analytics]
    WHERE Location = 'x.DetailsAdvertisement' 
    OR Location = 'x.DetailsShop'
    OR Location = 'x.None'
    OR Location = 'x'
    )

Upvotes: 0

Views: 101

Answers (5)

Jesuraja
Jesuraja

Reputation: 3844

Try this:

SELECT  *
FROM    [Analytics]
WHERE   DateCreated >= '2014-05-01' AND DateCreated < '2014-06-01'
        AND Identification = 'ElementFlow'
        AND Location IN('x.DetailsAdvertisement', 'x.DetailsShop', 'x.None', 'x')

Upvotes: 0

ydoow
ydoow

Reputation: 3006

Use one more level, how does it sound?

SELECT * FROM 
(
  select * from [Analytics]
  where exists
    (
    SELECT *
    FROM [Analytics]
    WHERE Location = 'x.DetailsAdvertisement' 
    OR Location = 'x.DetailsShop'
    OR Location = 'x.None'
    OR Location = 'x'
    )
) a
WHERE 
  DateCreated >= '2014-05-01'
  AND DateCreated < '2014-06-01'
  AND Identification = 'ElementFlow'

Upvotes: 0

K.K.Agarwal
K.K.Agarwal

Reputation: 856

Try this by removing where clause coming two times

select * from [Analytics]
    WHERE
    DateCreated >= '2014-05-01'
    AND DateCreated < '2014-06-01'
    AND Identification = 'ElementFlow'
    AND exists
    (
    SELECT *
    FROM [Analytics]
    WHERE Location = 'x.DetailsAdvertisement' 
    OR Location = 'x.DetailsShop'
    OR Location = 'x.None'
    OR Location = 'x'
    )

Or you try this...

select * from [Analytics]
        WHERE
        DateCreated >= '2014-05-01'
        AND DateCreated < '2014-06-01'
        AND Identification = 'ElementFlow'
        AND location in ('x.DetailsAdvertisement' ,'x.DetailsShop','x.None','x')

Upvotes: 1

NcDreamy
NcDreamy

Reputation: 805

You're using the WHERE clause twice. Replace the second WHERE with AND and it should work.

select * from [Analytics]
WHERE
DateCreated >= '2014-05-01'
AND DateCreated < '2014-06-01'
AND Identification = 'ElementFlow'
AND exists
(
SELECT *
FROM [Analytics]
WHERE Location = 'x.DetailsAdvertisement' 
OR Location = 'x.DetailsShop'
OR Location = 'x.None'
OR Location = 'x'
)

Upvotes: 0

Klaus Byskov Pedersen
Klaus Byskov Pedersen

Reputation: 120937

You should change where exists to and exists and that should be it.

Upvotes: 2

Related Questions