if i have roughly 25 identical tables is it possible to do the following
SELECT tm_name,
`tablename` as Activity
from t1, t2 ,t3
where date = curdate
or would it be better to add another column to the table thats hidden from the users and automatically populate with the tablename.
Jump to PostThe problem is that you will most likely not get the results you want. I suggest you create a query (or view) like this:
SELECT `tm_name`, `date`, 't1' AS TableName FROM t1 UNION SELECT `tm_name`, `date`, 't2' AS TableName FROM t2 -- and so on
The problem is that you will most likely not get the results you want. I suggest you create a query (or view) like this:
SELECT `tm_name`, `date`, 't1' AS TableName FROM t1
UNION
SELECT `tm_name`, `date`, 't2' AS TableName FROM t2
-- and so on
When you list multiple table names after the FROM keyword, you are essentially creating JOINing tables. Based on your description, it sounds like you are trying to query multiple tables with the same information to produce one result set. For this type of scenario, I would suggest that you use the UNION keyword seperated by your different SELECT statements. For example...
SELECT tm_name FROM table1
UNION
SELECT tm_name FROM table2
UNION
SELECT tm_name FROM table3
As pritaeas said, I'd recommend adding a table idetifier column, since that is the only way of identifying which table is every row from.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.