I have four tables in my DB - one, called kursplan, 2nd table is kursplan_kurse,3rd table is kursplan_kursraum 4th table is kursplan_trainer.
The 1st table Structure: id,club_id,Tag,Kurse_namen,from,to,Kursleiter,kursraum
here club_id means my user id in my user table
id | club_id | Tag |Kurse_namen | Kursleiter| kursraum
____________________________________________________________________________________________
1 | 35 | Monday |2 |2 | 4
2 | 35 | tue |3 |3 | 3
3 | 35 | wen |3 |3 | 3
4 | 12 | thu |1 |2 | 4
The 2nd table Structure:table name[kursplan_kurse]
id | club_id | Kurse_namen
______________________________________
1 | 35 | Kurse1
2 | 35 | Kurse2
3 | 35 | Kurse3
4 | 35 | Kurse4
The 3rd table Structure:table name[kursplan_kursraum]
id | club_id | kursraum
______________________________________
1 | 35 | kursraum1
2 | 35 | kursraum2
3 | 35 | kursraum3
4 | 35 | kursraum4
The 4thtable Structure:table name[kursplan_trainer]
id | club_id | Kursleiter
______________________________________
1 | 35 | Kursleiter1
2 | 35 | Kursleiter2
3 | 35 | Kursleiter3
4 | 35 | Kursleiter4
My select query is
$query = "SELECT * FROM `kursplan` WHERE `club_id` = '35' AND `Tag` = 'Monday' ";
I am geting the result in this format
Kurse_namen | kursraum |Kursleiter |
=====================================
2 | 4 |2 |
Now, what I want to achieve is to setup a JOIN query for get values from all 3 tables that are 2nd,3rd,4th tables
I need the result in this format I MEAN Instead of numbers i want names
Kurse_namen | kursraum | Kursleiter
______________________________________________
Kurse2 | kursraum4 | Kursleiter2
Please help me.
Although your question is not clear if you are having related to joins you can understand it through this sql.
select t2.Kurse_namen,t3.kursraum,t4.Kursleiter
from table2 t2,table3 t3,table4 t4,table1 t1
where t2.club_id=t3.club_id
and t3.club_id=t4.club_id
and t1.club_id=t2.club_id
and t2.club_id=35
and t1.tag='Monday'
$query = "SELECT `b`.`Kurse_namen`, `c`.`kursraum`, `d`.`Kursleiter`,
FROM `kursplan` as `a`
JOIN `kursplan_kurse` as `b` ON `b`.`id` = `a`.`Kurse_namen`
JOIN `kursplan_kursraum` as `c` ON `c`.`id` = `a`.`kursraum`
JOIN `kursplan_trainer` as `f` ON `f`.`id` = `a`.`Kursleiter`
WHERE `a`.`club_id` = '35' AND `a`.`Tag` = 'Monday'";
I think you have to do INNER JOINs
This may be could works: ($query =)
SELECT kursplan_kurse.Kurse_namen, kursplan_kursraum.kursraum,
kursplan_trainer.Kursleiter FROM `kursplan`
INNER JOIN kursplan
INNER JOIN kursplan_kursraum
INNER JOIN kursplan_trainer
USING(club_id) WHERE `club_id` = '35' AND `Tag` = 'Monday'
Use left join for ex.
SELECT `TABLE2 NAME`.`FIELd NAME`, `TABLE3 NAME`.`FIELD NAME` , `TABLE4 NAME`.`FIELD NAME`
FROM `TABLE NAME`
LEFT JOIN `TABLE2`
ON `TABLE2`.`FIELD(UNIQUE DATA EXAMPLE ID)` = `TABLE3`.`FIELD(UNIQUE DATA EXAMPLE ID)` `TABLE4 NAME`.`FIELD NAME(UNIQUE DATA EXAMPLE ID)`
The following query would output the data you need:
SELECT kk.Kurse_Namen, kr.kursraum, kt.kursleiter FROM kursplan k
LEFT OUTER JOIN kursplan_kurse kk (k.Kurse_namen = kk.id)
LEFT OUTER JOIN kursplan_kursraum kr (k.kursraum = kr.id)
LEFT OUTER JOIN kursplan_trainer kt (k.Kursleiter = kt.id)
WHERE k.club_id = 35
AND Tag = "Monday"