CODEWITHSUNDEEP
mysqljoin
amal
amal

Reputation: 3582

Mysql Query Left Join with Not in

For a given loanID, if that loanID already exists in table1, but not in table2 I want to select all itemnames belongs to that loanID else if that loanID exists in both tables(table1 and table2) and balanceDue>0 in that case also need to select itemnames belongs to that loanid.

table 1:

loanID, itemId, itemName, itemQty, balanceQty

table 2:

loanID, itemId, GRN, itemName, balanceDue, qty

Sample Data

Case 1

Table 1

loanID  itemId     itemName    
LN1     IT1        Item001        
LN1     IT2        Item002        
LN1     IT3        Item003        
LN2     IT1        Item001        
LN2     IT2        Item002        
LN2     IT3        Item003        
LN3     IT1        Item001        
LN3     IT2        Item002        
LN3     IT3        Item003

Table 2

loanID  itemId  itemName  balanceDue
LN1     IT1     Item001   0
LN1     IT2     Item002   0
LN1     IT3     Item003   0
LN2     IT1     Item001   0
LN2     IT2     Item002   0
LN2     IT3     Item003   1000

If I select LN3 as LoanID, since LN3 not in Table 2, I need to get all itemnames belongs to LN3 Result for LN3 must be

item001, item002, item003

Case 2

If I select LN3 as LoanID when its exists on table 2, if balanceDue>0 for any items belongs to LN3 those itemnames must be produce.

Result for LN3 must be

item001, item003

If I selected LN2 as loanID, Result for LN2 must be

item001

Table 1

loanID  itemId  itemName    
LN1 IT1 Item001        
LN1 IT2 Item002        
LN1 IT3 Item003        
LN2 IT1 Item001        
LN2 IT2 Item002        
LN2 IT3 Item003        
LN3 IT1 Item001        
LN3 IT2 Item002        
LN3 IT3 Item003

Table 2

loanID  itemId  itemName    balanceDue
LN1 IT1 Item001        0
LN1 IT2 Item002        0
LN1 IT3 Item003        0
LN2 IT1 Item001        300
LN2 IT2 Item002        0
LN2 IT3 Item003        1000
LN3 IT1 Item001        500
LN3 IT2 Item002        0
LN3 IT3 Item003        0

Upvotes: 0

Views: 70

Answers (3)

fthiella
fthiella

Reputation: 49049

SELECT
  table1.loanID,
  COALESCE(table2.itemName, table1.itemName)
FROM
  table1 LEFT JOIN table2 ON table1.loanID = table2.loanID
  AND balanceDue>0

Please see fiddle here. If loanID is present in table2, but there are no rows with balanceDue>0 this will return table1.itemName.

Edit

I think you probably need this query:

SELECT
  table1.itemName
FROM
  table1 LEFT JOIN table2
  ON table1.loanID = table2.loanID
  AND table1.itemId = table2.itemId                   
WHERE
  table1.loanID='LN3' AND
  COALESCE(table2.balanceDue>0, TRUE)

or this:

SELECT DISTINCT
  COALESCE(table2.itemName, table1.itemName)
FROM
  table1 LEFT JOIN table2
  ON table1.loanID = table2.loanID                   
WHERE
  table1.loanID='LN3' AND
  COALESCE(table2.balanceDue>0, TRUE)

Fiddle here.

Upvotes: 1

Monzur
Monzur

Reputation: 1435

SELECT T1.loanId, T1.itemName
FROM table1 AS T1
WHERE T1.loanId IN 
    (SELECT loanId FROM table2 WHERE loanId=T1.loadId AND balanceDue>0)
AND T1.loanId=?

Upvotes: 0

Akash
Akash

Reputation: 5012

Try

SELECT
  table1.loanID,
  GROUP_CONCAT( COALESCE( table2.itemName,table1.itemName ) )
FROM
  table1 
  LEFT JOIN table2 ON table1.loanID = table2.loanID
WHERE
  table2.loanID IS NULL OR balanceDue>0
GROUP BY
  table1.loanID

Upvotes: 2

Related Questions