| MacroGuru |
10-06-2005 11:08 AM |
A little MySQL query help
Alright,
I just wrote the following query:
Code:
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.... |
