variable selection variables in query

[showman13]

Hi, I have a query that I would like to be able to decide what fields to select based on the value of the fields in the database...

Here is the query I have

SELECT mem_id, company, fname, lname, addr1, addr2, city, state, zip, country FROM dc_order WHERE STATUS='W'

What I really would like to do is to select 'company' only if it has a value in it, and if it does, then ignore fname and lname.

But if there is no value in 'company' then I would like to get the fname and lname...

This is basically a file that is created when someone orders a Debit card, and they have the option of having their name or their company name on the card, but not both. When the record is inserted into the dc_order table, they have made that determination, but I need to be able to get whichever they chose for the processor to see what goes on the card, when I extract this info into an excel spread sheet...

Hope I'm not confusing the issue with the explanation, but there you have it.

Any feedback would be greatly appreciated.

Thanks
Douglas

[smantscheff]

Use the IF or IFNULL function:

SELECT mem_id, IFNULL(company, CONCAT(fname, ' ', lname)) as theName, addr1, addr2, city, state, zip, country FROM dc_order WHERE STATUS='W'

or

SELECT mem_id, IF(length(company) > 0, company, CONCAT(fname, ' ', lname)) as theName, addr1, addr2, city, state, zip, country FROM dc_order WHERE STATUS='W'

[showman13]

smantscheff,

SELECT mem_id, IF(length(company) > 0, company, CONCAT(fname, ' ', lname)) as theName, addr1, addr2, city, state, zip, country FROM dc_order WHERE STATUS='W'

That one worked perfectly.

Thank you, that was a totally new one on me.

Douglas

[smantscheff]

So better have a look at the list of built-in functions in MySQL. And you can even write your own...