Scott, how about the use of a MERGE table?
create a merge table 'orders' with 'orders_npfd' and 'orders_npfs'
underneath it.
http://dev.mysql.com/doc/refman/5.0/en/merge-storage-engine.html
Dan
On 8/2/06, Scott Haneda <[EMAIL PROTECTED]> wrote:
Forgive the mess I present you, this is a strangely done site with a even
stranger structure and methodology that I am trying to work with.
The basic idea is one website has multiple websites in it. So if you place
an order with website A, orders_A is where the data is stored, if you place
an order with website B, orders_B is where the data is stored. The site
changes from A to B based on the url. Anyway...
SELECT o.id, u.industry,
u.email, u.b_first_name, u.b_last_name,
t.created
FROM orders_npfd AS o
INNER JOIN users AS u
ON (o.user_id = u.id)
INNER JOIN transactions as t
ON (t.order_id = o.id)
WHERE t.type in ('first_charge', 'recurring')
ORDER BY t.created DESC;
This basically gives me a list of all orders that I want to see from the
orders_npfd table, works as I need it to. Now, I need to add in a second
table, for the orders from the other table. Table structure is more or less
the same, at least, the data I am selecting. So the second table is
orders_npfs <- note the 's'
How can I add that in so I will get results out of that table as well?
Second problem, each order table of course gets an id, or orderId, in the
above example, it is o.id and linked to t.order_id. The problem is, o.id is
autoinc PK and will/can overlap with the id's from the either of the orders
table.
However, users u.id will never overlap, nor share table data, so I think I
can use that in my condition to make sure there is no pollution of orders?
If I use a join in the above to get the second table data in there, I would
have to add in more x.foo items to my select, which really will not help me.
I am sure this is pretty confusing, if anyone has any suggestions, aside
from rethinking the design entirely, I would most appreciate it.
Maybe select the contents of both tables into a tmp table, and use that as
my join table above?
--
-------------------------------------------------------------
Scott Haneda Tel: 415.898.2602
<http://www.newgeo.com> Novato, CA U.S.A.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
