Home Find the first record according to a chain of JOIN rules in SQL
Reply: 3

Find the first record according to a chain of JOIN rules in SQL

Sebastian Widz
1#
Sebastian Widz Published in 2017-12-07 23:28:03Z

I need to find a "best fit" record by using a multi-field relation. I need to JOIN based on 3 fields, if this fails use 2 fileds, and if this fails use 1-filed condition, etc. Such chain can be long, fields can vary, etc. How to achieve this.

Below I present some ideas but somehow feel this could be done better.

First way (I don't like the UNION because I guess the engine will execute them all regardless of the LIMIT 1 clause which could work as "stop on first found":

SELECT A1, A2, A3, B4 FROM A
LEFT OUTER JOIN (SELECT B4 FROM
      ((SELECT B4, 1 AS ORD FROM B 
            WHERE B.B1 = A.A1 AND B.B2 = A.B2 AND B.B3 = A.A3)
      UNION ALL
      (SELECT B4, 2 AS ORD FROM B 
            WHERE B.B1 = A.A1 AND B.B2 = A.B2)
      UNION ALL
      (SELECT B4, 3 AS ORD FROM B 
            WHERE B.B1 = A.A1)) ORDER BY ORD ASC LIMIT 1); 

Second way (here I don't like the repeated SELECT IN WHEN and again in THEN section, also it is only good when a single field is returned

SELECT A1, A2, A3,
    CASE 
        WHEN EXISTS 
            (SELECT B4 FROM B WHERE B.B1 = A.A1 AND B.B2 = A.B2 AND B.B3 = A.A3) 
            THEN 
            (SELECT B4 FROM B WHERE B.B1 = A.A1 AND B.B2 = A.B2 AND B.B3 = A.A3 LIMIT 1)
        WHEN EXISTS 
            (SELECT B4 FROM B WHERE B.B1 = A.A1 AND B.B2 = A.B2) 
            THEN 
            (SELECT B4 FROM B WHERE B.B1 = A.A1 AND B.B2 = A.B2 LIMIT 1)
        WHEN EXISTS 
            (SELECT B4 FROM B WHERE B.B1 = A.A1) 
            THEN (SELECT B4 FROM B WHERE B.B1 = A.A1 LIMIT 1)
        ELSE NULL
    END AS B4
FROM A;

I use postgreSQL 9.6 but the questions should be treated as standard SQL question.

Code to test:

CREATE TABLE A (A1 INT, A2 INT, A3 INT);
CREATE TABLE B (B1 INT, B2 INT, B3 INT, B4 VARCHAR(2));

INSERT INTO A VALUES 
    (10, 20, 30),
    (11, 21, 31),
    (12, 22, 32),
    (13, 23, 33),
    (14, 24, 34);

INSERT INTO B VALUES 
    (10, 20,    30, 'A1'),
    (11, 21,    0,  'B'),
    (12, 0,     0,  'C'),
    (10, 23,    30, 'A2'),
    (14, 0,     34, 'D');

/*
EXPECTED RESULT
10, 20, 30, A1
11, 21, 31, B
12, 22, 32, C
*/
Gordon Linoff
2#
Gordon Linoff Reply to 2017-12-08 12:15:15Z

You can use left join. Here is one way:

select a.A1, a.A2, a.A3, coalesce(b1.B4, b2.b4, b3.b4) as b4
from A left join
     b b1
     on b1.b1 = a.a1 and b1.b2 = a.b2 and b1.b3 = a.b3 left join
     b b2
     on b1.b1 = a.a1 and b1.b2 = a.b2 and b1.a1 is null left join
     b b3
     on b1.b1 = a.a1 and b2.b1 is null and b1.b1 is null;

The above could return multiple rows. If you only want one row, then you can try:

select a.*,
       (select b.b4
        from b
        where b.b1 = a.a1
        order by (b.b2 = a.a2 and b.b3 = a.a3)::int desc,
                 (b.b2 = a.a2)::int desc
        limit 1
       ) as b4
from a;

This is not as efficient. And, the query can be phrased as a lateral join -- particularly useful if you want to return more than one value.

Used_By_Already
3#
Used_By_Already Reply to 2017-12-08 12:00:44Z

left join lateral may work for you on this as it allows limiting the results to one row (each):.

CREATE TABLE table1 (A1 INT, A2 INT, A3 INT);
CREATE TABLE table2 (B1 INT, B2 INT, B3 INT, B4 VARCHAR(10));

INSERT INTO table1 VALUES 
    (10, 20, 30),
    (11, 21, 31),
    (12, 22, 32),
    (13, 23, 33),
    (14, 24, 34);

INSERT INTO table2 VALUES 
    (10, 20,    30, 'A1'),
    (11, 21,    0,  'B'),
    (12, 0,     0,  'C'),
    (10, 23,    30, 'A2'),
    (14, 0,     34, 'D');

select
a1,a2,a3,coalesce(ljl1.b4,ljl2.b4,ljl3.b4)
from table1
left join lateral (
  select b4 from table2
  where table1.a1 = b1 and table1.a2 = b2 and table1.a3 = b3
  order by b1, b2, b3, b4
  limit 1
  ) ljl1 ON true
left join lateral (
  select b4 from table2
  where table1.a1 = b1 and table1.a2 = b2
  order by b1, b2, b3, b4
  limit 1
  ) ljl2 On true
left join lateral (
  select b4 from table2
  where table1.a1 = b1
  order by b1, b2, b3, b4
  limit 1
  ) ljl3 ON true
a1 | a2 | a3 | coalesce
-: | -: | -: | :-------
10 | 20 | 30 | A1      
11 | 21 | 31 | B       
12 | 22 | 32 | C       
13 | 23 | 33 | null    
14 | 24 | 34 | D       

dbfiddle demo here

You can add where coalesce(ljl1.b4,ljl2.b4,ljl3.b4) IS NOT NULL if required to more closely match the expected result, but I'm not sure why his row isn;t in your expected result:

14 | 24 | 34 | D       
Angelo Capozzi
4#
Angelo Capozzi Reply to 2017-12-08 01:09:24Z

Quick Example:

SELECT * FROM (
    SELECT
    CONCAT(B1,"-",B2,"-",B3) AS B_CONDITION1,
    CONCAT(B1,"-",B2) AS B_CONDITION2,
    B1 AS B_CONDITION3,
    b.*
    FROM b
) AS b_tmp
INNER JOIN 
(
    SELECT
    CONCAT(A1,"-",A2,"-",A3) AS A_CONDITION1,
    CONCAT(A1,"-",A2) AS A_CONDITION2,
    A1 AS A_CONDITION3
    FROM a
) AS a_tmp ON 
(a_tmp.A_CONDITION1 = b_tmp.B_CONDITION1 OR a_tmp.A_CONDITION2 = b_tmp.B_CONDITION2 OR a_tmp.A_CONDITION3 = b_tmp.B_CONDITION3)

You must to see with big data it behaviour.. Sorry my english, Good Work!

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.332884 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO