dynamic join types...

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

dynamic join types...

Post 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?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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)
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Re: dynamic join types...

Post 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 
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post 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?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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'
Post Reply