Efficient SQL table joining or query in POSTGRES ?

Question

I have the following tables

table 1

 ID1   YEAR1       
 1      1980 
 2      1964 
 3      1910

table 2

 ID2  YEAR2   VALUE 
 1     2000     A 
 1     1900     B 
 2     1950     C
 2     1900     B 
 3     2000     C 
 3     1970     B 
 4     1900     D 
 4     1800     E

I would like to join / query these tables to:

for each ID1 in table 1 match, add a column named VALUE - so the column names would be A, B, ... and so on, and the column would be either TRUE(T) or FALSE(F).

The column A ... would be TRUE, if there was ID1 matching ID2 from table 2, and the YEAR2 for that given row in table 2 which matched ID2 was less than YEAR1 from that table 1 having given 1, and VALUE from row having ID2 from table 2 had A

So the resultant table would be as follows:

ID1   YEAR1     A   B   C   D   E         

 1      1980    F   T   F   F   F 
 2      1964    F   T   T   F   F 
 3      1910    F   F   F   F   F

Answer 1

You have marked your question as postgresql so I will give a PostgreSQL answer, with common table expressions and a pivot table as provided by the tablefunc extension.

This is in fact a typical example of problems best expressed with the help of pivot tables.

As you see, the most tedious part is filling falses where the pivot table has nulls.

CREATE EXTENSION tablefunc;  -- If you haven’t already

WITH ct AS (
  SELECT * FROM CROSSTAB (
    'SELECT id2, t1.year1, t2.value, t2.year2 < t1.year1 
       FROM t2 JOIN t1 ON t1.id1 = t2.id2 ORDER BY 1,3',
    $$VALUES('A'::CHAR(1)),('B'),('C'),('D'),('E')$$)          
  AS ct("ID1" INT, "YEAR1" INT, 
        "A" BOOL, "B" BOOL, "C" BOOL, "D" BOOL, "E" BOOL)
) SELECT "ID1", "YEAR1", 
         ("A" AND ("A" IS NOT NULL)) AS "A",
         ("B" AND ("B" IS NOT NULL)) AS "B",
         ("C" AND ("C" IS NOT NULL)) AS "C",
         ("D" AND ("D" IS NOT NULL)) AS "D",
         ("E" AND ("E" IS NOT NULL)) AS "E",
    FROM ct;

Answer 2

You can use a query like the following:

SELECT t1.ID1, t1.YEAR1,
       COUNT(CASE WHEN t2.VALUE = 'A' AND t1.YEAR1 > t2.YEAR2 THEN 1 END) AS A,
       COUNT(CASE WHEN t2.VALUE = 'B' AND t1.YEAR1 > t2.YEAR2 THEN 1 END) AS B,
       COUNT(CASE WHEN t2.VALUE = 'C' AND t1.YEAR1 > t2.YEAR2 THEN 1 END) AS C,
       COUNT(CASE WHEN t2.VALUE = 'D' AND t1.YEAR1 > t2.YEAR2 THEN 1 END) AS D,
       COUNT(CASE WHEN t2.VALUE = 'E' AND t1.YEAR1 > t2.YEAR2 THEN 1 END) AS E,
       COUNT(CASE WHEN t2.VALUE = 'F' AND t1.YEAR1 > t2.YEAR2 THEN 1 END) AS F      
FROM Table1 AS t1
LEFT JOIN Table2 AS t2 ON t1.ID1 = t2.ID2
GROUP BY t1.ID1, t1.YEAR1

If, e.g. value of column A is 1, then this is considered as T, otherwise it is considered as a F.

Demo here

Answer 3

You can use group by as Giorgos or joins like this:

SELECT T1.ID1, T1.YEAR1
FROM TABLE1 T1
LEFT JOIN TABLE2 A ON T1.ID1 =SELECT T1.ID1, T1.YEAR1,
       CASE WHEN A.ID2 IS NOT NULL THEN 'T' ELSE 'F' END AS A,
       CASE WHEN B.ID2 IS NOT NULL THEN 'T' ELSE 'F' END AS B,
       CASE WHEN C.ID2 IS NOT NULL THEN 'T' ELSE 'F' END AS C,
       CASE WHEN D.ID2 IS NOT NULL THEN 'T' ELSE 'F' END AS D,
       CASE WHEN E.ID2 IS NOT NULL THEN 'T' ELSE 'F' END AS E
FROM TABLE1 T1
LEFT JOIN TABLE2 A ON T1.ID1 = A.ID2 AND T1.YEAR1 > A.YEAR2 AND A.VALUE = 'A'
LEFT JOIN TABLE2 B ON T1.ID1 = B.ID2 AND T1.YEAR1 > B.YEAR2 AND B.VALUE = 'B'
LEFT JOIN TABLE2 C ON T1.ID1 = C.ID2 AND T1.YEAR1 > C.YEAR2 AND C.VALUE = 'C'
LEFT JOIN TABLE2 D ON T1.ID1 = D.ID2 AND T1.YEAR1 > D.YEAR2 AND D.VALUE = 'D'
LEFT JOIN TABLE2 E ON T1.ID1 = E.ID2 AND T1.YEAR1 > E.YEAR2 AND E.VALUE = 'E'

Answer 4

I would simply use conditional aggregation and boolean types:

SELECT t1.id1,
       BOOL_OR( (t2.VALUE = 'A' AND t2.YEAR < t1.YEAR) ) as A,
       BOOL_OR( (t2.VALUE = 'B' AND t2.YEAR < t1.YEAR) ) as B,
       BOOL_OR( (t2.VALUE = 'C' AND t2.YEAR < t1.YEAR) ) as C,
       BOOL_OR( (t2.VALUE = 'D' AND t2.YEAR < t1.YEAR) ) as D,
       BOOL_OR( (t2.VALUE = 'E' AND t2.YEAR < t1.YEAR) ) as E,
       BOOL_OR( (t2.VALUE = 'F' AND t2.YEAR < t1.YEAR) ) as F
FROM Table1 t1 LEFT JOIN
     Table2 t2
     ON t1.ID1 = t2.ID2
GROUP BY t1.ID1;