PDA

View Full Version : SQL - Joining more than 2 tables



nitric
06-20-2002, 12:19 PM
Hi all

Is it possible to join more than 2 tables using SQL? I've used the COLOR=blue]INNER JOIN[/COLOR] statement to join 2 tables based on a common field, but can't get it to work with 3 tables.

Thanks!!
:confused: :confused: :confused:

ReyN
06-20-2002, 02:52 PM
Hi

The Sql UNION operation creates a union query, which combines the results of two or more independent queries or tables.

[TABLE] query1 UNION [ALL] [TABLE] query2 [UNION [ALL] [TABLE] queryn [ ... ]]

where query1~n is a SELECT statement, the name of a stored query, or the name of a stored table preceded by the TABLE keyword.

You can merge the results of two or more queries, tables, and SELECT statements, in any combination, using a single UNION statement. The following example merges an existing table named New Accounts and a SELECT statement:

TABLE [New Accounts] UNION ALL
SELECT * FROM Customers
WHERE OrderAmount > 1000;

By default, no duplicate records are returned when you use UNION; however, you can include the ALL predicate to ensure that all records are returned. This also makes the query run faster.

All queries in a UNION operation must request the same number of fields; however, the fields do not have to be of the same size or data type. :D

allida77
06-20-2002, 02:57 PM
http://www.w3schools.com/sql/sql_join.asp

ReyN
06-20-2002, 03:15 PM
I didn't see any example of joining more than 2 tables in that w3schools link.

allida77
06-20-2002, 03:42 PM
Sorry... Can you post your entire SQL statement.

ritap
06-25-2002, 07:06 AM
If you have 3 tables with the following variables
table1 : ssn, name
table2: ssn, fav_color
table3: ssn, fav_food


SELECT table1.name, table2.fav_color, table3.fav_food
FROM table1, table2, table3
WHERE table1.ssn = table2.ssn
and table2.ssn = table3.ssn

If you wanted to retrieve people(rows) that did not have entries in all 3 tables then you must do outer joins. The syntax is different depending on the database you are using but for Oracle it would be:
SELECT table1.name, table2.fav_color, table3.fav_food
FROM table1, table2, table3
WHERE table1.ssn = table2.ssn(+)
and table2.ssn = table3.ssn(+)
and table3.ssn = tabl1.snn(+)

I hope this helps (and I hope I did not make any typos).
Rita

BennoM1984
06-05-2011, 11:01 PM
@allida77 the link is brilliant

thank you !!

Vadim Slutsky
02-29-2012, 04:27 AM
SELECT
table1.colum1,table2.colum1,table3.column1
FROM table1
INNER JOIN table2 ON (table1.column1=table2.column1)
JOIN table3 ON (table2.column1=table3.column1)
GROUP BY table1.column2;