[RESOLVED] selecting from multiple tables in mysql database

semtex

I want to make an RSS feed displaying the latest updates on my website. My site has 3 sections: reviews, interviews and news. I want to combine them into 1 RSS feed.

So that means I have to find the 10 newest items throughout all 3 database tables. The problem is that the tables have some fields in common (like 'activated_dt', a datetime field) but not all.

What would be a good query to find the 10 newest items??

SELECT ID
FROM reviews,interviews,news
ORDER BY activated_dt DESC
LIMIT 10

But how do I know what table the ID is from?

laserlight

The problem is that the tables have some fields in common (like 'activated_dt', a datetime field) but not all.

How are the tables related?

semtex

They do not relate to each other. They're different tables.
The table "reviews" stores all information about reviews that appear on my website (band name, cd title, release date, review_text...)

The table "interviews" stores all interviews information
(band name, person talking, quote)

The same for the "news" table.

So basically I want to find the latest update. If I add an interview to my site, the latest update will come from the table "interviews". If I add a review the day after, then the latest update will come from the table "reviews".

I store a datetime called "activated_dt" that has the datetime of the update. And every table has that field.

I hope that's clear?

Basically I need to query the 3 tables and look for the "activated_dt" field and order that in descending order. The first result will be the latest updated item. But then I need to find which table that item comes from (news, interviews or reviews).

laserlight

hmm... as an interim solution, you could select the latest 10 rows from each table, then merge the results and use the latest 10 of the merged results.

They do not relate to each other. They're different tables.

I think that the tables should be related in some way, though with the information you have provided I am not sure what is the correct relationship.

semtex

laserlight wrote:
I think that the tables should be related in some way, though with the information you have provided I am not sure what is the correct relationship.

They are not related to each other. Every table has 2 fields that have the same name though: ID, activated_dt

activated_dt holds a datetime value that represents the date and time of the record's input.

laserlight

They are not related to each other. Every table has 2 fields that have the same name though: ID, activated_dt

The thing is, if they really are not related at all, then it does not make sense to attempt to join them. They are related, but your current database schema does not reflect that.

semtex

Do you mean the best thing would be to make some kind of relation between the tables in the database, so I could ease the query?

Like making a new table storing the latest updates?

laserlight

Do you mean the best thing would be to make some kind of relation between the tables in the database, so I could ease the query?

I am hesitant to make any particular schema recommendation because I do not quite understand what is the actual relationship between the sets of data.

One possibility is that they are all part of an articles table. Another possibility (effectively your "making a new table storing the latest updates" idea) is that you can record the datetime values in an articles table, and then have the id of the articles table in as a foreign id in each of the three tables (but this sounds a little fishy to me).

Then again, you could settle for the hack I described in my first post.

semtex

Thanks. The problem is that, at the moment, I'm not quite sure how to "merge" the results. I know it's possible to order an array, but I'm not sure how to do that.

semtex

Just wanted to let you know that I found a good way to do this. At least it seems to be working. I use a UNION query.

$sql = "(SELECT
ID,
activated_dt,
1 as tableid
FROM
reviews
ORDER BY
activated_dt DESC
LIMIT 15)
UNION
(SELECT
ID,
activated_dt,
2 as tableid
FROM
interviews
ORDER BY
activated_dt DESC
LIMIT 15)
ORDER BY
activated_dt DESC
LIMIT
15";

This way I am able to know the ID of the returned items, along with the tableID!