CODEWITHSUNDEEP
sqlsqlite
Chris
Chris

Reputation: 13

Conditional JOIN produces two many rows

I am trying to join two tables like these:

Table A

A1, A2, A3
A, 1, 35
A, 1, 70
A, 1, 105

Table B

B1,B2, B3
B, 1, 30
C, 1, 32
D, 1, 40
E, 1, 55
F, 1, 60
G, 1, 77
H, 1, 80

The expected result is like this:

A, 1, 35, C, 32
A, 1, 70, F, 60
A, 1, 105, H, 80

That is, for every row in table A, I want to pick just one row from table B, which should be the row with the value of B3 smaller, but closest to the value of A3.

So far, I have tried the following query:

SELECT A1, A2, A3, B1, B3 FROM A JOIN (SELECT B1, B2, B3 FROM B ORDER BY B2, B3 DESC) AS A ON A.A2 = B.B2 AND A.B3 < A.A3

However, this results in a table as follows:

A, 1, 35, B, 30
A, 1, 35, C, 32
A, 1, 70, B, 30
A, 1, 70, C, 32
A, 1, 70, D, 40
A, 1, 70, E, 55

and so forth. I also tried to add a LIMIT 1 to the inner SELECT, but that does not produce any results. If I run the inner query by itself (with LIMIT 1), I do get the expected result. How can I change the query to produce the desired result? I am using sqlite at the moment, but would like to write this in a way that is portable to other DBMS.

Upvotes: 1

Views: 157

Answers (3)

Geoff
Geoff

Reputation: 8850

I don't have SQLite available, but the following works in SQL Server; you should be able to replace TOP with LIMIT.

You should be able to get it from a nested query in the select, as long as you specify TOP 1:

select *, 
   (select top 1 B3 
    from B 
    where B3 <= A3
    order by B3 desc)
from A

EDIT: To add the extra column from B requires you to nest the above query into a subquery:

select t.*, B.B1 from (
select *, 
   (select top 1 B3 
    from B 
    where B3 <= A3
    order by B3 desc) as B3
from A
) as t join B on B.B3 = t.B3

Upvotes: 0

JodyT
JodyT

Reputation: 4412

Try the following query:

SELECT 
  a.A1,
  a.A2, 
  a.A3,
  (
    SELECT b.B3
    FROM TableB AS b
    ORDER BY ABS(a.A3 - b.B3)
    LIMIT 1
  ) AS x
FROM TableA AS a;

SQL Fiddle

SQL Fiddle wouldn't let me use sqlite cause my browser doesn't support it, so I hope this works for you.

EDIT: This should actually give you the closest value. 77 is closer to 70 than 60... but I don't know if this is exactly what you wanted.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270091

When you want exactly one row from the initial table, then you can think about using a subquery in the select clause:

select a.*,
       (select b3
        from TableB b
        where b.b3 <= a.a3
        order by b3 desc
        limit 1
       ) as b3
from TableA a

The subquery itself is called a "correlated subquery", because the where clause references both the inner table (b) and the outer table (a). It orders the table b results by b3, filtering out all but the values less than or equal to a.a3 in each row. The limit 1 returns a single value, which because of the sort is the closest value less than or equal to the value in a.a3.

Your question says the closest value, but the example results have the greatest value less than or equal to.

Upvotes: 1

Related Questions