Join two tables on specific condition

Related searches

I need help in joining two tables with a specific condition. I want to left join Table_1 with Table_2 on the nearest Table_A.Col_A <= Table.Col_B

So I have two tables

Table_1
Col_A
1
2
6 

Table_2
Col_A | Col_B
1     | p1
4     | p2
5     | p3

Result Table
Col_A | Col_B
1     | p1
2     | p1
6     | p3

A correlated subquery handles this:

select t1.col_a,
       (select t2.col_b
        from table2 t2
        where t2.col_A <= t1.col_A
        order by t2.col_A desc
        limit 1
       )
from table1 t1;

SQL join two tables with specific condition, MySQL - How to Join Different Tables Based on Condition (Switch Join - Select teamPersons VALUES (1, 1), (1, 2); -- Team 1 and Steve participate in contest INSERT p2.name) to select the person name as an individual or team member. The table_1 and table_2 are called joined-tables. For each row in the table_1, the query find the corresponding row in the table_2 that meet the join condition . If the corresponding row found, the query returns a row that contains data from both tables.

I would try doing something like this.

SELECT DISTINCT t1.col1, t2.col2
FROM Table_1 t1 JOIN Table_2 t2 on t1.col1 <= t2.col1
WHERE --something matches

However there is not much information on this post, the more details you can provide should allow us to help you better.

MySQL, Normally, filtering is processed in the WHERE clause once the two tables have only want to create matches between the tables under certain circumstances. Sometimes in a single query, it is required to join different tables based on a condition in one of the tables. For example, you need to get all persons participating in a contest as individuals or as members of a team. Contest table points either to Team or Person table depending on the participant type:

You can put the condition in the ON clause like this:

select 
  t1.Col_A, t2.Col_B 
from Table_1 t1 left join Table_2 t2
on t2.Col_A = (select max(Col_A) from Table_2 where Col_A <= t1.Col_A)

See the demo. Results:

| Col_A | Col_B |
| ----- | ----- |
| 1     | p1    |
| 2     | p1    |
| 6     | p3    |

SQL Joins Using WHERE or ON, When our data was all in a single table, we could easily retrieve a particular row Where the join condition is met, the rows of the two tables are joined, just as in � Here are the different types of the JOINs in SQL: (INNER) JOIN: Returns records that have matching values in both tables. LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table. RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table.

SQL joins and how to use them, The match condition is commonly called the join condition. When the match conditions involve equality, that is matching exactly the contents of one column to � If you join the facilities table twice you will get what you are after: select u.* from room u JOIN facilities_r fu1 on fu1.id_uc = u.id_uc and fu1.id_fu = '4' JOIN facilities_r fu2 on fu2.id_uc = u.id_uc and fu2.id_fu = '3' where 1 and vizibility='1' group by id_uc order by u_premium desc, id_uc desc

SQL Inner Join to Two or More Tables, I'm trying to write a SQLite query that only performs a JOIN if a certain condition is met (items.super = 1). I know I could write some code in my application to figure out if super == 1 and then execute the correct query, but I'd much rather just have one query that works in both cases--whether super is 0 or 1.

Basically all of that let's us join Table1 to the Table2 on just the number column if Table1's letter column is null and join on number and letter if Table1's letter column is not null. Which results in our final answer: Try it out in SQL Fiddle for some more practice!

Comments
  • Now I get the error Correlated scalar subqueries must be aggregated
  • @sks27 . . . That error makes no sense. MySQL (and almost every other database) supports correlated subqueries like this.