mySQL Nested Query Syntax

Question

I am trying to use a nested query approach to build a query-on-query for my mySQL database and failing to correctly generate output. I am able to import my table into Microsoft Access and build Query1 and then build Query2 on Query1 to get the correct output I'm looking for so I feel like I'm close, I just can't get the right syntax to get the output I'm looking for using a mySQL query approach.

Query1, here is the SQL statement from Access for Query1.

SELECT DISTINCT MediaBuys.DistrictID, MediaBuys.SpenderID, MediaBuys.PG, MediaBuys.SupportType, MediaBuys.PriSupportType 
FROM MediaBuys 
WHERE MediaBuys.PG ="P";

Query2, if I have built Query1 in Access as above and I run this SQL statement in Access as a separate query built on the first I can generate the output I'm looking for.

SELECT Query1.DistrictID, Query1.SpenderID, Query1.PG, Query1.SupportType, Query1.PriSupportType, Count(Query1.SupportType) AS CountOfSupportType 
FROM Query1 INNER JOIN Query1 AS Query1_1 ON Query1.PG = Query1_1.PG AND Query1.SpenderID = Query1_1.SpenderID AND Query1.DistrictID = Query1_1.DistrictID 
GROUP BY Query1.DistrictID, Query1.SpenderID, Query1.PG, Query1.SupportType, Query1.PriSupportType 
HAVING Count(Query1.SupportType) > 1;

I'd like to be able to produce the same output from a query in mySQL. Since I have the SQL statements of these two queries I feel like this should be doable, I've attempted to build a nested query in a number of different ways and each attempt fails, it seems I can't put together the correct syntax. The most common error I receive is "Error Code: 1146. Table 'Query1' doesn't exist".

Is this doable in mySQL and if so can anyone help me with the correct syntax?

See meta.stackoverflow.com/questions/333952/…

Strawberry
– Strawberry

2020-06-02 19:46:29 +00:00
Commented Jun 2, 2020 at 19:46 — Strawberry
– Strawberry, Commented Jun 2, 2020 at 19:46

forpas · Accepted Answer · 2020-06-02 19:50:52Z

1

Just like you created the query Query1 in Access, create a view View1 in MySql:

CREATE VIEW View1 AS
SELECT DISTINCT DistrictID, SpenderID, PG, SupportType, PriSupportType 
FROM MediaBuys 
WHERE PG ='P';

and your query will be:

SELECT 
  View1.DistrictID, View1.SpenderID, View1.PG, View1.SupportType, View1.PriSupportType,
  Count(View1.SupportType) AS CountOfSupportType 
FROM View1 INNER JOIN View1 AS View1_1 
ON View1.PG = View1_1.PG AND View1.SpenderID = View1_1.SpenderID 
AND View1.DistrictID = View1_1.DistrictID 
GROUP BY View1.DistrictID, View1.SpenderID, View1.PG, View1.SupportType, View1.PriSupportType 
HAVING Count(View1.SupportType) > 1;

answered Jun 2, 2020 at 19:50

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Scott Kennedy Over a year ago

This is exactly what I was looking for, thank you so much for taking the time to help me with my issue.

Collectives™ on Stack Overflow

mySQL Nested Query Syntax

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related