How to create a query to list all combinations possibilities

Question

Hi

I have a currency(Rec_ID, currency) table which is having M-M relationship with it self it results in CurrencyExchangeRate table (Rec_ID, Currency_FK1 AS 'FROM', Currency_FK2 AS 'TO, Exchange_Rate)

Now I need a query to do all combination of currency rec_id (repetition is not allowed), along with the exchange rate or null. if there is 3 records in Currency then I the query should return 6 rows.

Thanks

Answer 1

If you want all combinations (even those that do not have a rate defined) then join it with itself first and then with the rates

SELECT 
  c1.currency AS [From], c2.currency AS [To] , cer.Exchange_Rate
FROM
  currency c1 JOIN currency c2 ON c1.rec_id <> c2.rec_id
  LEFT OUTER JOIN CurrencyExchangeRate cer ON c1.rec_id = cer.[from] 
         AND c2.rec_id = cer.[to]

Answer 2

SELECT
   CCY1, CCY2, CER.Exchange_Rate
FROM
    ( 
    SELECT
        c1.rec_id AS rec_id1, c1.currency AS CCY1,
        c2.rec_id AS rec_id2, c2.currency AS CCY2
    FROM
        currency c1
        CROSS JOIN --all combinations...
        currency c2
    WHERE
        c1.rec_id <> c2rec_id -- ...removes same pairs
    ) foo
    LEFT JOIN -- ...get matching FX pairs
    CurrencyExchangeRate CER ON foo.rec_id1 = cer.[from] AND foo.rec_id2 = cer.[to]