CODEWITHSUNDEEP
mysqlsql
jafar jafarnezhad
jafar jafarnezhad

Reputation: 39

How to join two tables with condition on their columns?

I have two tables:

Table A : 

id | key | price

Table B: 

id | key | min | max

I want the rows from table B that min and max column contain the price column from table A. In other words price is greater than min and lower than max.
I used this code but didnt return the true result : 

SELECT * FROM B INNER JOIN A ON A.key=B.key where A.price > B.min AND A.price < B.max;

Upvotes: 0

Views: 53

Answers (3)

Dinesh Saxena
Dinesh Saxena

Reputation: 46

Use this mysql query to fetch the results and make sure to set the price column to INT or FLOAT.

SELECT 
    table1.id,table1.key,table1.price,
    table2.min,table2.max
FROM
    table1
        INNER JOIN
    table2 ON table1.key = table2.key
        AND (table1.price > table2.min
        AND table1.price < table2.max);

Upvotes: 1

jafar jafarnezhad
jafar jafarnezhad

Reputation: 39

Ok I found the problem .
the price Column type was Varchar . I changed it to float and now the query return the true result.

Upvotes: 0

Erandra Jayasundara
Erandra Jayasundara

Reputation: 1

try

SELECT * FROM B LEFT JOIN A ON B.key=A.key where A.price > B.min AND A.price < B.max;

Left join will return the values in the left table. In here it's B

it's shown here https://www.w3schools.com/sql/sql_ref_join.asp

Upvotes: 0

Related Questions