Mysql: How to SELECT from diferent tables...

[ayukawaa]

Due to the fact of the many fields asociated with a single company for some project (200+), I've have to change from a single row to 6 tables asociated with the main table through the id of the row of the main table...

FIRST: I'm not allowed to DELETE or UPDATE ANYTHING.

Each of the tables can be updated mutiples times...

Every time a record is updated, it is INSERT as a NEW record (with the ID of the main table)

Even the DELETE is a row in the status table because it is not really deleted and can be undone with another record where status change again to 'live'.

So, for every table, you have a history of all the changes made, where were made, who made them and can see them one by one.

How can I SELECT a single RECORD from ALL tables at the same time?

The right ones should be the LAST ONES INSERTED IN EACH TABLE.

In other words, it should be something like mergin all of this

SELECT * FROM TABLE1 where idp=999
+ select * from TABLE2 where idp=999 ORDER BY idp DESC limit 1
+ select * from TABLE3 where idp=999 ORDER BY idp DESC limit 1
+ select * from TABLE4 where idp=999 ORDER BY idp DESC limit 1
+ select * from TABLE5 where idp=999 ORDER BY idp DESC limit 1
+ select * from TABLE6 where idp=999 ORDER BY idp DESC limit 1

Some clue?

Now I'm making 7+ selects for showing ONE page and I want to reduce it to only ONE select.

I don't mind if I have to change the database for another schema if you have a better way, but I repeat: I'M NOT ALLOWED TO OVERWRITE ANYTHING.

[Rabbit]

You mean like a UNION ALL query?

sql query
UNION ALL
sql query
UNION ALL
sql query

[ayukawaa]

Rabbit no, that will select multiples values from multiples tables.

One table can have 5 updates, but another table can have 23 updates, but I only want the LAST RECORD of EACH table of the id given .

That's driving me crazy.

*_*

[Rabbit]

I know. That's why you just select one from each table and union them all together.

[jdstankosky]

You can try something like this:

SELECT *
FROM table1 t1
JOIN table2 t2 ON t1.idp = t2.idp
JOIN table3 t3 ON t3.idp = t2.idp
JOIN table4 t4 ON t4.idp = t3.idp

WHERE t1.idp = 999

For as many tables as you have, keep adding joins. This SHOULD grab everything from the row(s) where the idp = 999 across all the tables joined.

[Rabbit]

@jd, that's not what they're asking for. They only want one from each table. They have to use the union all option.

[jdstankosky]

UNION ALL will display duplicates. It doesn't care about multiple results. The OP said this is a behaviour he wants to avoid.

[Rabbit]

@jd, actually, they weren't talking about duplicates. They were talking about multiple records per table. They only want one record per table. It doesn't have anything to do with duplicates.

In fact, from their description, if there were to be duplicate records in different tables, it sounds as if they would actually want those duplicates. Hence the suggestion to use UNION ALL instead of UNION.

[ayukawaa]

No, I don't want to obtain multiples records from the diferent tables, I want A SINGLE RECORD with the most recent changes of ALL THE TABLES.

The admin will have a history button to obtain all the changes made if he/she want, but when presenting info I want only ONE record.

[Rabbit]

That's not what your sample query shows. But if what you want is what you are now describing and not what your sample query describes. You will still use a union all. Union all the tables and then select the top 1.