SEO

Hi there

Here's the first line for a recent query. I'd like to CONCAT address details
into a string (the number and streetname are stored in one table, street type
in another (ie: "avenue", "drive", etc), direction in another but it doesn't
seem to work.

If I CONCAT the items from a single table, I get a partial string ("24
Sussex"):

SELECT
a.AddressID,a.StreetNumber,a.StreetName,s.Suffix,s d.Direction,ct.CityName,p.P
rovinceName,cnt.Country,CONCAT(a.StreetNumber,' ',a.StreetName) as
AssembledAddress


But if I try to CONCAT from different tables, my "AssembledAddress" is blank
instead of the hoped-for "24 Sussex Drive West":

SELECT
a.AddressID,a.StreetNumber,a.StreetName,s.Suffix,s d.Direction,ct.CityName,p.P
rovinceName,cnt.Country,CONCAT(a.StreetNumber,' ',a.StreetName,'
',s.Suffix,' ',sd.Direction) as AssembledAddress


How can I get my full string in a single variable when its made up from
different table sources?


In case it's important, here's the full query:

SELECT
a.AddressID,
a.StreetNumber,
a.StreetName,
s.Suffix,
sd.Direction,
ct.CityName,
p.ProvinceName,
cnt.Country,
CONCAT(a.StreetNumber,' ',a.StreetName,' ',s.Suffix,' ',sd.Direction) as
AssembledAddress,
j.JobID,
j.ProjectName,
j.JobType,
FROM_UNIXTIME(j.Timestamped) as OrderDate,
j.Timestamped,
j.TimestampedCancelled,
j.EstimatedArea,
j.MeasuringUnits,
j.MeasureDesignation,
j.TimestampedCleared,
j.JobStatus,
j.CurrentAdmin,
j.ActualCurrency,
CONCAT(u.FirstName,' ',u.LastName) AS UserName,
c.CompanyName,
c.ClientID,
jq.QuoteID,
jq.ProjectName as QuoteName,
jq.Status as QuoteStatus,
MATCH (a.BuildingName,a.StreetNumber,a.Postal,a.StreetNa me) AGAINST ('24
Sussex Drive West') as AddressScore
FROM Addresses a
LEFT JOIN StreetSuffixes s ON a.StreetSuffix=s.SuffixID
LEFT JOIN StreetDirections sd ON a.Direction=sd.StreetDirID
LEFT JOIN Cities ct ON a.City=ct.CityID
LEFT JOIN Provinces p ON a.StateProv=p.ProvinceID
LEFT JOIN Countries cnt ON a.Country=cnt.CountryID
JOIN Jobs j ON j.AddressID=a.AddressID
LEFT JOIN JobQuoteData jqd ON jqd.JobID=j.JobID
LEFT JOIN JobQuotes jq ON jqd.QuoteID=jq.QuoteID
JOIN Users u ON j.ContactID=u.UserID
JOIN Clients c ON u.ClientID=c.ClientID
WHERE MATCH (a.BuildingName,a.StreetNumber,a.Postal,a.StreetNa me) AGAINST
('24 Sussex Drive West')
AND 1=1 AND 1=1 AND 1=1 AND 1=1
AND (Timestamped >= 1041397200) AND (Timestamped <= 1194584400)
HAVING AddressScore > 3
ORDER BY AddressScore DESC,OrderDate DESC

Good Man wrote:
> Hi there
>
> Here's the first line for a recent query. I'd like to CONCAT address
> details into a string (the number and streetname are stored in one
> table, street type in another (ie: "avenue", "drive", etc), direction
> in another but it doesn't seem to work.
>
> If I CONCAT the items from a single table, I get a partial string ("24
> Sussex"):
>
> SELECT
> a.AddressID,a.StreetNumber,a.StreetName,s.Suffix,s d.Direction,ct.CityName,p.P
> rovinceName,cnt.Country,CONCAT(a.StreetNumber,' ',a.StreetName) as
> AssembledAddress
>
>
> But if I try to CONCAT from different tables, my "AssembledAddress"
> is blank instead of the hoped-for "24 Sussex Drive West":
>
> SELECT
> a.AddressID,a.StreetNumber,a.StreetName,s.Suffix,s d.Direction,ct.CityName,p.P
> rovinceName,cnt.Country,CONCAT(a.StreetNumber,' ',a.StreetName,'
> ',s.Suffix,' ',sd.Direction) as AssembledAddress
>
>
> How can I get my full string in a single variable when its made up
> from different table sources?
>
>
> In case it's important, here's the full query:
>
> SELECT
> a.AddressID,
> a.StreetNumber,
> a.StreetName,
> s.Suffix,
> sd.Direction,
> ct.CityName,
> p.ProvinceName,
> cnt.Country,
> CONCAT(a.StreetNumber,' ',a.StreetName,' ',s.Suffix,' ',sd.Direction)
> as AssembledAddress,
> j.JobID,
> j.ProjectName,
> j.JobType,
> FROM_UNIXTIME(j.Timestamped) as OrderDate,
> j.Timestamped,
> j.TimestampedCancelled,
> j.EstimatedArea,
> j.MeasuringUnits,
> j.MeasureDesignation,
> j.TimestampedCleared,
> j.JobStatus,
> j.CurrentAdmin,
> j.ActualCurrency,
> CONCAT(u.FirstName,' ',u.LastName) AS UserName,
> c.CompanyName,
> c.ClientID,
> jq.QuoteID,
> jq.ProjectName as QuoteName,
> jq.Status as QuoteStatus,
> MATCH (a.BuildingName,a.StreetNumber,a.Postal,a.StreetNa me) AGAINST
> ('24 Sussex Drive West') as AddressScore
> FROM Addresses a
> LEFT JOIN StreetSuffixes s ON a.StreetSuffix=s.SuffixID
> LEFT JOIN StreetDirections sd ON a.Direction=sd.StreetDirID
> LEFT JOIN Cities ct ON a.City=ct.CityID
> LEFT JOIN Provinces p ON a.StateProv=p.ProvinceID
> LEFT JOIN Countries cnt ON a.Country=cnt.CountryID
> JOIN Jobs j ON j.AddressID=a.AddressID
> LEFT JOIN JobQuoteData jqd ON jqd.JobID=j.JobID
> LEFT JOIN JobQuotes jq ON jqd.QuoteID=jq.QuoteID
> JOIN Users u ON j.ContactID=u.UserID
> JOIN Clients c ON u.ClientID=c.ClientID
> WHERE MATCH (a.BuildingName,a.StreetNumber,a.Postal,a.StreetNa me)
> AGAINST ('24 Sussex Drive West')
> AND 1=1 AND 1=1 AND 1=1 AND 1=1
> AND (Timestamped >= 1041397200) AND (Timestamped <= 1194584400)
> HAVING AddressScore > 3
> ORDER BY AddressScore DESC,OrderDate DESC

Are you sure that all the elements in the CONCAT functions are not NULL?
Try surrounding them all by IFNULL(element,'')

"Paul Lautman" <paul.lautman@btinternet.com> wrote in
news:5phb04Fra3epU1@mid.individual.net:

> Good Man wrote:
>> Hi there
>>
>> Here's the first line for a recent query. I'd like to CONCAT address
>> details into a string (the number and streetname are stored in one
>> table, street type in another (ie: "avenue", "drive", etc), direction
>> in another but it doesn't seem to work.
>>
>> If I CONCAT the items from a single table, I get a partial string
>> ("24 Sussex"):

>> But if I try to CONCAT from different tables, my "AssembledAddress"
>> is blank instead of the hoped-for "24 Sussex Drive West":
>>
>> SELECT
>> a.AddressID,a.StreetNumber,a.StreetName,s.Suffix,s d.Direction,ct.CityN
>> ame,p.P rovinceName,cnt.Country,CONCAT(a.StreetNumber,'
>> ',a.StreetName,' ',s.Suffix,' ',sd.Direction) as AssembledAddress
>>
>> How can I get my full string in a single variable when its made up
>> from different table sources?

> Are you sure that all the elements in the CONCAT functions are not
> NULL? Try surrounding them all by IFNULL(element,'')

Bingo! Many thanks.