PDA

View Full Version : A little MySQL query help

MacroGuru
10-06-2005, 11:08 AM
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
10-06-2005, 01:29 PM
Hate to sound like an ass, but with that many joins, I'm tempted to think your data should be represented differently.
cartman
10-06-2005, 01:42 PM
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
10-06-2005, 01:54 PM
The "not in" clause usually (always?) cannot be replaced by a join. Same thing with "not exists".
MacroGuru
10-06-2005, 03:34 PM
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 :D

Thanks for looking and trying to help me out here guys,.....
PackerFanatic
10-06-2005, 03:40 PM
I will take a look and see what I can come up with.
PackerFanatic
10-06-2005, 03:57 PM
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
10-06-2005, 04:05 PM
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
10-06-2005, 04:09 PM
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
10-06-2005, 04:12 PM
Views aren't supported either? Shitty, heh...