Joining two queries into one query or making a sub-query
I am having some trouble with the following queries originally done for some Access forms:
SELECT qry1.TCKYEAR AS Yr, COUNT(qry1.SID) AS STUDID, qry1.SID AS MID, table_tckt.tckt_tick_no
FROM table_tckt INNER JOIN qry1 ON table_tckt.tckt_S开发者_如何学PythonID = qry1.SID
GROUP BY qry1.TCKYEAR, qry1.SID, table_tckt.tckt_tick_no
HAVING (((table_tckt.tick_no)=[forms]![frmNAME]![cboNAME]));
SELECT table_tckt.sid, FORMAT([tckt_iss_date], 'yyyy') AS TCKYEAR, table_tckt.tckt_tick_no, table_tckt.licstate
FROM table_tckt
WHERE (((table_tckt.licstate)<>"NA"));
I am no longer working with Access, but JSP for the forms. I need to somehow either combine these two queries into one query or find another way to have a query 'query' another one.
The following would be the quickest way to do it in SQL Server. I don't know what you are using as a backend but maybe it will point you in the right direction.
It basically uses the statement within the with {} to create an in memory table named sql1 that you can query.
with
{
SELECT table_tckt.sid,
FORMAT([tckt_iss_date], 'yyyy') AS TCKYEAR,
table_tckt.tckt_tick_no,
table_tckt.licstate
FROM table_tckt
WHERE (((table_tckt.licstate)<>"NA"));
} as sql1
SELECT qry1.TCKYEAR AS Yr,
COUNT(qry1.SID) AS STUDID,
qry1.SID AS MID,
table_tckt.tckt_tick_no
FROM table_tckt
INNER JOIN qry1 ON table_tckt.tckt_SID = qry1.SID
GROUP BY qry1.TCKYEAR, qry1.SID, table_tckt.tckt_tick_no
HAVING (((table_tckt.tick_no)=@YourVariable));
精彩评论