SELECT multiple columns based on value of one column

Question

I want to select specific columns based on a value of one column.

Currently, what I do is get the value of a specific column and use it to make another query. Like this:

SELECT service_type FROM agreements WHERE id = 1;

I'll pass the value to a PHP variable and make another query based on the result.

Sample code:

if (service == 'internet')
SELECT ipreq, latency, ... FROM agreements WHERE id = 3 AND service_type = 'internet';
else
SELECT rates, markup, ... FROM agreements WHERE id = 3 AND service_type = 'vpn';

Is there a way to do this without resorting to making multiple queries?

P.S.

Some of the columns comes from another table. I just want to select the specific columns from specific tables based on the value of a column (in this case, the service_type)

Anna Fortuna · Accepted Answer · 2013-06-15 01:34:54Z

1

Well, assuming that in the further course of the application you tread the result columns identically, you could try to use

SELECT * FROM
(
    select service, ipreq, latency, ... FROM agreements WHERE id = 3
    UNION ALL
    select service, rates, markup, ... FROM agreements WHERE id = 3
) tmp
WHERE service = 'internet'

This would select a combination of all possible entries for id = 3 and then select the one you need by service.

However, this only works of all the fields have identical types.

Probably you should also rename the fields to unify access to them, like

SELECT * FROM
(
    select service, ipreq as field1, latency as field2, ... FROM agreements WHERE id = 3
    UNION ALL
    select service, rates as field1, markup as field2, ... FROM agreements WHERE id = 3
) tmp
WHERE service = 'internet'

If you don't want to do that, you need to access them by column index in your application. Still the field types need to be the same.

EDIT:

Here is the updated code:

SELECT * FROM
(
     SELECT doc.id as docid, service, ipreq, latency,... FROM agreements WHERE service = 1
     UNION ALL
     SELECT doc.id as docid, service, rates, markup,... FROM agreements WHERE service = 2
) as tmp
WHERE docid = 1

Now this code will select all fields pertaining to the specific service type.

edited Jun 15, 2013 at 1:34

Anna Fortuna

1,0712 gold badges17 silver badges35 bronze badges

answered Jun 12, 2013 at 14:52

Thorsten Dittmar

56.9k10 gold badges95 silver badges145 bronze badges

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

4 Comments

Anna Fortuna Over a year ago

This will mean that I will still have to get the value of service beforehand, right?

Thorsten Dittmar Over a year ago

Well, that depends. Using the above approach you could also get all possible entries for id = 3 by removing the outer SELECT. You could then filter within your application. In your sample code, however, you selected the service type already, adjusting the query according to it, so I thought that was given.

Anna Fortuna Over a year ago

Thanks for clarifying Thorsten. I am currently trying this and see if it will work the way I want it to be.

Anna Fortuna Over a year ago

This works for me, though I ended up altering it a bit. Thanks!

Slickrick12 · Accepted Answer · 2013-06-12 14:50:52Z

0

Mysql supports the Control Flow Syntax which allows you to build your IF/ELSE right inside the query. You can even use Subqueries inside of the If/Else statements, to create very dynamic select statements.

answered Jun 12, 2013 at 14:50

Slickrick12

9371 gold badge7 silver badges21 bronze badges

Collectives™ on Stack Overflow

SELECT multiple columns based on value of one column

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related