SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Hybrid View

  1. #1
    Non-Member demosfen's Avatar
    Join Date
    Nov 2003
    Location
    This is not a legal advice ---------->
    Posts
    623
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    need help with a query

    Can anybody help with this query? It should be like this -

    SELECT * FROM book WHERE supplierID=2 AND quantityInStock>0 ,

    but the record should also be included in the result set if the table has a record with the same isbn and supplierID=9. How do I go about this?

    (Note to self: next time when designing a database from scratch, think really good, so that you won't have to build lame queries 4 years later.)

  2. #2
    SitePoint Addict
    Join Date
    Sep 2004
    Location
    Kelowna, BC
    Posts
    202
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    is this what you mean?

    SELECT * FROM book WHERE (supplierID=2 OR supplerID=9) AND quantityInStock>0

  3. #3
    Non-Member demosfen's Avatar
    Join Date
    Nov 2003
    Location
    This is not a legal advice ---------->
    Posts
    623
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Not quite, I don't need records with supplierID=9 selected. I need all records with supplierID=2 where either quantityInStock>0 , or isbn equals isbn of another record in the same table where quantityInStock>0 and supplierID=9.
    Say, if I have 2 records:

    isbn supplierID quantityInStock
    1888722010 2 0
    1888722010 9 5

    I would need the first record selected even though quantity=0 (don't need the second record)

  4. #4
    SitePoint Enthusiast
    Join Date
    Feb 2002
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Not sure exactly what you are after but I will give an example that may or may not help you solve it:
    Code:
     SELECT * 
         FROM book as b1, book as b2
         WHERE b1.isbn=b2.isbn
           and (b1.InStock>0 or b2.InStock>0)
           and (b1.supplierID=2 or b2.supplerID=9;
    ....sort of thing, you may want to play around with the join type

  5. #5
    Non-Member demosfen's Avatar
    Join Date
    Nov 2003
    Location
    This is not a legal advice ---------->
    Posts
    623
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks a bunch for the help, I will be able to figure out what I need from your sample.


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
  •