Join three tables / SQL

I know joining three tables already exists as topics here, but I don't know how to implement those in my example... here is what I have:

$id = _GET[ 'id' ]


table1:

mid | name
11 . . . johan
22 . . . zoki


table2:

id | mid | time
1 . . 11 . 1234
1 . . 22.. ..1111
2... ..11.... 1112


table3:

id | num2 | surname
1 . 212 . pogancic
2 . 111 . pajser


What I need: to load all things from 3 tables. My try was:

SELECT *
FROM table2 JOIN
table1
ON table1.mid=table2.mid
WHERE id=$id


But in that way I can't get 'surname'...tried also with adding another JOIN:

SELECT *
FROM table2 JOIN
table1
ON table1.mid=table2.mid JOIN
table3
ON table3.id=table2.id
WHERE id=$id


But without positive results; any help?

EDIT: With the WHERE id=$id I can't get the results; it seems there's a problem, not in a JOIN

WHERE id=$id

2 Answers
2

Your query looks good. Maybe something is wrong with your data. The data you describe is:

create table table1 (
mid int,
name varchar(20)
);

insert into table1 (mid, name) values (11, 'johan');
insert into table1 (mid, name) values (22, 'zoki');

create table table2 (
id int,
mid int,
time int
);

insert into table2 (id, mid, time) values (1, 11, 1234);
insert into table2 (id, mid, time) values (1, 22, 1111);
insert into table2 (id, mid, time) values (2, 11, 1112);

create table table3 (
id int,
num2 int,
surname varchar(20)
);

insert into table3 (id, num2, surname) values (1, 212, 'pogancic');
insert into table3 (id, num2, surname) values (2, 111, 'pajser');


Therefore, the query should be:

select t1.*, t2.*, t3.*
from table2 t2
join table1 t1 on t2.mid = t1.mid
join table3 t3 on t3.id = t2.id


Result:

mid name id mid time id num2 surname
--- ----- -- --- ---- -- ---- --------
11 johan 1 11 1234 1 212 pogancic
11 johan 1 11 1234 1 212 pogancic
11 johan 2 11 1112 2 111 pajser
22 zoki 1 22 1111 1 212 pogancic


Is this what you want?

Ok i got it working. Thanks to @Scott Hunter for reminding me. Problem was:

WHERE id=$id -> WHERE table2.id=$id

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.