Mysql Query Left Join with Not in

Question

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

Akash · Accepted Answer · 2013-04-17 18:37:37Z

2

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

edited Apr 17, 2013 at 18:37

answered Apr 17, 2013 at 18:32

Akash

5,02212 gold badges44 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

amal Over a year ago

thankx for your reply. But unfortunately i didnt get the expected result. I edited the original post again. Could you look at it again. Hope it will give better idea

Quassnoi Over a year ago

@user2033382: this query does exactly what you asked for in your post. Please provide some sample data and expected output (update your post with them).

amal Over a year ago

Yes Answer is nearly correct. But it gives multiple loanIDs. I want to do above for a given loanID. If that LoanID not exists on table 1, then i need all itemnames belongs to that loanID. else if that loanID already exsists on table2 but balanceDue >0 then also i need itemnames for that loanid.

Akash Over a year ago

@user2033382 You could add append the condition to where clause as table1.loanID='LN3'

fthiella · Accepted Answer · 2013-04-17 20:36:27Z

1

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.

edited Apr 17, 2013 at 20:36

answered Apr 17, 2013 at 18:28

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

5 Comments

amal Over a year ago

thankx for your reply. But unfortunately i didnt get the expected result. I edited the original post again. Could you look at it again. Hope it will give better idea

fthiella Over a year ago

@user2033382 can you post some sample data? do you need to show itemNames from both table1 and table2? and what if there are some rows from table2 that have balanceDue=0 and some others with balanceDue>0?

amal Over a year ago

@ fthiella I just posted some sample data. Im sorry i dont know how to make it look like table. If loanID exists on table1 but not in table2, in that case all itemnames belongs to that loanid must be produce. Unless, if that loanID already exists on table2 and balancedue > 0, then also itemnames with balance>0 must be produce. I updated original post with sample data

fthiella Over a year ago

@user2033382 i've updated my answer, i'm not really sure if it is correct, i hope it's okay now

amal Over a year ago

Wow. it works finally. I got expected result from 2nd Query. Thanx lot. Appreciate your effort.

Monzur · Accepted Answer · 2013-04-17 18:44:30Z

0

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=?

answered Apr 17, 2013 at 18:44

Monzur

1,42915 silver badges12 bronze badges

1 Comment

amal Over a year ago

thankx for your reply. But unfortunately i didnt get the expected result. I edited the original post again. Could you look at it again. Hope it will give better idea

Collectives™ on Stack Overflow

Mysql Query Left Join with Not in

3 Answers 3

4 Comments

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related