Page 1 of 1

dynamic join types...

Posted: Sat Apr 22, 2006 12:37 pm
by Burrito
I have a query that looks something like this:

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 1
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?

Posted: Sat Apr 22, 2006 1:03 pm
by timvw
Lookup the difference between inner and outer join :)

(Afterwards you can still use an IFNULL function in your select clause to set it to something different it if it was autofilled with a NULL)

Re: dynamic join types...

Posted: Sat Apr 22, 2006 1:07 pm
by timvw
Something like (untested :D)

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 OUTER 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 1 

Posted: Sat Apr 22, 2006 1:50 pm
by Burrito
Tim, that still returns nothing for me.

I tried 'LEFT OUTER JOIN'ing all of the tables, and still nada...any other ideas?

Posted: Sat Apr 22, 2006 2:22 pm
by timvw
Can't it be solved with a SELECT COUNT(somecolumn) AS count (offcourse you need to GROUP BY that somecolumn now, and the LIMIT can be thrown away)... This way you recieve 0 if there were no such rows..

Code: Select all

SELECT COUNT(t.primarykey) AS count
FROM cs_test_transactiondetails AS t
LEFT OUTER 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`
GROUP BY t.primarykey
WHERE w.`cs_reference_ID` = 'myRefId'