SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2005
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Complicated MySQL Query, Need help

    Hey, i'm doing maintence on this guy's site, and it's a very poorly designed database. I cannot re-structure it, as I am not allowed any site downtime. (it takes thousands of dollars of sales per day)

    Anyway. I have 2 linking tables.

    Tbl_orders contains everything about orders

    Tbl_shipping contains information about orders that have Shipped.

    The problem is, this bonehead stored dateCanceled dateCompleted dateReturned and dateShipped in tbl_shipping.

    So if an order hasn't been shipped.. he adds it to shipping table, then cancels it.


    I need to pull all orders (shipped and not shipped) that aren't canceled or returned.

    I need everything from tbl_orders unless it's id exists in tbl_shipping AND dateCanceled is not null AND dateReturned is not null.

    THis is what i'm trying:
    select * from tbl_orders O left join tbl_shipping S on O.orderID = S.orderID WHERE S.dateDeleted is NULL AND S.dateReturned is NULL

    but it's not giving any unshipped orders. (the ones that exist in tbl_orders but aren't referenced in tbl_shipping)

    Any ideas?
    Thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by HockeyGod
    I need everything from tbl_orders unless it's id exists in tbl_shipping AND dateCanceled is not null AND dateReturned is not null.
    Code:
    select O.* 
      from tbl_orders O 
    left outer
      join tbl_shipping S 
        on O.orderID = S.orderID 
       and S.dateDeleted is NULL 
       and S.dateReturned is NULL
     where S.orderID is null
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Feb 2005
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's not working right.

    I want everything from tbl 0 unless it's orderID is marked as dateCanceled or dateDeleted in tbl_S

    a setup like this is possible however:

    tbl_orders
    ____________________
    1 name order data
    2 name order data
    3 name order data
    4 name order data

    tbl_shipping
    ______________________
    2 name dateCanceled is not null
    3 name dateCanceled, dateDeleted are null
    4 name dateCanceled, dateDeleted are null

    The query should return orders 1, 3 and 4.

    The one above can't be right. There are over 500 customers in the database, and it's returning 94 as the customer count. I think it's JUST giving me the orders similiar to order 1

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by HockeyGod
    That's not working right.
    you're right, i made a small error
    Code:
    select O.* 
      from tbl_orders O 
    left outer
      join tbl_shipping S 
        on O.id = S.id
       and S.dateCancelled is not null
       and S.dateDeleted is not null 
     where S.id is null
    Quote Originally Posted by HockeyGod
    I want everything from tbl 0 unless it's orderID is marked as dateCanceled or dateDeleted in tbl_S
    that's funny, in your first post you said AND, not OR

    okay, change it to this --
    Code:
    select O.* 
      from tbl_orders O 
    left outer
      join tbl_shipping S 
        on O.id = S.id
       and ( S.dateCancelled is not null
          or S.dateDeleted is not null
           )
     where S.id is null
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Feb 2005
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks man. Sad part is, i'm still not sure if that works or not. In testing that I found that this database is so screwed up it's not even funny.

    They built an automatic shipping admin that updates it, but the company also is manually updating shipping, and they both hit different tables with different values... so I have no consistency. I have 1 table saying this order has shipped, and another saying it's open..

    Worse, the shipping table doesn't tell me what parts of the order shipped, just that part of it has shipped.

    I want to scream.

    I see why the guy i replaced was fired now. I need to completely re-structure this database, but the site is taking hundreds of orders a day, and our contract says we pay them $150 for every hour the site is down.

    I see no happy ending here.

    Thanks though.


Bookmarks

Posting Permissions

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