CODEWITHSUNDEEP
sql
Aleksei Yerokhin
Aleksei Yerokhin

Reputation: 879

LIKE in INNER JOIN doesn't work

While practicing in SQL here http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_join i made up a task for myself - need to compose a table using INNER JOIN that will contain CustomerID, EmployeeID, ContactName of a customer, and employee's last and first names WHERE Employee's first name is contained in Customer's contact name. The following doesn't give an output:

SELECT DISTINCT Customers.CustomerID, Customers.ContactName AS CustomerContactName, Employees.EmployeeID, Employees.FirstName AS EmployeeFirstName, Employees.LastName AS EmployeeLastName
FROM Customers
JOIN Employees
ON Customers.ContactName LIKE "%"+Employees.FirstName+"%"

Though this one:

SELECT DISTINCT Customers.CustomerID, Customers.ContactName AS CustomerContactName, Employees.EmployeeID, Employees.FirstName AS EmployeeFirstName, Employees.LastName AS EmployeeLastName
FROM Customers
JOIN Employees
ON Customers.ContactName LIKE "%Janet%" AND Employees.FirstName LIKE "%Janet%"

gives correct output for just one case. Do i miss something or it is w3schools' issue?

Upvotes: 0

Views: 87

Answers (3)

GarethD
GarethD

Reputation: 69789

The first thing to note is that the two queries are not comparable, If first name was Janette, and contact name was Janet Jackson, then the both values statisfy LIKE '%Janet%', but Janet Jackson does not contain Janette, so the join condition is not met.

Secondly, I think you should be using single quotes for literals, not double, e.g. '%' + Employees.FirstName + '%'.

I am not sure what engine w3schools is running, but with the above change, and changing JOIN to INNER JOIN which are equivalent I got results:

SELECT  DISTINCT 
        Customers.CustomerID, 
        Customers.ContactName AS CustomerContactName, 
        Employees.EmployeeID, 
        Employees.FirstName AS EmployeeFirstName, 
        Employees.LastName AS EmployeeLastName
FROM    Customers
        INNER JOIN Employees
            ON Customers.ContactName LIKE '%'+Employees.FirstName+'%';

enter image description here

Upvotes: 1

corky_bantam
corky_bantam

Reputation: 339

This works

SELECT DISTINCT Customers.CustomerID, Customers.ContactName AS  CustomerContactName, Employees.EmployeeID, Employees.FirstName AS EmployeeFirstName, Employees.LastName AS EmployeeLastName
FROM Customers
INNER JOIN Employees
ON Customers.ContactName LIKE "%"+Employees.FirstName+"%"

Results in:

Number of Records: 3
CustomerID  | CustomerContactName   | EmployeeID    | EmployeeFirstName | EmployeeLastName
41          | Annette Roulet        | 9             | Anne              | Dodsworth 
67          | Janete Limeira        | 3             | Janet             | Leverling 
68          | Michael Holz          | 6             | Michael           | Suyama

Looks like you've got to explicitly tell it that it is an inner join. though this should not be necessary. thats w3schools for you!

Upvotes: 1

rory.ap
rory.ap

Reputation: 35318

Without seeing your actual data, I can't really be sure what's actually happening in your case, but there is a flaw in your first query:

Customers.ContactName LIKE "%"+Employees.FirstName+"%"

This will only return records where the Customers.ContactName contains the entire Employees.FirstName.

For example, if you have an Employees.firstName = "Jonathan", then it will only return records where there is a Customers.ContactName that contains the entire value "Jonathan", not just some part of it.

Upvotes: 0

Related Questions