select from multiple tables

cdegournay

Hi,

I'd like to select data from several tables (more than 2) to show the last entries among all tables. Ex : I have 3 tables : "cd", "book" and "dvd" with "title" and "date" fields. What I want to get is something like this :

misery [book]
titanic [dvd]
gladiator [dvd]
kill bill [cd]
discworld [book]

number 1 being the most recent entry.

I tried " SELECT * FROM cd, book, dvd ORDER BY date LIMIT 5" but of course it doesn't work.
I didn't find any solution on the net so I call for help.

Thanks.

leatherback

Hi,

You need to tell your database in which way these tables relate to eachother. Now, in your case there is no relation, as far as I can tell.

If I were you (Not sure how full your tables are already..) I would set up one main table, products:

ID | type | title | date
1 | DVD | Titanic | 01022004
2 | CD | Kill Bill | 01022003
etc

and do the queries from there.

Make life much easier, and you have a proper design. Also if you want to expand to displaying gardening tools or something.

jlippman

Is there any other way to do this... at this point it would take me forever to redo the whole database and scripting.

I had someone write me a script and now I want to make it display and event (table 1) and sort it by the event date (table 1) and then group by city (table 2). any thoughts?