SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Basic question - use of JOIN or..?

    Hi,

    Let's say i have a table with differents topics (records) on it.
    Every time i show a record in a PHP page I need to look for related news, links...up to 8 categories.
    Some topics do have many things related, but others don't.
    I put a foreign key in the related tables, pointing to the primary key of the topics table.
    In the past, I used to check every table looking for that foreign key, then displaying results, then checking the next table and so on.
    But now, I think there will be too many queries due to the number of related tables.
    Could I get that information with one single query?
    I tried to do so using JOIN, but when i add a third table the results are confusing. And maybe i'm wrong and it can't be done like that.
    Thanks for helping me.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    it would depend on the nature of the columns being selected from the 8 tables (you might be able to use UNION), or whether they are in a one-to-many relationship with your main table (you might be able to use LEFT JOINs)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    it would depend on the nature of the columns being selected from the 8 tables...
    Do you mean the field type?
    More in depth, what I got is:

    1.One table named Topics with columns as follows: id(small int,pk)|topicName(varchar).
    2. Eight tables, different each other:
    a)Links:id(small int,pk)|topicID(small int)|linkName (varchar)|url (varchar)
    b)News:id(small int,pk)|topicID(small int)|newHeading (varchar)|newText (text)
    c)People:id(small int,pk)|topicID(small int)|name (varchar)|email(varchar)

    ...and so on.
    So everytime the page is called:
    1. I look for a topic with a specific id on table Topics, i.e. id=4.
    2. I do a select query to the links table, looking for records where topic.ID=4.
    3.I do a second query to the news table, looking for records where topic.ID=4.
    ...and so on.
    This works, but it needs too many queries and it's a rudimentary way of getting data...isn't it?
    Thank you, Rudy.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    UNION is your only way to improve this

    in order for this to work, you have to select the same number of columns from each table, and the columns have to be datatype compatible
    Code:
    select 'link:'     as rowtype
         , linkName    as data1
         , url         as data2
      from topics
    left outer
      join links
        on topics.id = links.topicID
    union all
    select 'news:'     as rowtype
         , newHeading  as data1
         , left(newText,255) as data2 
      from topics
    left outer
      join news
        on topics.id = news.topicID
    union all
    select 'people:'     as rowtype
         , name          as data1
         , email         as data2 
      from topics
    left outer
      join people
        on topics.id = people.topicID
    ...
    basically you are squeezing all of your queries into the same template for the result set (note you have to flag rows from each table with some sort of marker to indicate which table they came from, so that you will format them correctly on output)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I guess I need to read some more tutorials on MySQL to understand the query you wrote. Anyway, now I know it can be done. That's what i was asking about.
    Thanks Rudy!

  6. #6
    Knowledge is key 2 progression Tryst's Avatar
    Join Date
    Sep 2003
    Location
    Wales
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why have you got to use separate SELECT queries to get all data relating to a Topic?

    Surely you can just perform one query, and use the INNER JOIN to link all these tables together in one Query?

    Tryst

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    tryst: no, that wouldn't work

    you'd get cross join effects

    here's an experiment involving only two tables -- people and their pets, people and their cars

    TABLE1
    curly cat
    curly bird
    larry dog
    moe rabbit
    moe hamster

    TABLE2
    curly ford
    curly chev
    larry acura
    larry pinto
    moe volkswagen

    try returning data from both tables with a join
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Zealot
    Join Date
    Feb 2005
    Location
    UK
    Posts
    121
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Plus any IDs that were not present in all 8 tables would be excluded by INNER JOINs.


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
  •