I have two tables, quotation
and comparitive
.
Table quotation
has fields: tender_id, supplier_name
Table comparitive
has fields: tender_id, sup_name,make,shelf_life,datasheet,coc
Now what I want is I need a query which joins these two tables and show records where quotation.tender_id=comparitive.tender_id and comparitive.tender_id=$tender_id and comparitive.sup_name IN quotation.supplier_name
.
How can I achieve that? I have tried different ways but desired output is not coming.
This is what I have tried.
SELECT comparitive_statement1.sup_name
, comparitive_statement1.tender_id
, comparitive_statement1.coc
, comparitive_statement1.shelf_life
, comparitive_statement1.make
, comparitive_statement1.datasheet
, quotation_items.supplier_name
, quotation_items.tender_id
FROM comparitive_statement1
, quotation_items
WHERE comparitive_statement1.tender_id = quotation_items.tender_id
AND quotation_items.tender_id='$tender_id'
and quotation_items.supplier_name = comparitive_statement1.sup_name
group by quotation_items.supplier_name