asked on

MySQL Query - Join Multiple Statements

On an eCommerce Site, I need to be able to filter orders by Customer First Name and/or Customer Last Name and/or Date Range and/or Products Ordered - using any or all of those criteria to narrow down a list.

The mySQL Query I have to filter by everything BUT Products Order is:

SELECT orders.idorder, customers.name, customers.lastName, customers.email, orders,orderDate, orders.total, orders.paymentDetails FROM orders, customers WHERE orders.idCustomer = customers.idCustomer AND customers.name LIKE '%ache%' AND customers.lastName LIKE '%ne%' AND orders.OD4SR>=str_to_date('01/01/2009', '%m/%d/%Y') AND orders.OD4SR<=str_to_date('12/31/2009', '%m/%d/%Y') ORDER BY idOrder DESC

(and I use programming conditional statements to only include the parts of the Query needed based on Filter Data Entered)

The above query works without fail (although there is probably an easier way to do it)

Here is the problem - I need to also Filter based on Products Ordered, in that if I enter say "Widget", only those orders that ordered a "Widget" will show.

Right now, I run a SECOND Query based on the results of the First Query:

SELECT products.idProduct FROM orders, dbSessionCart, cartRows, products WHERE orders.idOrder='[idOrder-From-First-Query]' AND orders.idOrder = dbSessionCart.idOrder AND dbSessionCart.idDbSessionCart = cartRows.idDbSessionCart AND cartRows.idProduct = products.idProduct AND products.description LIKE '%Widget%'

If Data Returned from Second Query, show Data from First Query, if Not - Skip It.

Problem is - when the first query is done, it will return 50 results per page from one query. But then the second query will need to run 50 times (one for each result from the first query) in order to filter the returned orders based on products ordered. And that causes things to slow down quite a bit.

Table Relationships to one another are:

ORDERS
.idOrder
.idCustomer

CUSTOMERS
.idCustomer

Then for Products:

ORDERS
.idOrder

DBSESSIONCART
.idOrder
.idDbSessionCart

CARTROWS
.idDbSessionCart
.idProduct

PRODUCTS
.idProduct
.description

Is there any way, I can take the two queries above - and join them into ONE Query, so that I only return the rows I need?

This probably makes no sense, so please ask questions and I will answer as best I can.

Basically, all I am looking for is the query to filter based on EVERYTHING. From there, I can hack the query into conditional statements. I just have no idea how to join the two queries together to speed up the process and to get all data through one query, not dozens of repeated queries.

Thanks.

HainKurt

easiest way is this (not optimized version)

SELECT orders.idorder, customers.name, customers.lastName, customers.email, orders,orderDate, orders.total, orders.paymentDetails
FROM orders, customers
WHERE orders.idCustomer = customers.idCustomer
AND customers.name LIKE '%ache%'
AND customers.lastName LIKE '%ne%'
AND orders.OD4SR>=str_to_date('01/01/2009', '%m/%d/%Y')
AND orders.OD4SR<=str_to_date('12/31/2009', '%m/%d/%Y')
where exists (SELECT products.idProduct FROM orders, dbSessionCart, cartRows, products WHERE orders.idOrder='[idOrder-From-First-Query]' AND orders.idOrder = dbSessionCart.idOrder AND dbSessionCart.idDbSessionCart = cartRows.idDbSessionCart AND cartRows.idProduct = products.idProduct AND products.description LIKE '%Widget%')
ORDER BY idOrder DESC

HainKurt

better way

SELECT orders.idorder, customers.name, customers.lastName, customers.email, orders,orderDate, orders.total, orders.paymentDetails 
  FROM orders, customers 
 WHERE orders.idCustomer = customers.idCustomer 
       AND customers.name LIKE '%ache%' 
       AND customers.lastName LIKE '%ne%' 
       AND orders.OD4SR>=str_to_date('01/01/2009', '%m/%d/%Y') 
       AND orders.OD4SR<=str_to_date('12/31/2009', '%m/%d/%Y') 
 where exists (
       SELECT products.idProduct 
         FROM dbSessionCart, cartRows, products 
        WHERE orders.idOrder = dbSessionCart.idOrder 
              AND dbSessionCart.idDbSessionCart = cartRows.idDbSessionCart 
              AND cartRows.idProduct = products.idProduct 
              AND products.description LIKE '%Widget%'
              )
ORDER BY idOrder DESC

Open in new window

Granticus

ASKER

Using MySQL 5.1.40, unfortunately the Query returns a SQL Syntax Error near 'where exists'.

I think it is because WHERE is already used, and then it is asking to use WHERE EXISTS (and that second where is causing the error). So I changed it to AND EXISTS, which seemed to bypass the error - but then the query process just ran forever (over five minutes) and I had to kill it.

I think the problem is going to be that the ORDERS table has 10,000+ records and the CARTROWS table has 33,000+ records, thus making the "where exists" too big to manage?

I am beginning to think I can not accomplish filtering by Product Data just because of how the tables are structured.

Sharath S

Replace second WHERE with AND.

SELECT orders.idorder, customers.name, customers.lastName, customers.email, orders,orderDate, orders.total, orders.paymentDetails 
  FROM orders, customers 
 WHERE orders.idCustomer = customers.idCustomer 
       AND customers.name LIKE '%ache%' 
       AND customers.lastName LIKE '%ne%' 
       AND orders.OD4SR>=str_to_date('01/01/2009', '%m/%d/%Y') 
       AND orders.OD4SR<=str_to_date('12/31/2009', '%m/%d/%Y') 
 and exists (
       SELECT products.idProduct 
         FROM dbSessionCart, cartRows, products 
        WHERE orders.idOrder = dbSessionCart.idOrder 
              AND dbSessionCart.idDbSessionCart = cartRows.idDbSessionCart 
              AND cartRows.idProduct = products.idProduct 
              AND products.description LIKE '%Widget%'
              )
ORDER BY idOrder DESC

Open in new window

Granticus

ASKER

@Sharath_123
If you see my comment minutes before yours, you'll notice that I had applied that change, and the results it gave (basically causing the query to run too long and having to be killed). Unfortunately, while it fixed the query error, it still did not provide desired results.

HainKurt

again same mistake and Sharath_123 corrected it :)

Granticus

ASKER

@HainKurt
Agreed, but please see my other comments - which both addresses the error and the result.

HainKurt

ok, is this better (still there is room for performance though)

select * from (
SELECT orders.idorder, customers.name, customers.lastName, customers.email, orders,orderDate, orders.total, orders.paymentDetails 
  FROM orders, customers 
 WHERE orders.idCustomer = customers.idCustomer 
       AND customers.name LIKE '%ache%' 
       AND customers.lastName LIKE '%ne%' 
       AND orders.OD4SR>=str_to_date('01/01/2009', '%m/%d/%Y') 
       AND orders.OD4SR<=str_to_date('12/31/2009', '%m/%d/%Y') 
       ) x
where  exists (
       SELECT 1 
         FROM dbSessionCart, cartRows, products 
        WHERE x.idOrder = dbSessionCart.idOrder 
              AND dbSessionCart.idDbSessionCart = cartRows.idDbSessionCart 
              AND cartRows.idProduct = products.idProduct 
              AND products.description LIKE '%Widget%'
              )
ORDER BY idOrder DESC

Open in new window

ASKER CERTIFIED SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kevin Cross

Please run:

EXPLAIN SELECT orders.idorder, customers.name, customers.lastName, customers.email, orders,orderDate, orders.total, orders.paymentDetails 
  FROM orders, customers 
 WHERE orders.idCustomer = customers.idCustomer 
       AND customers.name LIKE '%ache%' 
       AND customers.lastName LIKE '%ne%' 
       AND orders.OD4SR>=str_to_date('01/01/2009', '%m/%d/%Y') 
       AND orders.OD4SR<=str_to_date('12/31/2009', '%m/%d/%Y') 
 and exists (
       SELECT products.idProduct 
         FROM dbSessionCart, cartRows, products 
        WHERE orders.idOrder = dbSessionCart.idOrder 
              AND dbSessionCart.idDbSessionCart = cartRows.idDbSessionCart 
              AND cartRows.idProduct = products.idProduct 
              AND products.description LIKE '%Widget%'
              )
ORDER BY idOrder DESC

Open in new window

And post the results ...

Also, you can try to run EXPLAIN on this and see if it makes a difference:

SELECT o.idorder
     , c.name, c.lastName, c.email
     , o.orderDate, o.total, o.paymentDetails 
FROM orders o
JOIN customers c ON o.idCustomer = c.idCustomer 
JOIN (
   SELECT dbSessionCart.idOrder, COUNT(1) AS Cnt
   FROM dbSessionCart, cartRows, products 
   WHERE EXISTS (
      SELECT 1
      FROM cartRows
     JOIN products ON cartRows.idProduct = products.idProduct 
     WHERE dbSessionCart.idDbSessionCart = cartRows.idDbSessionCart 
     AND products.description LIKE '%Widget%'
   )
   GROUP BY dbSessionCart.idOrder
) p ON p.idOrder = o.idOrder
WHERE (o.OD4SR>='2009-01-01' AND o.OD4SR<'2010-01-01')
AND (c.name LIKE '%ache%' AND c.lastName LIKE '%ne%')
ORDER BY idOrder DESC

Open in new window

Kevin Cross

It appears HainKurt was typing also. My secondary solution seems to be in line with http:#a35742299. The likelihood is as you stated in that the issue is with volume of records and the different checks using LIKE on fields that are usually not indexed in schemas such as this. However, the hope is that all the join fields are; therefore, showed my suggestion in ANSI format to make it a little clearer which those are. The net is though to use EXPLAIN to see if we can get to the bottom of things a little better, so if you could post the results of both that would be great.

Sharath S

Do you have any indexes on these tables?

Granticus

ASKER

I've tried all of the Queries supplied, and one not only worked, but was super fast even when I removed all the customer/date filter criteria and forced it to just bring up orders based on product filter and nothing else.

And that was Post 35742299 by HainKurt

I did have to make two small changes to the code.

1. ORDER BY idOrder DESC returned an error that idOrder was ambiguous as it could mean x.idOrder OR y.idOrder. I changed it to x.idOrder and it worked.

2. If the same order had multiple products matching the criteria, it would display that order number multiple times. I needed the order to only show once. In the second half of the query, I changed SELECT dbSessionCart.idOrder to SELECT DISTINCT dbSessionCart.idOrder to only return the order number once. When I made that change, query worked flawlessly.

Thank you to everyone that helped. I am very impressed with not only the speed of the responses, but the quality of the responses as well.