UNION Query Question

RRepsher · #1 (**permalink**) 10-27-03, 09:27

I have some list boxes that I use in a QBF form. I need the list to look like:

!All
John Smith
Michael McGee
Robert James
etc...

The query below only returns the first 4 characters of the user name (the length of "!ALL")

!All
John
Mich
Robe

Is this typical? I tried to find something about it in the manual...

Thanks,

Rick

SELECT DISTINCT "!All" AS Name, 0 AS UserID FROM tblUser;
UNION SELECT CONCAT(tblUser.FirstName, " ", tblUser.LastName) AS Name, tblUser.UserID
FROM tblUser
ORDER BY Name;

r937 · #2 (**permalink**) 10-27-03, 13:20

yes, it is most typical

the datatype specs for the result set are taken from the first query in the union

just reverse the order --

Code:

SELECT CONCAT_WS(' ',tblUser.FirstName, tblUser.LastName) AS Name
     , tblUser.UserID 
  FROM tblUser
UNION ALL
SELECT DISTINCT '!All'
     , 0 
  FROM tblUser
ORDER BY Name

note: use CONCAT_WS so that you don't get a leading space when the first name is null

use UNION ALL to avoid a complete sort of the result set looking for duplicate rows (because there can't be any)

rudy
http://r937.com/

RRepsher · #3 (**permalink**) 10-27-03, 13:21

Thanks for the reply... I didn't think about reversing the order.

Internet Services

Web Development

Digital Marketing

Consumer Tech