SQL JOIN first line with CONCAT_WS
I only want to reference the first result from my users table, but I am 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 looks like this
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'm trying to follow the SQL Server answer : how to join the first line , but it doesn't work, otherwise I did it wrong :)
I also tried: does not apply to MySQL, so does not 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
Trying to 3: . This feature is accepted, but for some reason is not limited
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)
Example vwt_users:
╔════╦═══════╦══════╦══════╦══════╗ ║ ID ║ FIRST ║ LAST ║ COMP ║ LIVE ║ ╠════╬═══════╬══════╣══════╣══════╣ ║ 1 ║ JOHN ║ DOE ║ 1 ║ 1 ║ ║ 1 ║ JOHN ║ DOE ║ 2 ║ 1 ║ ╚════╩═══════╩══════╩══════╩══════╝
The actual fields are User ID, Username, Employee ID, First Name, Last Name, Email Address, User Level, Company ID, Company Name, User Asset. Of these, only the data of the employee (ID, first and last name) and company (ID and first name) are changed
source to share
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
When you join a vwt_users that has the same user id multiple times, the results are the same, the correlated subquery gets you 1 row with a limit, but for each user id (duplicates here) from vwt_users.
one way is to use cross connect like your try 2 and remove the connection using 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
source to share
concat_ws()
not a SQL Server function. This is a MySQL function. You can probably do what you want:
ON vwt_usersa.userID = (SELECT TOP 1 employeeFirstName + ' ' + employeeLastName AS courseAdded
FROM vwt_users
WHERE userID = tw_tblcourse.courseAddedID)
This may not handle correctly NULLs
. If it matters, 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)
source to share