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 JOIN
s and / or OUTER JOIN
s.
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)
精彩评论