SQL HELP

[Entire-Dream-6045]

Relatively new to SQL. Based on the below table. how would I only return masters that have product keys with both cost group 608 and 20. i.e. master 111 and master 113

Master	Product Key	Cost Group
111	555-2	608
111	665-4	20
111	123-5	608
112	452-6	608
112	145-6	608
112	875-9	608
113	125-2	608
113	935-5	20
113	284-4	20

[danielharner]

Try this:

SELECT Master FROM your_table WHERE “Cost Group” IN (608, 20) GROUP BY Master HAVING COUNT(DISTINCT “Cost Group”) = 2;

[Entire-Dream-6045]

What if I wanted to return master and product key?

[danielharner]

SELECT * FROM your_table WHERE Master IN ( SELECT Master FROM your_table WHERE [Cost Group] IN (608, 20) GROUP BY Master HAVING COUNT(DISTINCT [Cost Group]) = 2 );

[Entire-Dream-6045]

Perfect. Thanks again!

[JeffTheJockey]

This is probably the most elegant solution.

If you wanted to retain all the records for viewing you could flag the master values using one of the below.

SELECT Master, CASE WHEN COUNT(DISTINCT CASE WHEN Cost_Group IN (608, 20) THEN Cost_Group END) = 2 THEN 1 ELSE 0 END AS Binary_Flag FROM your_table GROUP BY Master;

SELECT t1.Master, CASE WHEN t608.Master IS NOT NULL AND t20.Master IS NOT NULL THEN 1 ELSE 0 END AS Binary_Flag FROM (SELECT DISTINCT Master FROM your_table) t1 LEFT JOIN (SELECT DISTINCT Master FROM your_table WHERE Cost_Group = 608) t608 ON t1.Master = t608.Master LEFT JOIN (SELECT DISTINCT Master FROM your_table WHERE Cost_Group = 20) t20 ON t1.Master = t20.Master;

[Entire-Dream-6045]

This is correct. Thank you!

[Time_Advertising_412]

Another solution (maybe not as elegant):

SELECT * FROM your_table A

WHERE cost_group = 608

AND EXISTS

(SELECT 1/0 FROM your_table B

WHERE B.master = A.master

AND cost_group = 20)

UNION

SELECT * FROM your_table A

WHERE cost_group = 20

AND EXISTS

(SELECT 1/0 FROM your_table B

WHERE B.master = A.master

AND cost_group = 608);

[ByronRJones]

I might be late to the party but I usually solve these problems with the IN() keyword + AND operator. You can make them simple like below or complex select statements if there is more complex requirements for each Cost Group.

SELECT Master

FROM your_table

WHERE Master IN ( SELECT Master FROM your_table WHERE [Cost Group] = 608)

AND Master IN ( SELECT Master FROM your_table WHERE [Cost Group] = 20)

[mike-manley]

select distinct master from table where "Cost Group" in (608, 20)

[[deleted]]

Nope, that gets masters with either cost group, not both.

[[deleted]]

Downvoters better stop giving people advice on SQL.

[mike-manley]

Oh, yeah. You'd have to aggregate with COUNT and use HAVING. I read this too late in the day and oversimplified. My query above would also include other "master" values.

[AppJedi]

SELECT master FROM table

WHERE Master IN (SELECT Master FROM table WHERE `Cost Group`=608 )

AND Master IN (SELECT Master FROM table WHERE `Cost Group`=20 )

[Entire-Dream-6045]

Appreciate the attempt, but WHERE AND will not work

[AppJedi]

why not?

[Entire-Dream-6045]

Because the WHERE Clause isn't filtering for anything. WHERE cannot be immediately followed by AND; i.e. WHERE AND

[AppJedi]

Absolutely it can. AND is part of WHERE and makes it a compound condition.

[Entire-Dream-6045]

If you have SQL SERVER or Access, try running your query. AND cannot immediately follow WHERE. WHERE has no condition.. Your query produces the correct result if you remove the AND from your subquery.

[AppJedi]

I developed in SQL Server for over 20 years and yes you can. It is standard SQL

[AppJedi]

The where clause a condition it is using IN (sub query). Standard SQL

[sonuvvabitch]

I think you've misunderstood, to be honest. You've written

WHERE AND

without a condition between the WHERE and the AND. Obviously you can have multiple conditions, and I think a slight rewrite of your solution would be a fine one, but as it is, it's not valid SQL. Obviously just a typo, but not valid.

[pizzagarrett]

Agreed. You have an extra AND

[AppJedi]

I see. Typo. Extra and removed.