Help Regarding The Search Querry

[aseem pareek]

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

[r937]

your query is pretty difficult to read

let me first rewrite it for you --

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

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?