• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 225
  • Last Modified:

combining items in a query

I have this query:

SELECT p.PK_PersonsID
     , p.FirstName
     , p.LastName
     , p.City
     , r.PK_RegistrationID
     , r.PersonsID_FK as RID
     , r.ClassID_FK
     , c.PK_ClassID
     , c.ClassDate1
     , c.CourseID_FK
     , co.PK_CourseID
     , co.CourseTitle
     , m.PersonsID_FK as MID
     , m.Designation
  FROM tbl_persons p INNER JOIN tbl_registrations r
    ON p.PK_PersonsID = r.PersonsID_FK INNER JOIN tbl_classes c
    ON r.ClassID_FK = c.PK_ClassID INNER JOIN tbl_courses co
    ON co.PK_CourseID = c.CourseID_FK LEFT OUTER JOIN tbl_members m
    ON m.PersonsID_FK = p.PK_PersonsID
 WHERE c.PK_ClassID = <cfqueryparam value="#Val(url.id)#" cfsqltype="CF_SQL_NUMERIC">
 ORDER BY LastName

Is there a way to combine three of the SELECT items into a single result? That is, for each record returned I want to combine p.FirstName, p.LastName, and p.Designation into a single query item. The reason is I am printing name tags using CF Report Builder. On a regular page I would just display the three items like this: #FirstName# #LastName#, #Designation# . But in Report Builder each query item has to have a fixed width (as far as I can tell) so there is space between the items on the name tags depending on how long each one is.
0
brucecrabtree
Asked:
brucecrabtree
1 Solution
 
mkishlineCommented:
You should be able to do CONCAT(p.FirstName," ",p.LastName," ",p.Designation) AS heading in the SELECT statement, assuming you're using MySQL.
0
 
TeggertCommented:
With SQL Server It is the following

Below is the entire query, but the line of note is;      , p.FirstName + ' ' + p.LastName + ', ' + m.Designation as NameAndDesignation


SELECT p.PK_PersonsID
     , p.FirstName + ' ' + p.LastName + ', ' + m.Designation as NameAndDesignation
     , p.City
     , r.PK_RegistrationID
     , r.PersonsID_FK as RID
     , r.ClassID_FK
     , c.PK_ClassID
     , c.ClassDate1
     , c.CourseID_FK
     , co.PK_CourseID
     , co.CourseTitle
     , m.PersonsID_FK as MID
  FROM tbl_persons p INNER JOIN tbl_registrations r
    ON p.PK_PersonsID = r.PersonsID_FK INNER JOIN tbl_classes c
    ON r.ClassID_FK = c.PK_ClassID INNER JOIN tbl_courses co
    ON co.PK_CourseID = c.CourseID_FK LEFT OUTER JOIN tbl_members m
    ON m.PersonsID_FK = p.PK_PersonsID
 WHERE c.PK_ClassID = <cfqueryparam value="#Val(url.id)#" cfsqltype="CF_SQL_NUMERIC">
 ORDER BY LastName

Then just call #NameAndDesignation#
0
 
brucecrabtreeAuthor Commented:
I am using MySQL. I tried using this:

        , concat(p.FirstName, " ", p.LastName, " ", m.Designation) as heading

in the SELECT statement but it is not catching all records in the "heading" item. That is, in a query that returns 8 records total, only 2 have anything in the "heading" column in the query results. Maybe it is because the table that contains the m.Designation field is in a table that is being called by an outer join? Of the 8 records returned, only one has a value in the Designation field (but there are no null values in the table). Yet 2 heading results are returned by the concat select statement.
0
 
mrichmonCommented:
You need to use single quotes.

Also anytime you have a null value you will get no results for the whole string.

So you need

concat(COALESCE(p.FirstName, ' '),' ',COALESCE(p.LastName, ' '), ' ',COALESCE(m.Designation, ' ')) as heading

The COALESCE takes the first value if it is not null and the second one otherwise...
0
 
brucecrabtreeAuthor Commented:
Thanks. Using coalesce appears to work. There are no null values in the tables, though, so I don't understand why it works versus just concat.

I have posted a related question about this same query here: http://www.experts-exchange.com/Web/WebDevSoftware/ColdFusion/Q_21832741.html
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now