How do I use an IF statement in an MySQL join query?

if statement in join sql server
if else condition in sql join
case statement in join condition in sql server
if condition in join mysql
sql join on condition
sql if statement
if else condition in mysql query in php
case when using join

I have a SQL query that left joins a table in different ways depending on a condition.

SELECT m.id, u.first_name AS otherUser
FROM matches AS m
IF (u.id=m.user2ID)
    LEFT JOIN users AS u ON u.id = m.user1ID
ELSE
    LEFT JOIN users AS u ON u.id = m.user2ID
ENDIF
WHERE m.user1ID=2 OR m.user2ID=2

matches is a table with integer columns user1ID and user2ID. users is a table containing users of my web application. users has a VARCHAR field called first_name.

The intention of this query is to get the names of the users matched with the current user.

However, MySQL returns this error:

#1064 - You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version for 
the right syntax to use near 'IF (u.id=m.user2ID) LEFT JOIN users 
AS u ON u.id = m.user1ID ELSE LEFT JOIN user' at line 3

Why?

Specify the condition as part of the ON clause:

SELECT m.id, u.first_name AS otherUser
FROM matches AS m
LEFT JOIN users AS u ON (u.id=m.user2ID and u.id = m.user1ID) or (u.id<>m.user2ID and u.id = m.user2ID) 
WHERE m.user1ID=2 OR m.user2ID=2

Another way to do the same thing:

SELECT m.id, u.first_name AS otherUser
FROM matches AS m
LEFT JOIN users AS u ON IF(u.id=m.user2ID,u.id = m.user1ID,u.id = m.user2ID) 
WHERE m.user1ID=2 OR m.user2ID=2

Mysql IF statement THEN JOIN - Databases, Hey i'm writing a script at the moment and i need to write a query which I've never used IF statements in mysql before and i can't find much  MySQL IF Statement. Summary: in this tutorial, you will learn how to use MySQL IF statement to execute a block of SQL code based on a specified condition. Note that MySQL has an IF() function that is different from the IF statement described in this tutorial. The IF statement has three forms: simple IF-THEN statement,

Use this query:

SELECT m.id, u.first_name AS otherUser FROM matches AS m LEFT JOIN users AS u ON u.id = m.user1ID AND u.id=m.user2ID LEFT JOIN users AS u1 ON u1.id = m.user2ID WHERE m.user1ID=2 OR m.user2ID=2

Updated query:
SELECT m.id, u.first_name AS otherUser
FROM matches AS m
LEFT JOIN users AS u ON u.id = (CASE WHEN u.id=m.user2ID 
                                   THEN m.user1ID
                                   ELSE m.user2ID
                               END)
WHERE m.user1ID=2 OR m.user2ID=2

Check this Source.

Also check this MYSQL Inner Join if statement . It might be helpful for you.

Conditional Column Join in SQL, Given write a query to pull all rows/columns from the tables below If a letter is null include all combinations of colors with shapes in the output. We can accomplish this by using a case statement in the on clause of our join. The cross join is useful for generating planning data. For example, you can carry the sales planning by using the cross join of customers, products, and years. In this tutorial, you have learned various MySQL join statements including cross join, inner join, left join and right join to query data from two tables.

You can not use the IF THEN ELSE END IF-stuff in a SELECT in this way. However, you can use it in stored procedures and functions.

I would JOIN u.id with both m.user1ID and m.user2ID and use DISTINCT to avoid duplicates.

There is a IF() which you can use in SELECTs, but you can not do flow control with IF().

MySQL 5.7 Reference Manual :: 13.2.9.2 JOIN Clause, INNER JOIN is used with an ON clause, CROSS JOIN is used otherwise. and views in the FROM clause into the outer query block (see Section 8.2.2.4, If you mix comma joins with the other join types when there is a join condition, an error​  SQL HOME SQL Intro SQL Syntax SQL Select SQL Select Distinct SQL Where SQL And, Or, Not SQL Order By SQL Insert Into SQL Null Values SQL Update SQL Delete SQL Select Top SQL Min and Max SQL Count, Avg, Sum SQL Like SQL Wildcards SQL In SQL Between SQL Aliases SQL Joins SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL Self Join SQL Union SQL Group By SQL Having SQL Exists SQL Any, All SQL Select Into SQL Insert Into Select SQL Case SQL Null Functions SQL Stored Procedures SQL

Thanks, It worked for me. Tried something else like below :

**

Create table TEMP1 (ID INT,T_MONTH INT,T_YEAR INT)
INSERT INTO TEMP1 VALUES(1,1,2001)
INSERT INTO TEMP1 VALUES(2,2,2001)
INSERT INTO TEMP1 VALUES(3,3,2001)

**

CREATE TABLE TEMP2 (T_MONTH INT,T_YEAR INT,FREQUENCY CHAR(1),VAL FLOAT)
INSERT INTO TEMP2 VALUES(1,2001,'M',1.1)
INSERT INTO TEMP2 VALUES(3,2001,'M',1.2)
INSERT INTO TEMP2 VALUES(3,2001,'Q',1.3)
INSERT INTO TEMP2 VALUES(12,2001,'A',1.4)

SELECT * FROM TEMP1 L JOIN TEMP2 H
ON L.T_YEAR = H.T_YEAR
OR (L.T_MONTH = (CASE WHEN H.FREQUENCY='M'
                       THEN H.T_MONTH
                  END)
    OR dbo.GetQuarterFromMonth(L.T_MONTH) = (CASE WHEN H.FREQUENCY = 'Q'
                        THEN dbo.GetQuarterFromMonth(H.T_MONTH)
                        END))
WHERE H.FREQUENCY = 'Q'

Here GetQuarterFromMonth is a user defined funtion which returns Quarter for a particular month.

The objective of above query is to inflate the value of Quarter for all the months in table 1. If frequency is annual then in that case the value should be inflated for all the months 1-12 of table 1 which is achieved through first join condition. In case frequency is Monthly then each month from table1 should be mapped to table2. The only unique case is handling quarter frequency.

Understanding MySQL LEFT JOIN Clause By Examples, The LEFT JOIN allows you to query data from two or more tables. The following statement shows how to use the LEFT JOIN clause to join the two tables: If there is no match, the columns of the row from the right table will contain NULL . You can use multiple tables in your single SQL query. The act of joining in MySQL refers to smashing two or more tables into a single table. You can use JOINS in the SELECT, UPDATE and DELETE statements to join the MySQL tables. We will see an example of the LEFT JOIN also which is different from the simple MySQL JOIN. Using Joins at the

You can also have two LEFT JOIN on the same table like this...

LEFT JOIN users AS u ON u.id = m.user1ID
LEFT JOIN users AS u2 ON u2.id = m.user2ID

This is an alternative to using IF statements.

IF statement in ON clause, You need to keep the comparison column outside of your CASE statement. INNER JOIN Users u ON u.id = CASE WHEN c.sender = ? then  After launching and connecting to SQL Server Management Studio, create a new login and select the database that is connected to Chartio. Grant MySQL table and column permissions. You can grant a user table and column permissions in MySQL with GRANT statements. How to Find Duplicate Values in a SQL Table

How to use a JOIN statement in MySQL, In SQL, the JOIN statement is used to combine row entries of two or more tables, based on a In the query, we specify the SELECT clause followed by the column​(s) to select, then DeptID for stuID = 6 when taking the JOIN of these tables. The SQL If Else Statement is one of the most useful decision-making statements in real-time programming. SQL If statement will test the condition first and depending upon the result it will execute the statements. If the test condition in SQL If statement is true, the statements inside the if block will be executed.

Conditional Joins in MySQL – MySQL Diary, One way to do a Conditional Join in MySQL is by using a LEFT JOIN. 2010, 1:​43 pm, by Hazan Ilan, under Cool MySQL Queries. combine the results into one column using an “IF” statement by the “SELECT” expression. Summary: in this tutorial, you will learn how to use the MySQL INNER JOIN clause to select data from multiple tables based on join conditions. The INNER JOIN matches each row in one table with every row in other tables and allows you to query rows that contain columns from both tables. The INNER JOIN is an optional clause of the SELECT statement.

MySQL, Sometimes in a single query, it is required to join different tables based on a You can use UNION clause to combine the results from several queries into one: Note that you need to do something with the returned value, e.g. compare it to 1. Your statement attempted to return the value of an assignment or test for equality, neither of which make sense in the context of a CASE/THEN clause.

Comments
  • Thanks, these two queries are generating the right number of results, but strangely returning NULL for all values in the otherUser column. It doesn't seem to detect the first names of the users, as shown here goo.gl/jg7xp
  • it is a LEFT OUTER JOIN so it seems the condition is not satisfied for any row. I have simply copy pasted the conditions from your original query. On a second look, I see something wrong with the conditions - u.id<>m.user2ID and u.id = m.user2ID. Now that would never be true! The other condition is also unlikely although not impossible. Please check your conditions again. Just use this answer as a template and plug in the correct conditions.
  • If the query works with the changed conditions pleae edit my answer and change the conditions for benefit of whoever happens to read this later
  • Thanks, this query is generating the right number of results, but strangely returning NULL for all values in the otherUser column. It doesn't seem to detect the first names of the users, as shown here goo.gl/jg7xp