JOIN question

[LaureenJ]

Can I do a query using join for two tables in mySQL?

SELECT COUNT (*) AS itema_count, itema_operation, old_itema_code, new_itema_code
FROM itema_changes
WHERE edit_operation IN ('-', '+', '>')
AND customer_id= '123'
AND timestamp > '2006-01-15' AND pickup_timestamp < '2006-01-21'
ORDER BY itema_count
DESC LIMIT 10

JOIN

SELECT COUNT (*) AS itemb_count, itemb_operation, old_itemb_code, new_itemb_code
FROM itemb_changes
WHERE edit_operation IN ('-', '+', '>')
AND customer_id= '123'
AND timestamp > '2006-01-15' AND pickup_timestamp < '2006-01-21'
ORDER BY itemb_count
DESC LIMIT 10

 

[r937]

change the word JOIN to UNION ALL, and place parentheses around the SELECTs

also, add the necessary GROUP BY clause to each SELECT

note that the resulting dataset will have column names as determined by the column names used in the first SELECT

so in order that you be able to distinguish which table the row came from, add a literal to each SELECT

( SELECT 'itema' as source
     , COUNT(*) AS itema_count
     , itema_operation
     , old_itema_code
     , new_itema_code 
  FROM itema_changes 
 WHERE edit_operation IN ('-', '+', '>') 
   AND customer_id= '123' 
   AND timestamp > '2006-01-15' 
   AND pickup_timestamp < '2006-01-21' 
GROUP
    BY itema_operation
     , old_itema_code
     , new_itema_code    
ORDER 
    BY itema_count DESC LIMIT 10 )
UNION ALL
( SELECT 'itemb'
     , COUNT(*) AS itemb_count
     , itemb_operation
     , old_itemb_code
     , new_itemb_code 
  FROM itemb_changes 
 WHERE edit_operation IN ('-', '+', '>') 
   AND customer_id= '123' 
   AND timestamp > '2006-01-15' 
   AND pickup_timestamp < '2006-01-21' 
GROUP
    by itemb_operation
     , old_itemb_code
     , new_itemb_code    
ORDER 
    BY itemb_count DESC LIMIT 10 )

r937.com | rudy.ca