CODEWITHSUNDEEP
phpmysql
Ryan
Ryan

Reputation: 1164

SQL - IF/ELSE Logic depending on table column

So, as you may have guessed, I am writing a query to select data from a database. And, as you may have also guessed, I am writing it with the logic of an If/Else statement. Obviously, this isn't as simple in SQL as we all wish it to be. To put the logic into simple terms, here's some sort of pseudo-code version:

SELECT * FROM table1 INNER JOIN table2 on col2 = col1
// Some other JOINS and irrelevant data stuff
if (table2.conditionalColumn == 'x') {
    JOIN table3 ON col3 = col2 WHERE a = 1, b = 2, c = 3
} else {
    JOIN table3 ON col3 = col2 JOIN table4 ON col4 = col3
}

I understand that the above query has no resemblance to a proper SQL query in the slightest. What I am trying to find out is how to work around having a column which has a value which determines whether to JOIN to one table, or to join to another table. The whole concept of this is similar to the friend-of-friend system for social networks.

Please feel free to ask for any further explanation of the issue if need be. I have attempted using some ORlogic, but I got completely stuck. How can I solve this issue?

Thanks!

Edit - To make it easier to understand my goal, I have added a scenario below:

I want to select data from a table named projects if the user has a row inside of the projects_members table, OR they have a row in the the teams table, and that team has a row inside of projects_members. There is a column inside of projects_members named memberType, which is set to 'user' or 'team'.

Upvotes: 0

Views: 68

Answers (2)

Ali Sheikhpour
Ali Sheikhpour

Reputation: 11055

Move the condition inside the join conditions:

SELECT * FROM table1 INNER JOIN table2 on col2 = col1
X JOIN table3 t3 ON (col3 = col2 and table2.conditionalColumn = 'x'  
and a = 1 and b = 2 and c = 3) 
X JOIN table3 t32 ON (col3 = col2 and table2.conditionalColumn <> 'x')
X JOIN table4 t4 ON (col4 = col3 and table2.conditionalColumn <> 'x')

Upvotes: 1

Ryan
Ryan

Reputation: 1164

So, I fiddled about in my phpMyAdmin query builder for a bit, and I found a solution to the issue I had. Here's the solution query in the style of the scenario given to the problem:

SELECT
    *
FROM
    users_data t1
INNER JOIN
    teams_members t2 ON t2.memberId = t1.userId
LEFT JOIN
    projects_members p1 ON (
        p1.memberType = 'team' AND p1.memberId = t2.teamId
    )
LEFT JOIN
    projects_members p2 ON (
        p2.memberType = 'user' AND p2.memberType = t1.userId
    )
WHERE
    t1.userName = 'foo'

Upvotes: 1

Related Questions