You have to use LFET JOIN for taible that may nor have records for all suppliers.
SELECT rfq.*, ;
rfqdetail.rqd_desc, rqd_Linenum, rqd_needby, rqd_reqnum, rqd_qty,;
quotes.quo_freight, quo_portother, quo_date, quo_daysvalid,;
quotedetails.qud_delivery, qud_unitcost, ;
Suppliers.sup_name;
FROM rfq ;
INNER JOIN rfqdetail ON rfq_pk = rqd_rfqfk;
INNER JOIN Suppliers ON quo_suppfk = sup_pk;
LEFT JOIN quotes ON rfq_pk = quo_rfqfk;
LEFT JOIN quotedetails ON rqd_pk = qud_rqdfk AND quo_pk = qud_quotefk;
ORDER BY sup_Name, rqd_LineNum
>
>I'm working on a system where a Request For Quotation (RFQ) with many Line Items (RFQDetail) is created. The RFQ is sent to multiple Suppliers (stored in RFQSuppliers). The Suppliers then send back a Quotation (Quote) with all the line items from the RFQDetail (Stored in (QuoteDetail).
>
>I am trying to build up a cursor with a row for each Supplier and RFQDetail Line Item and the Supplier's quote, whether or not a supplier sent in a Quotation.
>
>The SQL that brings out the data as I need it (except for if a Supplier has not quoted) is this:
>
>
>SELECT rfq.*, ;
> rfqdetail.rqd_desc, rqd_Linenum, rqd_needby, rqd_reqnum, rqd_qty,;
> quotes.quo_freight, quo_portother, quo_date, quo_daysvalid,;
> quotedetails.qud_delivery, qud_unitcost, ;
> Suppliers.sup_name;
> FROM rfq ;
> INNER JOIN rfqdetail ON rfq_pk = rqd_rfqfk;
> INNER JOIN quotes ON rfq_pk = quo_rfqfk;
> INNER JOIN quotedetails ON rqd_pk = qud_rqdfk AND quo_pk = qud_quotefk;
> INNER JOIN Suppliers ON quo_suppfk = sup_pk;
> ORDER BY sup_Name, rqd_LineNum
>
>
>The table that links suppliers to RFQs is RFQSuppliers with the following fields:
>rqs_rfqFK (Foreign Key to RFQ)
>rqs_SuppFK (Foreign Key to Suppliers)
>
>Can anyone help me with the syntax to include a record for each line item on the RFQ for each supplier, whether or not they have quoted?
>
>If needed I can knock up a program with some sample data to illustrate the problem.
>
>Thanks,
--sb--