Vfrdtyky

I am trying to get the account number for a product by joining a table.

https://www.db-fiddle.com/f/q6GJFqFqrNrDgMd3fnmEwG/3

My account table is structured like:

| account_no | line_number | content |

| ---------- | ----------- | ------- |

| CQ01       | 5           | CUST1   |

| CQ01       | 6           | Q       |

| CQ88       | 5           | CUST1   |

| CQ88       | 6           | P       |

| CQ22       | 5           | CUST2   |

| CQ22       | 6           | P       |

My product table is structured like

| warehouse | product | customer | location |

| --------- | ------- | -------- | -------- |

| 55        | ABC DEF | CUST1    | P        |

My query I have built is

select p.*, a.account_no from products p

left join accounts a on 

    (a.content = p.customer and a.line_number = 5)

    and

    (a.content = p.location and a.line_number = 6);

unfortunately results in a NULL for account_no

| warehouse | product | customer | location | account_no |

| --------- | ------- | -------- | -------- | ---------- |

| 55        | ABC DEF | CUST1    | P        |            |

which is not the result I am after.

What am I doing wrong?

Thanks

EDIT:

My expected output would be:

| warehouse | product | customer | location | account_no |

| --------- | ------- | -------- | -------- | ---------- |

| 55        | ABC DEF | CUST1    | P        | CQ88       |

As my accounts table only has account_no = 'CQ88' when line_number 5 is 'CUST1' and line_number 6 = 'P'

The problem is with your ON conditions:

(a.content = p.customer and a.line_number = 5)

and

(a.content = p.location and a.line_number = 6);

Since you have used an AND operator, so both conditions need to be True for a match to be found. And there's no single row in Accounts table having line number both 5 and 6.

So, change and to or. It should solve your issue.

Let me know if it works.

I am guessing you want two joins:

select p.*,

       a5.account_no as customer_account_no,

       a6.account_no as location_account_no

from products p left join

     accounts a5

     on a5.content = p.customer and

        a5.line_number = 5 left join

     accounts a6

     on a6.content = p.location and

        a6.line_number = 6;

I solved it.

I needed to re-structure my accounts table so there were one row per account_no

https://www.db-fiddle.com/f/q6GJFqFqrNrDgMd3fnmEwG/6

I then performed a simple join

select p.*, new_accounts.account_no from products p

left join (      

select distinct a.account_no, concat(b.content , '-',c.content) as 'custloc' from accounts a

left join (select * from accounts where line_number = 5) b on a.account_no = b.account_no

left join (select * from accounts where line_number = 6) c on a.account_no = c.account_no

  ) new_accounts ON concat(p.customer,'-',p.location) = new_accounts.custloc;

resulted in

| warehouse | product | customer | location | account_no |

| --------- | ------- | -------- | -------- | ---------- |

| 55        | ABC DEF | CUST1    | P        | CQ88       |

select p.*, a.account_no from products p

inner join accounts a on 

    (a.content = p.customer and a.line_number = 5)

inner join accounts b on 

    (b.content = p.location and b.line_number = 6) and a.account_no  = b.account_no