Select data from three tables?

Ho开发者_Python百科w can I write a SQL statement to select data from three tables?

Use a join

SELECT *
FROM table_1
JOIN table_2 ON (table_2.table_1_id = table_1.table_1_id)
JOIN table_3 ON (table_3.table_1_id = table_1.table_1_id)

This will require that each table has a table_1_id key and that there is an entry in each table.

You can use a LEFT JOIN if table_2 or table_3 may not have data but you still want to show the data from table_1

Here's a very general example:

SELECT KEYVAL, A.OTHER_COL, B.ANOTHER_COL, C.YET_ANOTHER_COL
  FROM TABLE_A A
  INNER JOIN TABLE_B B
    USING (KEYVAL)
  INNER JOIN TABLE_C C
    USING (KEYVAL)

If your database doesn't support USING you could say:

SELECT A.KEYVAL, A.OTHER_COL, B.ANOTHER_COL, C.YET_ANOTHER_COL
  FROM TABLE_A A
  INNER JOIN TABLE_B B
    ON (B.KEYVAL = A.KEYVAL)
  INNER JOIN TABLE_C C
    ON (C.KEYVAL = A.KEYVAL)

Or you could say

SELECT A.KEYVAL, A.OTHER_COL, B.ANOTHER_COL, C.YET_ANOTHER_COL
  FROM TABLE_A,
       TABLE_B,
       TABLE_C
  WHERE B.KEYVAL = A.KEYVAL AND
        C.KEYVAL = A.KEYVAL

but I think the latter is less clear.

Share and enjoy.

Look at the Join(SQL) clause.

You can join your first table with your second. Then you third table to your first or second table.

Ex:

SELECT * 
FROM TABLE_A
JOIN TABLE_B ON TABLE_A.ID = TABLE_B.ID
JOIN TABLE_C ON TABLE_C.ID = TABLE_B.ID

If your tables have the same schema and you want to essentially concatenate the rows from each table then you should think about a UNION query.

SELECT
Field1, Field2, Field3
FROM Table1
UNION
SELECT
Field1, Field2, Field3
FROM Table2
UNION
SELECT
Field1, Field2, Field3
FROM Table3

If you want to include related data from each table in a single row of your result set then you should look at using INNER JOINs and / or OUTER JOINs.

SELECT Table1.Field1, Table2.Field2, Table3.Field3
FROM Table1
INNER JOIN Table2 ON Table1.Field1=Table2.Field1
LEFT OUTER JOIN Table3 on Table1.Field3=Table3.Field3

We'll need more information from you, though, in order to give you a definitive answer including which RDBMS you're using.

Using join you can select data from three tables

SELECT column_name(s)
FROM table1
JOIN 
  table2 ON table1.column_name = table2.column_name
JOIN 
  table3 ON table3.column_name = table2.column_name;

select Data From Three Table like Joins on Country State and city

select country.country,country.countryid,countrystate.state, countrystatecity.city from country inner join countrystate on(country.countryid=countrystate.countryid) inner join countrystatecity on(countrystate.stateid=countrystatecity.stateid)