Basic question - use of JOIN or..?

[verdus]

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.

[r937]

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)

[verdus]

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.

[r937]

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

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)

[verdus]

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!

[Tryst]

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

[r937]

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

[Roger Ramjet]

Plus any IDs that were not present in all 8 tables would be excluded by INNER JOINs.