Mismatch between the total number of rows in a sql join

Question

When I join entries from the points table (n = 34, 436) to the mid_pts table (n=1.98 million) the total number of rows of the table_joined is n = 26,016. However, when I add the unjoined_pts table of n = 9,627 to the table_joined it doesn't match the original 'point' table number, it exceeds it by 1207.

The following are examples of the tables I have:

points table:

LIC_LI_NO	DESCRIPTOR	Attribute 1	Attribute 2	Attribute 3
11112-01	04-81-09-01	xx	xyz	xx
11112-01	04-81-09-01	xyz	x	x
11119-03	04-81-09-01	xx	xx	xyz
11117-05	04-81-09-01	xx	xyz	xyz

mid_pts table

LIC_LI_NO	ORIG_LICLI	DESCRIPTOR
11112-01	11112-01	04-81-09-01
11112-01	11112-01	04-81-09-01
11119-03	11119-03	04-81-09-01
129517-05	11117-05	04-81-09-01
10000-01	10000-01	04-81-09-01
100232-01	100232-01	04-81-09-01
19-03	19-03	04-81-09-01
117-05	117-05	04-81-09-01
112-01	112-01	04-81-09-01

The table below is what I am expecting, but for some reason, as suggested by Laurenz Albe, a single row in my points table can relate to several rows in the mid_pts table. I would like each row in the points table to join to just one candidate row in the mid_pts table.

table_joined

LIC_LI_NO	ORIG_LICLI	DESCRIPTOR	Attribute 1	Attribute 2	Attribute 3
11112-01	11112-01	04-81-09-01	xx	xyz	xx
11112-01	11112-01	04-81-09-01	xyz	x	x
11119-03	11119-03	04-81-09-01	xx	xx	xyz
129517-05	11117-05	04-81-09-01	xx	xyz	xyz

CREATE TABLE table_joined AS
        SELECT m.geom, m.lic_li_no, m.orig_licli, p.id, p.descriptor, ... 
        FROM mid_pts m
        JOIN point p            
            ON CASE                                                     
            WHEN p.lic_li_no = m.lic_li_no THEN 1
            WHEN p.lic_li_no = m.orig_licli THEN 1
            ELSE 0 END = 1
            AND 
            (p.descriptor = m.descriptor)
    ;

When evaluated how many points didn't joined I get 9,627.

CREATE TABLE unjoined_pts AS 
        SELECT
        p.*
        FROM point p                                                  
        WHERE NOT EXISTS (SELECT * FROM  table_joined m 
                          WHERE m.id = p.id)                    
;

Erwin Brandstetter · Accepted Answer · 2023-03-23T18:09:36.627

I would like each row in the points table to join to just one candidate row in the mid_pts table.

Simple solution:

SELECT DISTINCT ON (p.ctid)
       m.geom, m.lic_li_no, m.orig_licli, p.id, p.descriptor, ... 
FROM   point p
JOIN   mid_pts m USING (descriptor)
WHERE  p.lic_li_no IN (m.lic_li_no, m.orig_licli);

This picks one arbitrary row among matching rows in mid_pts for every qualifying row in table point - if it has any match at all after the INNER JOIN. (You may want LEFT JOIN to presesrve oints without match? Move filter expressions to the join clause in this case.)

Since you didn't disclose table definitions, I am falling back to ctid to uniquely identify rows. (Use the PK instead if there is one.)

For your very few matches per point (close to one), DISTINCT ON should perform very well. See:

Select first row in each GROUP BY group?

About ctid:

Is the system column "ctid" legitimate for identifying rows to delete?

Mismatch between the total number of rows in a sql join

1 Answers1