SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    Jan 2010
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help Regarding The Search Querry

    HI,
    all members...

    I am creating a book shopping website search result page ,and am having a strange problem with my querry.

    i ll summarize the tables first i have three tables as
    M_CMN_ENG_BOOK
    (
    ISBN, primary key
    BOOK_TITLE,
    BOOK_SUBTITLE,
    AUTHOR_NAME,
    DESCRIPTION,
    COMMON_BOOK_CATEGORY_CODE,
    PUBLISHER_COMPANY_NAME,
    PUBLICATION_DATE
    )

    M_STORE_STOCK
    (
    AREA_CODE,
    ISBN,
    BOOK_CATEGORY_CODE,
    SUPPLIER_CODE,
    CURRENCY_CODE,
    PRICE_WITH_TAX,
    QTY

    )

    M_SUPPLIER_STOCK
    (
    AREA_CODE,
    ISBN,
    BOOK_CATEGORY_CODE,
    SUPPLIER_CODE,
    CURRENCY_CODE,
    PRICE_WITH_TAX,
    QTY

    )

    now i am using this querry to search the books
    SELECT
    DISTINCT B.ISBN,B.BOOK_TITLE,S.PRICE_WITH_TAX,B.AUTHOR_NAME,B.PUBLISHER_COMPANY_NAME,B.PUBLICATION_DATE,IF(S.QTY>0||K.QTY>0,'AVAILABLE','NOT AVAILABLE')AS 'STOCK'
    FROM
    M_CMN_ENG_BOOK B LEFT JOIN M_STORE_STOCK S ON B.ISBN=S.ISBN
    LEFT JOIN M_SUPPLIER_STOCK K ON B.ISBN=K.ISBN
    WHERE
    B.BOOK_TITLE LIKE '%$mart%' || B.AUTHOR_NAME LIKE '%$mart%' || B.PUBLISHER_COMPANY_NAME LIKE '%$mart%' || B.ISBN LIKE '$mart'

    GROUP BY B.ISBN,B.BOOK_TITLE,B.AUTHOR_NAME,B.PUBLISHER_COMPANY_NAME,B.PUBLICATION_DATE


    ($mart is the search criteria which user inputs)

    (if i execute the querry withouth any search criteria i get all the books from the book master table M_CMN_ENG_BOOK )

    but if i give a book title whose isbn is present in the
    M_CMN_ENG_BOOK
    but not in other two tables(i am joinning the three with left join)
    there is no result to be displayed
    i find it strange because i am using left join and thus i have to get all the data from the M_CMN_ENG_BOOK irrespective of wheter the isbn is matching with other two tables isbn.

    hope my post is clear i can specify any further details if you want.
    please do help me

    thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    your query is pretty difficult to read

    let me first rewrite it for you --
    Code:
    SELECT DISTINCT 
           b.isbn
         , b.book_title
         , s.price_with_tax
         , b.author_name
         , b.publisher_company_name
         , b.publication_date
         , CASE WHEN s.qty>0
                  OR k.qty>0
                THEN 'available'
                ELSE 'not available' END  AS 'stock' 
      FROM m_cmn_eng_book b 
    LEFT OUTER
      JOIN m_store_stock s 
        ON s.isbn = b.isbn
    LEFT OUTER
      JOIN m_supplier_stock k 
        ON k.isbn = b.isbn 
     WHERE b.book_title like '%$mart%' 
        OR b.author_name like '%$mart%' 
        OR b.publisher_company_name like '%$mart%' 
        OR b.isbn like '$mart' 
    GROUP 
        BY b.isbn
         , b.book_title
         , b.author_name
         , b.publisher_company_name
         , b.publication_date
    there is a problem mixing GROUP BY and DISTINCT, they are redundant, so let's just go with GROUP BY

    meanwhile, you have s.price_with_tax in the SELECT clause so that might be an issue if different stores have different prices, but we can resolve this using MIN(s.price_with_tax)

    also, since you are using GROUP BY, you have the individual values s.qty and k.qty in the SELECT clause, but here there is an easy solution, you just want to know if there are any books in stock, so we can use SUM
    Code:
    SELECT b.isbn
         , b.book_title
         , MIN(s.price_with_tax) AS lowest_price
         , b.author_name
         , b.publisher_company_name
         , b.publication_date
         , CASE WHEN SUM(s.qty) > 0
                  OR SUM(k.qty) > 0
                THEN 'available'
                ELSE 'not available' END  AS 'stock' 
      FROM m_cmn_eng_book b 
    LEFT OUTER
      JOIN m_store_stock s 
        ON s.isbn = b.isbn
    LEFT OUTER
      JOIN m_supplier_stock k 
        ON k.isbn = b.isbn 
     WHERE b.book_title like '%$mart%' 
        OR b.author_name like '%$mart%' 
        OR b.publisher_company_name like '%$mart%' 
        OR b.isbn like '$mart' 
    GROUP 
        BY b.isbn
         , b.book_title
         , b.author_name
         , b.publisher_company_name
         , b.publication_date
    make sense?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •