Web Development



sql joins

By Truenthusiast ·
Hi evreryone! I am working on a project in which I have to perform a join of three tables. Not all of the tables will contribute data in the results. Here is the code that I have so far...
SELECT Customers.CompanyName,Customers.ContactName,customers.phone
FROM orders
JOIN customers
ON Orders.orderid=Customers.CustomerID
JOIN Employees
ON Customers.CustomerID=Employees.EmployeeID

Am I on the right track with this syntax and do I need to include a 'WHERE' statement, because the column names show up, but nothing else.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Inner, Left/Right, Outer

by TobiF In reply to sql joins

By default, a join is treated as an "INNER JOIN".
This means that you will only get those orders, where both employeeid and customerid are entered and do have a match in the corresponding tables.

If you'd like to list, say, every order in the system, then read a bit about LEFT and RIGHT joins.

Oh, don't forget the wonderful keyword "ORDER BY" to sort your output.

the "WHERE" statement you'll use in real operation to just get orders from yesterday, orders in a particular status ('picked', 'invoiced') etc.

Collapse -

Well unless your naming conventions are really f'ing strange

by Tony Hopkinson In reply to sql joins

SELECT Customers.CompanyName,Customers.ContactName,customers.phone
FROM orders
JOIN customers
ON Orders.CUSTOMERID=Customers.CustomerID
JOIN Employees
ON Customers.EMPLOYEEID=Employees.EmployeeID

or some such is more likely to yield a result set....

Presumably the result you are after is only customers with orders and employees ?

Collapse -

Personally I always forget how joins work

by Slayer_ In reply to Well unless your naming c ...

I am pretty sure all joins can be duplicated using where clauses, which always made more sense to me.

But these days so many programs write queries for you, its just easier to use those tools, then modify the results.

Collapse -

Always have to look the syntax up myself

by Tony Hopkinson In reply to Personally I always forge ...

I grew up with where as well. You can do inner joins with where but not outers now
*= and =* are illegal (well in sql server anyway)

You can do outer joins with subqueries and unions, but that tends to confuse the crap out of the DBMS and you end up with something inefficient and slow, not to mention bloody hard to read and understand next month.

I'm trying to get in the habit of using the 'new' ( ) syntax all the time now.

Either method though, linking OrderID to CustomerID isn't usually going to give you anything useful.

Related Discussions

Related Forums