CODEWITHSUNDEEP
mysqldatabasestored-procedurestriggersmysql-workbench
chase cabrera
chase cabrera

Reputation: 129

Dynamic MySQLTable

Here's my situation. I have 9 tables in my database which all have a c_no (int), a cl_no (int), and a status (boolean). I need to know the best way to create a dynamic table which distinctly shows the c_no, cl_no, and what table it comes from denoted as a number.

The difficult part is making it reactive or dynamic to all the status 1's. Lets say someone updates one of the statuses we need that record to be removed. Also, if someone updates one of the cl_no to an new one we need that to be inserted into the table and if that cl_no for that c_no doesn't exist anymore from the origin table we need that removed as well. Basically we need an up-to-date table that consolidates all the info.

Example:

Table_One

c_no, cl_no, status

1,1,1

1,2,1

1,1,1

1,1,1

1,1,0

Table_Two

c_no, cl_no, status

1,10,1

1,3,1

1,32,1

1,1,0

Desired_Table

c_no,cl_no,ref_table

1,1,1

1,2,1

1,32,2

1,3,2

1,10,2

I have tried the following will without success,

1.) Creating a view which ran at 50+ secs, we need it to return a result in under 1 seconds. With 9 joins, the query ran too long.

2.) Creating triggers for each table, but when importing or updating 10,000 records the response time exponentially increase to well over 2 minutes in test cases.

Sorry for the poor format. Thank you in advanced for your time and help!!

Upvotes: 0

Views: 37

Answers (2)

Carsten Massmann
Carsten Massmann

Reputation: 28196

select cno, clno, min(tno) from
( select cno, clno, 1 tno from tbl1 where status=1
  union all
  select cno, clno, 2 from tbl2 where status=1
  union all
  ...
) u group by cno,clno

Upvotes: 0

Doug
Doug

Reputation: 547

Your looking for a union statement I think. Something along the lines of

SELECT c_no, cl_no, 1 FROM Table_One WHERE status = 1
UNION ALL
SELECT c_no, cl_no, 1 FROM Table_Two WHERE status = 1

If you then stick that into a view it will be dynamic and freely available. This does assume that your not needing to remove or deal with duplicates in any way between the two tables. The query would need to be changed slightly to manage that.

Your other option with the UNION is something like

SELECT c_no, cl_no, table_id FROM ( SELECT c_no, cl_no, 1 as table_id, status FROM Table_One UNION ALL SELECT c_no, cl_no, 1 as table_id, status FROM Table_Two ) lookup WHERE status = 1

Neither of these queries have been tested but should give you some ideas for options

What was your original query?

Upvotes: 1

Related Questions