Sql Query To read from 4 tables

i have 3 tables table A, table B and a relational table C

table A has ta_id as primary key along with other columns table B has tb_id as primary key along with other columns table C has ta_id, tb_id as foreign keys along with other columns.

i wanted to find all the rows of table B which have a common ta_id in table C. my sql query for the that is. SELECT B.ta_id, B.type, B.language, B.user_id FROM B INNER JOIN C ON B.tb_id=C.tb_id where开发者_如何转开发 C.ta_id = 1 ORDER BY B.user_id

the above query seems to be working..

but now i have another table called table D with D.tb_id as a foreign key (which is primary key in table B ). each row of table B has 0 or more rows associated in table D or we can say 1 or more rows in table D has exactly one corresponding row in table B.

Now i want to list my each row of table B with all the associated rows of table D. so it should look like this.

first row of table B first corresponding row of table D second corresponding row of table D ... ..

second row of table B first corresponding row of table D second corresponding row of table D ... ..

so in a way i am mixing the contents of 2 tables in display Please tell me how to achieve this using a sql query..?

Waiting for reply..!

Thanks Big O

Just add another inner join like this:

SELECT B.ta_id, B.type, B.language, B.user_id
FROM B INNER JOIN C ON B.tb_id=C.tb_id INNER Join D ON B.tb_id=D.tb_id
WHERE C.ta_id = 1
ORDER BY B.user_id

I believe that you can use SQL views easily to query data with lot of tables

You cannot do this in one simpl query, you need a loop. Think about what you are trying to do...

TABLE B ROW 1
TABLE D ROW 1 (Matching Row 1 Table B)
TABLE D ROW 2 (Matching Row 1 Table B)
TABLE D ROW 3 (Matching Row 1 Table B)
TABLE B ROW 2
TABLE D ROW 1 (Matching Row 2 Table B)
TABLE D ROW 2 (Matching Row 2 Table B)
TABLE D ROW 3 (Matching Row 2 Table B)
ETC...
ETC...

The only way you can do this is inside a stored procedure using temp tables and looping.