A little MySQL query help

[MacroGuru]

Alright,

I just wrote the following query:

 SELECT DISTINCT ci.cust_id, ci.first_name, ci.last_name, ci.home_phone, ci.work_phone, ci.mobile,ci.email, ls.lead_source, pi.product_name, oi.order_date, oi.order_id, opd.prod_price
FROM cust_info ci LEFT JOIN order_info oi 
 ON ci.cust_id = oi.cust_id
 LEFT JOIN order_product_details opd
 ON oi.order_id = opd.order_id
 LEFT JOIN lead_source ls
 ON ci.lead_source_id = ls.lead_source_id
 LEFT JOIN product_info pi
 ON opd.product_id = pi.product_id
WHERE oi.order_status_id IN (1,5) 
AND
 oi.order_date >= CURRENT_DATE() -60
AND ci.custi_d not in (select ci2.cust_id from cust_info ci2 LEFT JOIN order_info oi2
								ON ci.cust_id = oi.cust_id
								LEFT JOIN order_product_details opd2
								ON oi2.order_id = opd2.order_id
								WHERE opd2.product_id IN (6,16,26)) 
Group By ci.cust_id  

Just as I run it in MySQL...I found out we are running an outdated version of MySQL that doesn't support subselects.

I need to rewrite this using JOINS....

Now let me say the following....JOINS are not my strong suit, and neither is MySQL.....any help that someone can give me, I would extremely appreciate it....

[MIJB#19]

Hate to sound like an ass, but with that many joins, I'm tempted to think your data should be represented differently.

[cartman]

I've been looking at this for a while now, and I don't see a way to do it without using a subselect.

[Telle]

The "not in" clause usually (always?) cannot be replaced by a join. Same thing with "not exists".

[MacroGuru]

Originally Posted by MIJB#19

Hate to sound like an ass, but with that many joins, I'm tempted to think your data should be represented differently.

Yeah I know...it's why I am here.....I am rebuilding the system, moving from the PHP/MySQL solution they have been using and rolling to an MS SQL and .NET.

But in the meantime I am stuck supporting and snagging data for people out of this system. I figured out how to do it, but I need root access to the machine, and the MIS guys have said no, they will upgrade MySQL to 4.1 for me

Thanks for looking and trying to help me out here guys,.....

[PackerFanatic]

I will take a look and see what I can come up with.

[PackerFanatic]

Would you be able to create a view using that subselect you have and then left join your cust_id against that, checking for cust_ids not in that view...

ci.cust_id LEFT JOIN your view yv ON ci.cust_id <> yv.cust_id

Not sure how well that would work...

[MacroGuru]

Originally Posted by PackerFanatic

Would you be able to create a view using that subselect you have and then left join your cust_id against that, checking for cust_ids not in that view...

ci.cust_id LEFT JOIN your view yv ON ci.cust_id <> yv.cust_id

Not sure how well that would work...

Would love to....but they are not supported in MySQL.....

[MO542]

Originally Posted by indoorsoccersim

Would love to....but they are not supported in MySQL.....

If it doesn’t support inline views, then I’d create a temp table with the select statement in the subquery. Then I’d join those tables together.

[PackerFanatic]

Views aren't supported either? Shitty, heh...