CODEWITHSUNDEEP
mysqlsql
Manisha Patel
Manisha Patel

Reputation: 354

Combine two result set having no relation

Hello I have two table called table a and b and that has below data

Table a contain data

ID Value Score 
1  99.90 100   
2  98.60 200   
3  97.05 300   
4  95.06 400   
5  94.01 500

Table b contain below data 

ID Value 
1  96 
2  95

And i want result for both table and here the B.value is depend on the a.value. For example if b.value is 96 then I want the value of 97.05 means i want equal or nerest correspond higher value form a

Sample output

b.value a.value 
96       97.05
95       95.05

Upvotes: 1

Views: 60

Answers (1)

John Ruddell
John Ruddell

Reputation: 25842

if im understanding you correctly this will do what you want.

SELECT MIN(a.value), b.value 
FROM tablea a
JOIN tableb b ON a.value > b.value
GROUP BY b.value

DEMO1

EDIT:

to include other columns like the score you have to do it like so:

SELECT a_val, a.score, b_val 
FROM(
  SELECT MIN(a.value) as a_val, b.value as b_val
  FROM tablea a
  JOIN tableb b ON a.value > b.value
  GROUP BY b.value 
) t
JOIN tablea a ON a.value = t.a_val

DEMO2

Upvotes: 2

Related Questions