Correct syntax for finding all rows with same ID when a certain cell contains a value

Question

I've got to tables merged in SQL. Now i want the output to just show me all rows with the same ID's when a cell contains a certain text. This probably is peanuts, but I just can't seem to find the correct syntax using SQL Express 2013.

Example :

ID	ARTICLE
1	Coke
1	Coke Light
2	Coke
2	Coke Light
2	Fanta

The result should only show all the ID rows where one of them contains a Fanta. So in this case :

ID	ARTICLE
2	Coke
2	Coke Light
2	Fanta

The example is a simplified version of the original table. And the query needs to be kept as simple as possible, due to limitations of the external program that needs to run the query.

Answer 1

You may try the following join query:

SELECT T.ID, T.ARTICLE
FROM table_name T
JOIN
  (SELECT ID FROM table_name WHERE ARTICLE ='Fanta') D
ON T.ID=D.ID

If your sample input date is populated from another query, you may use CTE as the following:

WITH CTE AS
(your query)
SELECT T.ID, T.ARTICLE
FROM CTE T
JOIN
 (SELECT ID FROM CTE WHERE ARTICLE ='Fanta') D
ON T.ID=D.ID

See a demo.