CODEWITHSUNDEEP
mysql
user3649739
user3649739

Reputation: 1869

Mysql Select with concat where some fields are null

I'm doing a join between two tables to push criteria values into a record description. The problem is some records do not have some/all criteria and the entire string fails:

Select Concat(Description,'<br><br>',C.CritieraNameA,': ',T.CriteriaValueA,'<br><br>',C.CriteriaNameB,': ',T.CriteriaValueB) From Records T Inner Join Company C On T.CompanyID=C.ID

so I end up with

Supermarket specializing in Dairy Products

Hours: 8am-5pm
Credit Cards: Yes

and

Gone with the Wind

Running Time: Too long
Format: DVD

This works fine until I hit a record where either

Is there a way to do this select so when it doesn' find a CriteriaValue in Records:

Supermarket specializing in Meats

Hours:
Credit Cards: Yes

or a CriteriaName in Company:

Porsche

Type: Sports Car

It does not simply return an empty result?

Upvotes: 1

Views: 48

Answers (3)

user3649739
user3649739

Reputation: 1869

Concat_WS to the rescue:

Concat_WS('',C.CriteriaTypeA,': ',T.CriteriaValue)

Upvotes: 1

Paul Spiegel
Paul Spiegel

Reputation: 31792

This should do it:

Select Concat(
    Description,
    Coalesce(Concat('<br><br>',C.CritieraNameA,': ',T.CriteriaValueA), ''),
    Coalesce(Concat('<br><br>',C.CriteriaNameB,': ',T.CriteriaValueB), '')
) 
From Records T
Inner Join Company C
On T.CompanyID=C.ID

But as I wrote in the comment: Don't implement output logic in SQL.

Upvotes: 0

NotNull
NotNull

Reputation: 11

IfNull could be useful in this case - it will detect a null and replace it with a value you supply.

IFNULL(CriteriaTypeA , "not given")

Upvotes: 1

Related Questions