SQL problem

Does exactly what it says on the tin. Some of the nonsense contained herein may be very loosely related to The Sisters of Mercy, but I wouldn't bet your PayPal account on it. In keeping with the internet's general theme nothing written here should be taken as Gospel: over three quarters of it is utter gibberish, and most of the forum's denizens haven't spoken to another human being face-to-face for decades. Don't worry your pretty little heads about it. Above all else, remember this: You don't have to stay forever. I will understand.
Post Reply
User avatar
the-happening
Utterly Bastard Groovy Amphetamine Filth
Posts: 596
Joined: 13 Sep 2003, 20:21
Location: Farnborough, Hampshire

Not knowing a gret deal about SQL could someone help with the following question:

I need to do a join on two tables what would be the SQL syntax for that?

It is for a friend so if the above statement makes no sense i apologise.

Thanks in advance.
"i'm talking about god, devil, hell, do you understand, finally?"
User avatar
Quiff Boy
Herr Administrator
Posts: 16793
Joined: 25 Jan 2002, 00:00
Location: Lurking and fixing
Contact:

inner, left or right join? :D :lol:

this is a fairly good description of what they each do:

http://www.w3schools.com/sql/sql_join.asp

Code: Select all

Using Joins
OR we can select data from two tables with the JOIN keyword, like this:

Example INNER JOIN
Syntax

SELECT field1, field2, field3
FROM first_table
INNER JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield 

Who has ordered a product, and what did they order?

SELECT Employees.Name, Orders.Product
FROM Employees
INNER JOIN Orders
ON Employees.Employee_ID=Orders.Employee_ID 

The INNER JOIN returns all rows from both tables where there is a match. If there are rows in Employees that do not have matches in Orders, those rows will not be listed.

Result

Name Product 
Hansen, Ola Printer 
Svendson, Stephen Table 
Svendson, Stephen Chair 

Example LEFT JOIN
Syntax

SELECT field1, field2, field3
FROM first_table
LEFT JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield 

List all employees, and their orders - if any.

SELECT Employees.Name, Orders.Product
FROM Employees
LEFT JOIN Orders
ON Employees.Employee_ID=Orders.Employee_ID 

The LEFT JOIN returns all the rows from the first table (Employees), even if there are no matches in the second table (Orders). If there are rows in Employees that do not have matches in Orders, those rows also will be listed.

Result

Name Product 
Hansen, Ola Printer 
Svendson, Tove   
Svendson, Stephen Table 
Svendson, Stephen Chair 
Pettersen, Kari   

Example RIGHT JOIN
Syntax

SELECT field1, field2, field3
FROM first_table
RIGHT JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield 

List all orders, and who has ordered - if any.

SELECT Employees.Name, Orders.Product
FROM Employees
RIGHT JOIN Orders
ON Employees.Employee_ID=Orders.Employee_ID 

The RIGHT JOIN returns all the rows from the second table (Orders), even if there are no matches in the first table (Employees). If there had been any rows in Orders that did not have matches in Employees, those rows also would have been listed.

Result

Name Product 
Hansen, Ola Printer 
Svendson, Stephen Table 
Svendson, Stephen Chair 

Example
Who ordered a printer?

SELECT Employees.Name
FROM Employees
INNER JOIN Orders
ON Employees.Employee_ID=Orders.Employee_ID
WHERE Orders.Product = 'Printer' 

Result

Name 
Hansen, Ola 
good luck :lol: :D
What’s the difference between a buffalo and a bison?
User avatar
Francis
Overbomber
Posts: 2622
Joined: 02 Jul 2004, 16:58
Location: Loose shoes...

I do this for a living and I haven't got a clue what that means. :eek:

Oracle developers do it on tables.
And you know that she's half crazy but that's why you want to be there.
Post Reply