SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict Banana Man's Avatar
    Join Date
    Dec 2005
    Posts
    389
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Complex MySQL queries

    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

    Thanks
    Ronan

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Banana Man View Post
    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.
    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

    Quote Originally Posted by Banana Man View Post
    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.
    a simple join will do it
    Code:
    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'
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict Banana Man's Avatar
    Join Date
    Dec 2005
    Posts
    389
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    SitePoint Addict Banana Man's Avatar
    Join Date
    Dec 2005
    Posts
    389
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    . 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. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    sorry, i can't help you as long as we're talking about fictitious tables

    show your real table design and your real query, and maybe it will make more sense
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •