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

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'

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

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.