MySQL dynamically selecting fields within join?

Question

I am looking to dynamically select specific columns within a join query in which a specific field will only be selected if the value of another is not equal to 0.

So here's what I have going on.

The query uses the following 3 tables, users - schools - campuses

I would like to select all of the data from users, and if the campus_id field of users is not equal to 0, then I would like to include the campus name field of campuses where campuses.id = users.campus_id, how would I do this?

Maybe this will help? stackoverflow.com/questions/566600/dynamic-conditional-sql-join — Ruben
– Ruben, Commented Dec 9, 2011 at 8:04
@Braydon: every result row must have the same number of columns. What do you want the value of the column to be if the campus_id is 0? Also, sample code speaks volumes. Please include the table schema (as CREATE TABLE statements), sample data (as INSERT statements), as much of the query as you have, results of the query and desired results. — outis
– outis, Commented Dec 9, 2011 at 8:45
possible duplicate of Conditional column for query based on other columns in MySQL — outis
– outis, Commented Dec 9, 2011 at 9:09

Mansuro · Accepted Answer · 2011-12-09 08:10:20Z

2

You could use CASE WHEN

SELECT column1, column2, (CASE WHEN U.campus_id<>0 THEN campus_name) FROM USERS U, CAMPUSES C
WHERE (U.campus_id <>0 AND C.id=U.campus_id)

answered Dec 9, 2011 at 8:10

Mansuro

4,6575 gold badges38 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Braydon Batungbacal Over a year ago

How would I do this if I want more than 1 where condition?

Mansuro Over a year ago

@BraydonBatungbacal the you could use CASE WHEN .... THEN ... WHEN ... THEN .... ELSE check the documentation

Stephan · Accepted Answer · 2011-12-09 11:36:22Z

0

select * from users u  
left join campuses c on (u.campus_id = c.id)

edited Dec 9, 2011 at 11:36

Stephan

4,2451 gold badge23 silver badges33 bronze badges

answered Dec 9, 2011 at 8:02

Remy

12.8k14 gold badges66 silver badges106 bronze badges

Collectives™ on Stack Overflow

MySQL dynamically selecting fields within join?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related