Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Posts
    15

    Unanswered: translating from ms sql to mysql statements

    hi folks,

    I got following query in M$ $ql, and i want to produce the same result using mysql. how should i modify the query?

    i was thinking using inner join, but ... cant figure out how, pls help!! TIA!

    select VendorID,
    VendorName,
    Unit,
    Street,
    City,
    Region,
    Country,
    PostalCode,
    Telephone,
    Fax,
    Email,
    Website,
    FirstName,
    LastName,
    ClickThroughs,
    Views,
    if(Authorized = 1 , 'Y', 'N') as Authorized,
    'Banners' = ( select count(*) from Banners where Banners.VendorId = Vendors.VendorId )
    from Vendors
    where PortalId is null
    and Authorized = 0
    order by VendorName

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    here ya go --
    Code:
    select VendorID
         , VendorName
         , Unit
         , Street
         , City
         , Region
         , Country
         , PostalCode
         , Telephone
         , Fax
         , Email
         , Website
         , FirstName
         , LastName
         , ClickThroughs
         , Views
         , 'N'      as Authorized
         , count(Banners.VendorId) as Banners
      from Vendors
    left outer
      join Banners 
        on Vendors.VendorId = Banners.VendorId 
     where PortalId is null
       and Authorized = 0
    group
        by VendorID
         , VendorName
         , Unit
         , Street
         , City
         , Region
         , Country
         , PostalCode
         , Telephone
         , Fax
         , Email
         , Website
         , FirstName
         , LastName
         , ClickThroughs
         , Views
    order 
        by VendorName
    i ditched the IF (which you would ordinarily convert into a CASE expression) because you test for Authorized=0 in the WHERE Clause

    rudy
    http://r937.com/

  3. #3
    Join Date
    Jan 2003
    Posts
    15
    Great! Thanks Rudy!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •