asked on 9/13/2013

ORACLE 11G RETURNING A RESULTSET IF BOTH CONDITIONS ARE TRUE IN DIFFERENT ROWS

Table
Empid City Type Status
1245 Dallas OrderTo CheckedOut
1245 New York CheckedIn InTransit
1111 Dallas CheckedIn Temporary
1439 Dallas OrderTo CheckedOut
1439 New York CheckedIn InTransit
1880 DeRidder OrderTo CheckedOut
1883 Coushata CheckedIn InTransit

When given two Cities, for example, Dallas and New York
From these input I need to bring back the Empid that meets the following requirement:

Both must be true:
Their type and status must OrderTo and CheckedOut for Dallas and
their type and status must be CheckedIn and Intranist for New York

In this case my result set would be:
1245
1439

How would I construct the SQL to bring back this result?

Oracle Database

Last Comment

awking00

9/16/2013

PortletPaul

9/13/2013

This result from the sample data:
EMPID
1245
1439

from the query below:

SELECT
        EMPID
FROM YOURTABLE
GROUP BY
        EMPID
HAVING SUM(CASE WHEN CITY IN ('New York','Dallas') THEN 1 END) > 1
ORDER BY
        EMPID

Open in new window

visible at: http://sqlfiddle.com/#!4/dfdf9/4
{+ an edit, sorry}

cookiejar

9/13/2013

ASKER

Suppose I have this as input
1245 Dallas OrderTo CheckedOut
1245 New York CheckedIn InTransit
1111 Dallas CheckedIn Temporary
1439 Dallas OrderTo CheckedOut
1439 New York CheckedIn InTransit
1880 DeRidder OrderTo CheckedOut
1883 Coushata CheckedIn InTransit
1288 Dallas ReportsTo CheckedIn
1288 New York CheckedIn InTransit

With your query I would return 1288 as well but
I only want to return if status is ordersTo and CheckedOut for one City
and CheckedIn and InTransit for the other city.

ASKER CERTIFIED SOLUTION

PortletPaul

9/13/2013

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

cookiejar

9/13/2013

ASKER

Thank you, could you explain to me what this does?

HAVING SUM(CASE WHEN CITY IN ('New York','Dallas') THEN 1 END) > 1

PortletPaul

9/13/2013

That line is how we locate an EMPID that has BOTH cities

(i.e. If you leave out that line 1288 gets included in the result)

all it is doing is giving each base table row a value of 1 (because every row has to meet the where clause), then we add up those for each EMPID, if the result of that is 1 then the EMPID is only involved in one city not both, If the result is 2 of more then that EMPID has matching records in both cities.

des that help?

awking00

9/16/2013

select x.empid from
(select empid from table where city = 'Dallas'
and type = 'OrderTo' and status = 'CheckedOut') x,
(select empid from table where city = 'New York'
and type = 'CheckedIn' and status = 'InTransit') y
where x.empid = y.empid