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