SQL JOIN first row with CONCAT_WS

Question

I'm wanting to only reference the first result back from my users table, but I'm getting the following error

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1 CONCAT_WS(' ',employeeFirstName,employeeLastName) AS courseAdded FROM vwt_user' at line 6

My SQL is as follows

CREATE OR REPLACE VIEW vwt_courses AS
SELECT tw_tblcourse.*, tw_tblprovider.providerName, CONCAT_WS(' ',vwt_userse.employeeFirstName,vwt_userse.employeeLastName) AS courseEditor, tw_tblprovider.providerAdd1, tw_tblprovider.providerAdd2, u_tbltowns.townName AS providerTown, tw_tblprovider.providerPostCode
FROM tw_tblcourse
JOIN tw_tblprovider ON tw_tblprovider.providerID = tw_tblcourse.courseProviderID
LEFT JOIN u_tbltowns ON u_tbltowns.townID = tw_tblprovider.providerTownID
JOIN vwt_users AS vwt_usersa ON vwt_usersa.userID = (SELECT TOP 1 CONCAT_WS(' ',employeeFirstName,employeeLastName) AS courseAdded FROM vwt_users WHERE userID=tw_tblcourse.courseAddedID)
LEFT JOIN vwt_users AS vwt_userse ON vwt_userse.userID = tw_tblcourse.courseEditedID

I've been trying to follow the answer at SQL Server: How to Join to first row but it doesn't work, or else I've done it wrong :)

I've also tried: doesn't apply to MySQL so doesn't work

CREATE OR REPLACE VIEW vwt_courses AS
SELECT tw_tblcourse.*, tw_tblprovider.providerName, CONCAT_WS(' ',vwt_usersa.employeeFirstName,vwt_usersa.employeeLastName) AS courseAdded, CONCAT_WS(' ',vwt_userse.employeeFirstName,vwt_userse.employeeLastName) AS courseEditor, tw_tblprovider.providerAdd1, tw_tblprovider.providerAdd2, u_tbltowns.townName AS providerTown, tw_tblprovider.providerPostCode
FROM tw_tblcourse
JOIN tw_tblprovider ON tw_tblprovider.providerID = tw_tblcourse.courseProviderID
LEFT JOIN u_tbltowns ON u_tbltowns.townID = tw_tblprovider.providerTownID
CROSS APPLY (SELECT  TOP 1 vwt_users.employeeFirstName, vwt_users.employeeLastName FROM vwt_users WHERE vwt_users.userID = tw_tblcourse.courseAddedID) vwt_usersa
LEFT JOIN vwt_users AS vwt_userse ON vwt_userse.userID = tw_tblcourse.courseEditedID

Attempt 3: This one is accepted but isn't limiting for some reason

CREATE OR REPLACE VIEW vwt_courses AS
SELECT tw_tblcourse.*, tw_tblprovider.providerName, CONCAT_WS(' ',vwt_usersa.employeeFirstName,vwt_usersa.employeeLastName) AS courseAdded, CONCAT_WS(' ',vwt_userse.employeeFirstName,vwt_userse.employeeLastName) AS courseEditor, tw_tblprovider.providerAdd1, tw_tblprovider.providerAdd2, u_tbltowns.townName AS providerTown, tw_tblprovider.providerPostCode
FROM tw_tblcourse
JOIN tw_tblprovider ON tw_tblprovider.providerID = tw_tblcourse.courseProviderID
LEFT JOIN u_tbltowns ON u_tbltowns.townID = tw_tblprovider.providerTownID
JOIN vwt_users AS vwt_usersa ON vwt_usersa.userID = (SELECT userID FROM vwt_users WHERE userID = tw_tblcourse.courseAddedID LIMIT 1)
LEFT JOIN vwt_users AS vwt_userse ON vwt_userse.userID = (SELECT userID FROM vwt_users WHERE userID = tw_tblcourse.courseEditedID LIMIT 1)

vwt_users example:

╔════╦═══════╦══════╦══════╦══════╗
║ ID ║ FIRST ║ LAST ║ COMP ║ LIVE ║
╠════╬═══════╬══════╣══════╣══════╣
║  1 ║ JOHN  ║  DOE ║  1   ║  1   ║
║  1 ║ JOHN  ║  DOE ║  2   ║  1   ║
╚════╩═══════╩══════╩══════╩══════╝

Actual fields are user ID, Username, Employee ID, First Name, Last Name, Email, User Level, Company Id, Company Name, User Active. Of these, only the Employee (ID, First and Last Name) and Company (ID and Name) details change

score 1 · Answer 1 · answered Nov 09 '14 at 23:53

concat_ws() is not a SQL Server function. It is a MySQL function. You can probably do what you want with:

ON vwt_usersa.userID = (SELECT TOP 1 employeeFirstName + ' ' + employeeLastName AS courseAdded
                        FROM vwt_users
                        WHERE userID = tw_tblcourse.courseAddedID)

This may not handle the NULLs correctly. If that is important, you need more logic:

ON vwt_usersa.userID = (SELECT TOP 1 (case when employeeFirstName is NULl then employeeLastName
                                           when employeeLastName is NULL then emplyeeFirstName
                                           else employeeFirstName + ' ' + employeeLastName
                                      end) AS courseAdded
                        FROM vwt_users
                        WHERE userID = tw_tblcourse.courseAddedID)

Sorry that was an incorrect tag. It's a MySQL server – dpDesignz Nov 09 '14 at 23:56 — dpDesignz, Nov 09 '14 at 23:56

radar · Accepted Answer · 2014-11-10T02:30:10.630

1

TOP is not available in MySQL, you need to use LIMIT

 SELECT  vwt_users.employeeFirstName, vwt_users.employeeLastName FROM vwt_users WHERE vwt_users.userID = tw_tblcourse.courseAddedID
 order by
 vwt_users.employeeFirstName,     vwt_users.employeeLastName
 Limit 1

As you are joining with vwt_users, which has same user id multiple times, the results will be same, the correlated subquery gets you 1 row with limit but that is for each user id ( here duplicates) from vwt_users.

one way is to use cross join similar to your attempt 2 and remove join with vwt_users

SELECT tw_tblcourse.*,
       tw_tblprovider.providerName, 
       CONCAT_WS(' ',vwt_usersa.employeeFirstName,vwt_usersa.employeeLastName) AS courseAdded,   
       CONCAT_WS(' ',vwt_userse.employeeFirstName,vwt_userse.employeeLastName) AS courseEditor 
       tw_tblprovider.providerAdd1, tw_tblprovider.providerAdd2, u_tbltowns.townName AS providerTown,  
       tw_tblprovider.providerPostCode
FROM tw_tblcourse
JOIN tw_tblprovider 
ON tw_tblprovider.providerID = tw_tblcourse.courseProviderID
LEFT JOIN u_tbltowns 
ON u_tbltowns.townID = tw_tblprovider.providerTownID
CROSS JOIN (SELECT vwt_users.employeeFirstName, vwt_users.employeeLastName FROM vwt_users WHERE userID = tw_tblcourse.courseAddedID LIMIT 1) vwt_usersa
CROSS JOIN (SELECT vwt_users.employeeFirstName, vwt_users.employeeLastName  FROM vwt_users WHERE userID = tw_tblcourse.courseEditedID LIMIT 1) vwt_userse

edited Nov 10 '14 at 02:30

answered Nov 10 '14 at 00:24

radar

13,270
2
25
33

@dpDesignz, can you add some data, so it is easy to validate – radar Nov 10 '14 at 00:41
I've added an example of what's in the `vwt_users` view. I'm assuming that's what you meant? :) There are 10 fields, but most of them are the same data. NOTE: `vwt_users` is a view as well – dpDesignz Nov 10 '14 at 00:48
1

Don't join with vwt_users , get names from subquery and use them , joini g with users makes it to return as many rows as that user Id exists, limit will be applied for each user Id , course I'd combo, since we join with users, the the same user Id's are matched mtiple times – radar Nov 10 '14 at 01:33
Hmm, how I've structured it is there is one user ID, and multiple employees can be assigned to that user ID. Maybe I should be assigning the addedID and editedID to the employeeID not the userID – dpDesignz Nov 10 '14 at 02:20
Your edit gave the following error `#1349 - View's SELECT contains a subquery in the FROM clause ` – dpDesignz Nov 10 '14 at 02:22
MYSQL view can't contain the select in the from clause. just now realized that you are creating a view out of it, can you convert it into view that gets top 1 user name for each course id and join with it. – radar Nov 10 '14 at 02:28
Thanks, I'll do that :) – dpDesignz Nov 10 '14 at 02:30

SQL JOIN first row with CONCAT_WS

2 Answers2