CODEWITHSUNDEEP
sqlsql-serverjoin
Babak
Babak

Reputation: 198

join two tables based on column with nearest number

I have table A like this:

id |stuff| p_year | profit
---|-----|--------|-------
1  | 1   | 2000   | 12
1  | 2   | 2005   | 15
2  | 1   | 2002   | 14
2  | 2   | 2001   | 16

and table B like this:

stuff|p_year| category
-----|------|---------
1    | 2001 | 1
1    | 2008 | 2
2    | 2002 | 1
2    | 2009 | 2

Now, I want to join these tables based on stuff column and the biggest p_year of table A less than table B.

For example after join we have:

id|stuff|p_year_A|p_year_B|category|profit
--|-----|--------|--------|--------|------
1 | 1   | 2000   |2001    | 1      | 12
1 | 2   | 2005   | 2009   |  2     | 15
2 | 1   | 2002   | 2008   | 2      | 14
2 | 2   | 2001   | 2002   | 1      | 16

how can I do this using sql query?

Upvotes: 1

Views: 101

Answers (2)

Baum
Baum

Reputation: 95

I don't know if this work. But that would be my first attempt.

Only if p_year_B always bigger as p_year_A.

SELECT * FROM A, B WHERE p_year_B=(SELECT p_year_B FROM B WHERE p_year_A < p_year_B ORDER BY p_year_B DESC LIMIT 1);

Of course you have to adjust it ;)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269923

Your problem is quite suitable for a lateral join. However, not all databases support such joins.

Absent that, you can regular joins:

select a.*, b.*
from a join
     b
     on a.stuff = b.stuff
where b.year = (select max(b2.year)
                from b
                where b2.stuff = a.stuff and b2.year < a.year
               );

In SQL Server, you would do:

select a.*, b.*
from a outer apply
     (select top 1 b.*
      from b
      where b.stuff = a.stuff and b.year < a.year
      order by b.year desc
     ) b;

Upvotes: 2

Related Questions