Postgres SQL left outer join on the same table

Question

I have such query:

SELECT c.name, i.name FROM inv_invoice_items c 
LEFT OUTER JOIN inv_invoice_items i ON i.name = c.name
WHERE c.id_invoice = 2108 AND i.id_invoice = (SELECT id_invoice FROM inv_invoices WHERE id =   2108)

For this query I have such result:

name  | name
-------------
pen   | pen

but for select without join:

SELECT c.name FROM inv_invoice_items c
WHERE c.id_invoice = 2108

the result is:

name
------
pen
pencil

and second query:

SELECT i.name FROM inv_invoice_items i
WHERE i.id_invoice = (SELECT id_invoice FROM inv_invoices WHERE id = 2108)

gives result:

name
------
pen

I would expect for my first join query result:

name   | name
---------------
pen    | pen
pencil | NULL

How to achieve such result? I thought that way should work LEFT OUTER JOIN. Thanks for any suggestions in advance.

Ps. I need to catch differences in invoice items of corrective and corrected (related ) invoice.

Some sample data:

create table inv_invoices (id bigint, id_invoice bigint, primary key(id));
create table inv_invoice_items (id bigint, id_invoice bigint NOT NULL, name character varying(100) NOT NULL, primary key (id));
insert into inv_invoices values (2105, NULL), (2106, NULL), (2107, NULL), (2108, 2106);
insert into inv_invoice_items values (1000, 2105, 'pen'), (1001, 2105, 'pencil'), (1002,2106, 'pen'), (1003, 2107, 'rubber'), 
(1004, 2107, 'pencil'), (1005, 2108, 'pen'), (1006, 2108, 'pencil');

Remove the second Expression in AND: i.id_invoice = (SELECT id_invoice FROM inv_invoices WHERE id = 2108) . It's been useless here I guess — Pranav Kevadiya
– Pranav Kevadiya, Commented Mar 20, 2013 at 8:45
Not exactly. Removing second AND cause other result - also not expected - rows: pen, pen; pen, pen; pen, pen; pencil, pencil — robson
– robson, Commented Mar 20, 2013 at 10:00

Mahmoud Gamal · Accepted Answer · 2013-03-20 10:02:36Z

3

~~Try this instead:~~

SELECT DISTINCT c.name, i.name FROM inv_invoice_items AS c LEFT OUTER JOIN inv_invoice_items AS i ON i.name = c.name AND c.id_invoice = 2108 LEFT OUTER JOIN inv_invoices AS i2 ON i.id_invoice = i2.id_invoice AND i2.id = 2108;

Try this:

SELECT
  t.name AS name1,
  v.name AS name2
FROM
(
  SELECT c.name 
  FROM inv_invoice_items c
  WHERE c.id_invoice = 2108
) AS t
LEFT JOIN
(
  SELECT i.name 
  FROM inv_invoice_items i
  WHERE i.id_invoice = (SELECT id_invoice 
                        FROM inv_invoices 
                        WHERE id = 2108)
) AS v ON t.name = v.name;

SQL Fiddle Demo

This will give you:

|  NAME1 |  NAME2 |
-------------------
|    pen |    pen |
| pencil | (null) |

edited Mar 20, 2013 at 10:02

answered Mar 20, 2013 at 8:44

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

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

3 Comments

robson Over a year ago

Hello Mahmoud. Now I have in the left column all items of inv_invoice_items and in the right one - several times "pen" and "pencil" when the same value is in the left column. Any suggestions?

Mahmoud Gamal Over a year ago

@robson - Try adding the keyword DISTINCT : SELECT DISTINCT c.name, i.name ... instead. Also try to post some sample data from the two tables please.

robson Over a year ago

DISTINCT didn't help. Please see my first post - I've added some sample data to ilustrate the problem compatibile with our queries. Thank you.

Collectives™ on Stack Overflow

Postgres SQL left outer join on the same table

1 Answer 1

SQL Fiddle Demo

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related