dynamic join types...
Posted: Sat Apr 22, 2006 12:37 pm
I have a query that looks something like this:
Here's the problem, if I don't have a row on my cs_test_transactions table, it won't return anything. I need a way to dynamically determine if there is a row on that table and if not, do a different join type. I'd like to do this all in one query if possible. The other alternative I thought of was to statically create values in my query for a row on that table thereby 'tricking' it to think there is a row.
I can't seem to wrap my head around this in either scenario though...any suggestions?
Code: Select all
SELECT 1 AS transactionTable, t. * , 1 AS rebillingTable, r. * , 1 AS subscriptionTable, s. * , 1 AS websiteTable, w. * , 1 AS bankTable, b.bk_cc_bank_enabled, b.bank_email, 1 AS companydetailsTable, c.contact_email, c.customer_service_phone, c.cd_recieve_order_confirmations, c.companyname, c.cd_tracking_init_response, c.cd_enable_tracking, c.gateway_id
FROM cs_test_transactiondetails AS t
LEFT JOIN `cs_companydetails` AS c ON t.`userId` = c.`userId`
LEFT JOIN `cs_rebillingdetails` AS r ON t.`td_rebillingID` = r.`rd_subaccount`
LEFT JOIN `cs_subscription` AS s ON t.`td_subscription_id` = s.`ss_ID`
LEFT JOIN `cs_bank` AS b ON t.`bank_id` = b.`bank_id`
LEFT JOIN `etel_dbsmain`.`cs_company_sites` AS w ON t.`td_site_ID` = w.`cs_ID`
WHERE w.`cs_reference_ID` = 'myRefId'
LIMIT 1I can't seem to wrap my head around this in either scenario though...any suggestions?