MSSQL -> MYSQL Method

[d2g]

I have an sql query that worked on MS SQL 2000.. I need to know what the mysql version needs to look like..

the MS sql query is as such:

Select CARTS.CARTNUMBER, CARTS.UNITPRICE, QUANTITY = (Select SUM(numitems) from selitems where SELITEMS.CARTNUMBER = CARTS.CARTNUMBER)

basicly thats an example of what im talking about.. It works in MS SQL but in mysql the same query gives me the error: "Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'Select SUM(numitems) from selitems where SELITEMS.CARTNUMBER"

Does mysql support this using a different method?

Thanks!

Shane

 

[r937]

here is the direct conversion of the syntax --

select cartnumber
     , unitprice
     , ( select sum(numitems) 
           from selitems 
          where cartnumber 
              = carts.cartnumber )  as quantity 
  from carts

if you are not on mysql 4.1 yet, the above will still produce an error, but fortunately, there is another way to obtain the same results --

select carts.cartnumber
     , carts.unitprice
     , sum(selitems.numitems) as quantity
  from carts              
left outer
  join selitems 
    on carts.cartnumber
     = selitems.cartnumber
group
    by carts.cartnumber
     , carts.unitprice

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[d2g]

Thanks for the quick reply! I think we are going to move up to 4.1 tonight, I'll let you know how this turns out! Thanks again!

 

[d2g]

4.1 did the trick! But now we are onto the next phase.. I obtained an sql converter to attempt to find the differences in code but its even giving us some problems with this next one.. This is an example i obtained from the net for MS Sql.. need to accomplish the same thing in mysql.. I think this is the last of our weird querys types.

SELECT DISTINCT
saleschannels.saleschannelid,
saleschannels.saleschannel,
saleschannels.shortcode,
itemlocation.assigned
FROM saleschannels,
itemlocation
WHERE saleschannels.saleschannelid = itemlocation.assigned
AND itemlocation.assigned in
(
SELECT DISTINCT itemlocation.assigned
FROM itemlocation
WHERE itemlocation.companyid = 43392617
AND itemlocation.shipped = "false"
AND (itemlocation.deleterec = "false"
OR itemlocation.deleterec is Null)
)
ORDER BY saleschannels.saleschannelid

 

[r937]

that query should work just fine in mysql 4.1

what error are you getting?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[d2g]

Well the performance seems to be the issue.. IF i take out the nested query (IN) it runs ok. but put that in there and it crawls to a standstill and doesn't return the result (atlest not in the minute i let it sit there). MS Sql pumped that out right away.. I'm thinking maby it might be something my actual tables. Causing that query to take forever.

I'll run some more tests now that i know its not the query.

 

[r937]

have you defined any indexes on the table? an index on itemlocation.assigned would be a good place to start

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[d2g]

Checked the indexes.. they are in place.. Both querys work fine alone and quick, but nest them together and it takes
36359 ms to come up on my query manager.. and returns the 4 results its supposed too..

Here is the table layout for the query.
saleschannels:
saleschannels.idkey (Primary Key Auto Incr. Type INT 11)
saleschannels.saleschannelid (TYPE INT 11)
saleschannels.saleschannel (TYPE VARCHAR 250)
saleschannels.shortcode (TYPE VARCHAR 50)
Indexes on this table are:
PRIMARY - COLUMNS: IDKEY UNIQUE
SALESCHANNELID - COLUMNS: SALESCHANNELID

itemlocation:
itemlocation.stockid (Primary Key Auto Inc. Type INT 11)
itemlocation.assigned (TYPE INT 11)
itemlocation.companyid (TYPE INT 11)
itemlocation.shipped (TYPE VARCHAR 50)
itemlocation.deleterec (TYPE VARCHAR 50)
Indexes on this table are:
PRIMARY - COLUMNS: STOCKID UNIQUE
STOCKID - COLUMNS: STOCKID UNIQUE
ASSIGNED - COLUMNS: ASSIGNED

I'm sure im missing something simple.. MY Sql is a bit more picky than MS SQL was, but i'm sure thats why it will perform better.

Thanks again!

 

[r937]

why do you have DISTINCT in your queries?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[r937]

try this, see if it gives the same results --

select saleschannels.saleschannelid
     , saleschannels.saleschannel
     , saleschannels.shortcode
     , itemlocation.assigned
  from saleschannels
inner
  join itemlocation 
    on saleschannels.saleschannelid 
     = itemlocation.assigned
   and itemlocation.companyid = 43392617
   and itemlocation.shipped = 'false'
   and coalesce(itemlocation.deleterec
               ,'false') = 'false'
order 
    by saleschannels.saleschannelid

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[ericbrunson]

Why not?

SELECT DISTINCT
         saleschannels.saleschannelid,
         saleschannels.saleschannel,
         saleschannels.shortcode,
         itemlocation.assigned
FROM     saleschannels,
         itemlocation
WHERE    saleschannels.saleschannelid  = itemlocation.assigned
 AND     itemlocation.companyid  = 43392617
 AND     itemlocation.shipped  = "false"
 AND     (     itemlocation.deleterec  = "false"
           OR  itemlocation.deleterec  is Null )
ORDER BY saleschannels.saleschannelid

See if that runs faster.

 

[d2g]

Ok here are the results. I restarted the mysql machine, and ran all 3 queries.

My original = 35719 ms

r937's (had to add distinct) because we want to list only the unique saleschannelid's from the query. Result was 156ms

ericburnson's = 16 ms..

Eric's is of course what i should have done in the first place... Sometimes i can overthink things! Thanks again guys! I wonder if thats a bug with the nested queries or probably just bad sql on my part.. Thanks again!