I have seen many examples but they don't match with my special case (I know... everyone's case is a special case :D )
I have to match values from different tables, some of which are named after the id field of the main table (whenever a new customer is added, a "Bill_xxx" table is created). For example:
table: Customers
--------------------------------
custId | name | status
--------------------------------
34 | CustomerA | Active
35 | CustomerB | Inactive
36 | CustomerC | Active
--------------------------------
table: Bill_34
id | InvoiceNumber
001 | 98767575
002 | 98767576
table: Bill_35
id | InvoiceNumber
001 | 36528
002 | 36529
table: Bill_36
id | InvoiceNumber
001 | 485795
002 | 485796
Basically I'm trying to find a way to build a query like this, just to run it on demand via SQLYog. (I know I can use a cursor, but I need to run this in a prod db, for which I don't have permissions to create Stored Procedures):
Select * from
(Select custId from Customer where status = 'Active') as customers
INNER JOIN
(Select * from Bill_[custId]) as bills
If it's not clear, I need to dynamically build the name of the table in the second query based on the id's I get from the first one
And expect to receive something like this
--------------------------------------------
custId | id | InvoiceNumber
--------------------------------------------
34 | 001 | 98767575
34 | 002 | 98767576
35 | 001 | 36528
35 | 002 | 36529
36 | 001 | 485795
36 | 002 | 485796
____________________________________________
Is this even possible?
