CTE: Union of two tables in which one of them has only one field in common

Asked

Viewed 37 times

0

I need to do the following using a CTE.Imagining two tables with columns:

Articles: Reference, Quantity, Price
Associated References: Reference, Referral

I intend that if there is an associated reference for a reference of the Articles table, that returns the information of the existing associated reference in the Articles table.

Example:
Articles:
Refa, 10, 7€
Refb, 5, 4€
Refc, 2, 10€

Associated References:
Refa, Refc

Outcome will have to be:
Refa, 10, 7€
Refc, 2, 10€
Refb, 5, 4€
Refc, 2, 10€

sql sql-server cte

1 answer

0

A select with Join, I believe is enough:

    select Artigos.Referência,
   Artigos.Quantidade,
   Artigos.Preço,
   [Referências Associadas].RefAssociada from Artigos left join [Referências Associadas] on Artigos.Referência = [Referências Associadas].Referência

  • It is not a solution, as an article may have several references associated with it. And when there are associated references a line should be added with the information of this reference.

  • Post the table columns .

Browser other questions tagged sql sql-server cte

You are not signed in. Login or sign up in order to post.