1

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)                    
;
0

1 Answer 1

1

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:

About ctid:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.