CODEWITHSUNDEEP
mysqljoin
no use for a name
no use for a name

Reputation: 658

MySQL JOIN working properly only when selecting every column with wildcard

This was probably asked a few times, but I couldn't find an answer. While doing JOIN on two tables, I encounter a problem. First table is named "Employees" and another "Orders". I'm trying to create a query, which will give me the output like:

order_id (from Orders) | first_name (from Employees) | last_name (from Employees)

The queries I use are: 

SELECT * FROM Orders
LEFT JOIN Employees e on Orders.employeeid = e.EmployeeID;

or full join:

SELECT * FROM Orders
LEFT JOIN Employees e on Orders.employeeid = e.EmployeeID
UNION
SELECT * FROM Employees
RIGHT JOIN Orders o on Employees.EmployeeID = o.employeeid;

both work just fine, giving me the same results. Unless I select which columns I wish to extract. So query like that:

SELECT Orders.orderid, e.first_name, e.last_name FROM orders
LEFT JOIN Employees e on orders.employeeid = e.EmployeeID;

Gives me totally different results. Eg. first 100 orderids have same employee name, then another 100 different one and so on (only 4 employees overall, should be 9). What am I doing wrong?

EDIT (screenshots added):

Orders table:

enter image description here

Employees table:

enter image description here

Output when doing full join, everything seems to be ok:

enter image description here

Left join (or any other join, looks the same). Some orders seem to be ommited, but overall only 4 employees are listed.

enter image description here

Upvotes: 2

Views: 58

Answers (3)

saibbyweb
saibbyweb

Reputation: 3264

If

SELECT * FROM Orders
LEFT JOIN Employees e on Orders.employeeid = e.EmployeeID

is returning the correct set of results, then this should probably work:

Select orderid, FirstName, LastName from (SELECT * FROM Orders
LEFT JOIN Employees e on Orders.employeeid = e.EmployeeID LIMIT 999999) as joinedTable

Upvotes: 0

tmaj
tmaj

Reputation: 35135

I don't know your data, but I think you want:

 SELECT o.orderid, e.first_name, e.last_name FROM orders o INNER JOIN Employees e ON o.employeeid = e.employeeId
 where o.employeeid != null
 Order by o.orderid

Upvotes: 0

memo
memo

Reputation: 1938

It's hard to tell without seeing some data samples. But here are a few points:

With LEFT JOIN you get 'only 4 employees overall, should be 9' -- I assume there are 5 employees that do not have any order.

In the 'full join' you get mixed columns, the correct way should be

SELECT * FROM Orders
LEFT JOIN Employees e on Orders.employeeid = e.EmployeeID
UNION
SELECT * FROM Orders
RIGHT JOIN Employees e on Orders.employeeid = e.EmployeeID

Otherwise there should be no difference in the output if you specify the columns, as opposed to *.

Upvotes: 0

Related Questions