MySQL Forums
Forum List  »  Microsoft SQL Server

sub query
Posted by: Simon Collins
Date: September 23, 2008 10:12AM

Hi

We are currently evaluating a migration of data from MsSql to MySql.

I've copied the tables and indexes across and the data is all fine. However, I have a subquery which returns data on MSSQL, but no data on MySQL

the subquery below refers to ordersh.orddate, which is in the main query. When I alter that to a physical date (stored as integer 20080931 etc), the query runs (which isn't helpful to what i'm trying to achieve!).

SELECT DISTINCT ORDERSH.ORDHID
FROM ORDERSD
INNER JOIN ORDERSH ON ORDERSD.ORDHID = ORDERSH.ORDHID
INNER JOIN CUSTOMERMAILINGLIST on ORDERSH.ORDCUSTOMER = CUSTOMERMAILINGLIST.CUSTID
WHERE ORDERSD.ITEMSTATUS = 5 AND (ORDERSH.ORDSOURCE = 'website') and CUSTOMERMAILINGLIST.LISTID IN
(select CATALOGUESENT.MAILINGLISTID
from CATALOGUESENT
where (CATALOGUESENT.CATALOGUEID = 29)
And (CATALOGUESENT.ISADDITIONALLIST = 0)
and ORDERSH.ORDDATE > CATALOGUESENT.DATESENT)

"explain" gives the following output

1 | PRIMARY | ORDERSD | ref | IX_WAREHOSUESALECOUNT,Index_5,pickditem,Index_8,HID,Index_10 | Index_10 | 5 | const | 160082 | Using where | Using temporary
1 | PRIMARY | ORDERSH | eq_ref | PRIMARY,Index_4,Index_5,Index_6 | PRIMARY | 4 | stilettoxt.ORDERSD.ORDHID | 1 | Using where
1 | PRIMARY | CUSTOMERMAILINGLIST | ref | CUSTID_LISTID_IX,CUSTID_IX,Index_4 | CUSTID_LISTID_IX | 5 | stilettoxt.ORDERSH.ORDCUSTOMER | 1 | Using where | Using index | Distinct
2 | DEPENDENT SUBQUERY | CATALOGUESENT | index_subquery | Index_1,Index_2,Index_3 | Index_2 | 15 | func,const,const | 1 | Using where


Any ideas?

Options: ReplyQuote


Subject
Written By
Posted
sub query
September 23, 2008 10:12AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.