CODEWITHSUNDEEP
sqlsql-server
Bobby
Bobby

Reputation: 123

select from table A when condition is met in table B

Not the best title hence why I was unable to find a solution that would fix mine, point me in the direction if you know of one.

Currently I have the below query,

SELECT PRODUCT_NAME
      ,LIVE
      ,LOCATION
FROM PRODUCT_TABLE
WHERE ORDER = 'ONLINE'
AND LIVE = '0' OR '1'

This essentially is a really simple query that pulls back a lot of data in which I've been using Excel to drill down to what I need however as I'm sure you can imagine a really tedious process so preferably prefer to do it straight with SQL as I know it can be done just my knowledge has completely disappeared after not using it in a while.

But essentially what I'm wanting to achieve is looking for all products online that are live at a location (0 means YES in the above query) and have matching product name at another location that isn't live (in this case not live = 1).

For example below is some data that is formatted in a similar sort of way.

LOCATION    LIVE    PRODUCT_NAME
BERLIN      0       CHAIR
LONDON      1       CHAIR
PARIS       0       LAMP
PARIS       0       SOFA
WARSAW      1       CHAIR
MADRID      0       CHAIR
MANCHESTER  1       SOFA

If someone could provide a solution or point me in the right direction that would be great, thanks!

Upvotes: 0

Views: 403

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You want something like this:

select pt.*
from product_table pt
where pt.live = '0' and
      exists (select 1
              from product_table pt2
              where pt2.product_name = pt.product_name and
                    pt2.location <> pt.location and
                    pt2.live = '1'
             );

Upvotes: 2

Ion Sapoval
Ion Sapoval

Reputation: 635

SELECT DISTINCT
       pt.PRODUCT_NAME
      ,pt.LIVE
      ,pt.LOCATION
FROM PRODUCT_TABLE pt
LEFT JOIN PRODUCT_TABE pt2
    on pt.NAME = pt2.NAME 
        AND pt2.LIVE = 1 
        AND pt.LOCATION != pt2.LOCATION
WHERE pt.ORDER = 'ONLINE'
AND pt.LIVE = '0'
AND pt2.NAME IS NOT NULL

Upvotes: 0

Related Questions