Query which involves 4 tables, inner join?

Question

I am just developing a PHP aplication, and I need a query which involves 4 tables. tables are like:

news {news_pk, user, description, building_pk}
building {building_pk, area_pk, description}
area {area_pk,state_pk}
states {state_pk, name}

I would like to show results filtered by state name (or what I think is the same state_pk). I would like to show news from a certain state.

I am not using MySQL so often, but in this app I am doing several queries like this and I am not sure if the inner join is a good solution.

Which query could be the most effective to show only news from a certain state?

Yes, JOINS are good solutions for your problem (if that was a question here) — MSadura, Mar 25 '14 at 08:14
It depends on what you need as result and constraint on your query. — Tom R., Mar 25 '14 at 08:15
Thankyou for the answers. I forgot the final question. I am looking for the query which shows news from a certain state. — Cking, Mar 25 '14 at 08:28

score 0 · Answer 1 · edited May 23 '17 at 12:30

0

It very much depends on what you want to do, inner join might be a good solution if it is what you are looking for. You can seel the difference between joins in this answer: What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

Hope it helps! :)

edited May 23 '17 at 12:30

Community

1
1

answered Mar 25 '14 at 08:15

Daniel Ramos

137
1
8

Thanks, i will read that for sure. I have to improve in MySQL! – Cking Mar 25 '14 at 08:26

score 0 · Accepted Answer · answered Mar 25 '14 at 08:18

0

You should use following query for join for tables:

mysql_query("select a.news_pk,a.user.a.description,a.buiding_pk,b.building_pk,b.area_pk,b.description,c.area_pk,c.state_pk,d.state_pk,d.name from news as a,buiding as b,area as c,states as d where a.buiding_pk=b.building_pk and b.area_pk=c.area_pk and c.state_pk=d.state_pk");

answered Mar 25 '14 at 08:18

Chirag Shah

1,463
2
18
40

1

I will try if this works in a few minutes. Thankyou! – Cking Mar 25 '14 at 08:24
This is working fine. Now i just have to add several variables and conditions for showing only "state1" or "state2"... – Cking Mar 25 '14 at 08:46
Thankyou Chirag. This query is working perfectly for what I needed. I really apreciate your help!!! :-) – Cking Mar 25 '14 at 08:56

Query which involves 4 tables, inner join?

2 Answers2