selecting columns based on queries from three tables

[sneha24]

table item has
item_name
.....

table bill has
bill_id
qty
.....

table bill_id has
date
.....

now my query is
"select p.item_name,c.qty,c.bill_id
from bill as c
JOIN items as p ON p.id=c.id
where p.category_name='$q'";
i also want to add another condition in where clause so tat it also checks date form bill_id table

how do i do it

[guido2004]

What is the relationship between the bill_id table and the other two?

[sneha24]

bill will have a column bill_id which will have the same id(primary key) as tat of bill_id

[guido2004]

SELECT
    p.item_name
  , c.qty
  , c.bill_id
FROM bill AS c
INNER JOIN items AS p 
ON p.id = c.id
INNER JOIN bill_id AS bi
ON bi.bill_id = c.bill_id
WHERE p.category_name='$q'
AND bi.billdate = ....

Calling a column 'date' might result in errors, since it is a reserved word in MySQL. Unless you don't mind putting it in backticks each time you use it.

[sneha24]

thanks
it worked gr8