CODEWITHSUNDEEP
sqlsqliteself-join
Crossman
Crossman

Reputation: 875

Sqlite self join using alias

I have such sql query:

SELECT LeftCurrency.LeftCurrency, RightCurrency.RightCurrency FROM 
(
  SELECT DISTINCT [SecurityData].[Value] AS 'LeftCurrency'
  FROM [SecurityData]
  JOIN [Fields] ON [Fields].[Id] = [SecurityData].[FieldId]
  WHERE [Fields].[Mnemonic] = 'CRNCY'
) AS LeftCurrency
JOIN 
(
  SELECT DISTINCT [SecurityData].[Value] AS 'RightCurrency'
  FROM [SecurityData]
  JOIN [Fields] ON [Fields].[Id] = [SecurityData].[FieldId]
  WHERE [Fields].[Mnemonic] = 'CRNCY'
) AS RightCurrency
ON LeftCurrency.LeftCurrency != RightCurrency.RightCurrency

it works ok, but I have two similar sub-queries.

Also I tried something like this:

SELECT * FROM 
(
  SELECT DISTINCT [SecurityData].[Value] AS 'Currency'
  FROM [SecurityData]
  JOIN [Fields] ON [Fields].[Id] = [SecurityData].[FieldId]
  WHERE [Fields].[Mnemonic] = 'CRNCY'
) AS leftCurrency, leftCurrency AS rightCurrency
WHERE leftCurrency.Currency != rightCurrency.Currency

But it doesn't work. So is it possible to get rid of sub-query duplication?

Upvotes: 0

Views: 848

Answers (1)

CL.
CL.

Reputation: 180080

In SQLite 3.8.3 or later, you could use a common table expression:

WITH Currency(Currency) AS (
    SELECT DISTINCT [SecurityData].[Value]
    FROM [SecurityData]
    JOIN [Fields] ON [Fields].[Id] = [SecurityData].[FieldId]
    WHERE [Fields].[Mnemonic] = 'CRNCY'
)
SELECT LeftCurrency.Currency AS LeftCurrency,
       RightCurrency.Currency AS RightCurrency
FROM Currency AS LeftCurrency
JOIN Currency AS RightCurrency ON LeftCurrency.Currency != RightCurrency.Currency

Alternatively, use a temporary view.

Upvotes: 1

Related Questions