Muliti Table Join

[dyluck]

Hi there,

 

I am creating a query that involves 3 tables total.

How would you do this query? 

 

Table 1

TID

MNAME

1

JOHN

2

JIM

 

Table 2

MNAME

FULLNAME

JOHN

JOHN HENRY

JIM

JIM JONES

 

Table 3

TID

VIEWCOUNT

1

1

1

1

2

1

 

 

I am doing 1 query that will hopfully look something like this:

SELECT * FROM TABLE 1  JOIN THE REST TO LOOK LIKE THIS:

 

JOHN HENRY    2

JIM JONES      1

 

Thanks for all your help!  The beast of a query I attempted crashed mysql server

[F1Fan]

It depends exactly what you want. If you want results regardless of whether they are in all the tables, that will be a join. Otherwise, you can do a simple query like this:

 

SELECT * FROM table1 a, table2 b, table3 c WHERE a.mname = b.mname AND a.tid = c.tid
**OR**
SELECT a.*, b.fullname, c.viewcount FROM table1 a, table2 b, table3 c WHERE a.mname = b.mname AND a.tid = c.tid

[Ken2k7]

F1Fan: those SQLs don't produce the desired output.

 

Not tested, but it should work.

SELECT t2.fullname, SUM(t3.viewcount) cnt

FROM table1 t1

INNER JOIN table2 t2

    ON (t1.mname = t2.mname)

INNER JOIN table3 t3

    ON (t1.tid = t3.tid)

GROUP BY t3.tid;