CODEWITHSUNDEEP
sqlsql-server
CSharpened
CSharpened

Reputation: 12574

SQL: Select A when in A and not B or select B when in A and B

SQL is not my strong point and I am struggling to find a solution to this issue. I am trying to figure out how I can get a result set based on the following logic. Select record A when A is not in B OR select B if the record appears in B and A. I tried the following union which returns me all the records that match from the current day in the two tables but I cannot figure out how to pull the data I need from the two tables.

SELECT 'a',PurchaseOrderLine.iPurchaseOrderLineId, sProductDescription 
FROM PurchaseOrderLine 
WHERE PurchaseOrderLine.dRequiredDate  = convert(date, getdate())
UNION 
SELECT 'b',PurchaseOrderLine.iPurchaseOrderLineId, sProductDescription 
FROM GoodsIn
INNER JOIN PurchaseOrderLine 
    ON PurchaseOrderLine.iPurchaseOrderLineId = GoodsIn.iPurchaseOrderLineId
WHERE GoodsIn.dDateDelivered =  getdate())

Upvotes: 0

Views: 906

Answers (3)

James S
James S

Reputation: 3588

You can do a left outer join, and use a ISNULL or CASE statement in the select to return the required values.

I'll demonstrate:

SELECT 
    CASE WHEN b.iPurchaseOrderLineId IS NOT NULL THEN 'b' ELSE 'a' END AS [Source],
    a.iPurchaseOrderLineId,
    ISNULL(b.sProductDescription, a.sProductDescription) AS [sProductDescription]
FROM PurchaseOrderLine AS a
    LEFT JOIN GoodsIn AS b ON a.iPurchaseOrderLineId = b.iPurchaseOrderLineId
                           AND b.dDateDelivered = GETDATE()
WHERE b.iPurchaseOrderLineId IS NOT NULL 
      OR a.dRequiredDate = CONVERT(DATE, GETDATE())

Hope that helps!

Upvotes: 2

Endrju
Endrju

Reputation: 2456

If I understand you correctly, assuming GoodsIn=B, you may try something in this fashion.

SELECT 'a',PurchaseOrderLine.iPurchaseOrderLineId, sProductDescription 
FROM PurchaseOrderLine 
LEFT JOIN GoodsIn
    ON PurchaseOrderLine.iPurchaseOrderLineId = GoodsIn.iPurchaseOrderLineId
WHERE PurchaseOrderLine.dRequiredDate  = convert(date, getdate())
AND GoodsIn.iPurchaseOrderLineId IS NULL
UNION 
SELECT 'b',PurchaseOrderLine.iPurchaseOrderLineId, sProductDescription 
FROM GoodsIn
INNER JOIN PurchaseOrderLine 
    ON PurchaseOrderLine.iPurchaseOrderLineId = GoodsIn.iPurchaseOrderLineId
WHERE GoodsIn.dDateDelivered =  getdate());

You could also try literally as you described (assuming sProductDescription is in PurchaseOrderLine):

"Select record A when A is not in B"

SELECT 'a',PurchaseOrderLine.iPurchaseOrderLineId, sProductDescription 
FROM PurchaseOrderLine 
WHERE iPurchaseOrderLineId NOT IN(SELECT iPurchaseOrderLineId FROM GoodsIn)

or in this way:

SELECT 'a',PurchaseOrderLine.iPurchaseOrderLineId, sProductDescription 
FROM PurchaseOrderLine 
WHERE NOT EXISTS(SELECT * FROM GoodsIn 
    WHERE GoodsIn.iPurchaseOrderLineId=PurchaseOrderLine.iPurchaseOrderLineId)

or using EXCEPT:

SELECT 'a',PurchaseOrderLine.iPurchaseOrderLineId, sProductDescription 
FROM PurchaseOrderLine 
EXCEPT 
SELECT 'a', iPurchaseOrderLineId, sProductDescription 
FROM GoodsIn;

Just hints, tailor them to your needs.

Upvotes: 1

SQL HELP
SQL HELP

Reputation: 131

Hope This will help You: Just an example similar to you.

create table A(id int  , name char(12))
go

create table B(id int  , name char(12))
go

insert into A values (1,'ABC'),(3,'WXY')
insert into B values (1,'ABC'),(2,'AAA')


SELECT a.id,a.name FROM A EXCEPT SELECT * FROM B
UNION  
SELECT a.id,a.name FROM A inner join b on a.id=b.id and a.name=b.name

Thanks!!!

Upvotes: 1

Related Questions