Complex MySQL queries

Databases
1

Hi,

I have been working with PHP and MySQL for some time now but have always only ever need to do fairly straight forward queries. Now however i have a larger relation database and i am finding the queries are getting a bit more complicated. I’m trying to figure out how best to deal with this. The best option is probably to sit down and learn how to write more complex MySQL queries, option 2 is to add more direct relational fields to my tables but then i am duplicating content, or the third option is to break down my MySQL queries into a few straight forward PHP queries.

In the example below i am trying to print the table_1_title but only when the table_3_country is set to ‘ireland’ for example. Table 1 is not directly connected to table 3 however. Instead Table 1 connects to Table 2 with a foreign key in Table 1. Table 2 is then connected to Table 3 by a foreign key and it is here that i check what country this user is located in.

Table 1

  • table_1_id
  • table_1_title
  • FK_table_2_id

Table 2

  • table_2_id
  • FK_table_3_region

Table 3

  • table_3_id
  • table_3_country

Can anyone tell me how a MySQL query would be constructed for something like this?

I figured out a similar issue with a different query yesterday using INNER JOINS but even then i had another more complex query where another table was also needed after the INNER JOIN and i was stuck again.

Getting more and more confused trying to even figure out in my head what i want to achieve the more i get into it :frowning:

Thanks
Ronan

2

for sure the first option is best… option 2 is horrible, and option 3 works real well occasionally but will often be lacking in performance

a simple join will do it


SELECT table1.table_1_id
     , table1.table_1_title
  FROM table1
INNER
  JOIN table2
    ON table2.table_2_id = table1.FK_table_2_id
INNER
  JOIN table3
    ON table3.table_3_id = table2.FK_table_3_region
   AND table3.table_3_country = 'ireland'
3

Thanks! That’s got it working now. I had seen two INNER JOINs being used in one query but didnt know how to set it up.

4

. When i try to order the results it doesnt seem to have any effect.

I am using:

ORDER BY
table1.table1_id ASC

but if i change it to DESC it outputs in the same order. Should i be using some different syntax to achieve this?

5

sorry, i can’t help you as long as we’re talking about fictitious tables :slight_smile:

show your real table design and your real query, and maybe it will make more sense