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

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.

CodePudding user response：

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.

CodePudding user response：

I'm not sure what your threshold is for "simple" or why the external program is sensitive about that, but this is about the best compromise between simplicity and efficiency:

SELECT ID, Article
  FROM dbo.YourTableName AS t
  WHERE EXISTS
  (
    SELECT 1 FROM dbo.YourTableName AS i
      WHERE i.ID = t.ID
      AND i.Article = 'Fanta'
  );

Working example in this fiddle.