Multi-Query SQL Condensed to One Result

Question

There's information that my web-app needs from my database that I can only generate for the entire database with many queries populating a brand new table. Can anyone suggest to me a single query that returns the results the exact same as this populated table?

First, here are the two tables that have the information.

CREATE OR REPLACE TABLE AS_Event(
    AS_Id INT,
    geneAbsentId VARCHAR(15),
    genePresentId VARCHAR(15),
)ENGINE=INNODB;

CREATE OR REPLACE TABLE AS_ChoiceSupport(
    AS_Id INT,
    GeneId VARCHAR(15),
    ESTId VARCHAR(20),
    LibraryId INT,
)ENGINE=INNODB;

I've simplified this to just the information we need. geneAbsentId and genePresentId (or AS_Choice) point to the same table as GeneId in AS_ChoiceSupport. Each AS_Choice in every event can have >= 0 AS_ChoiceSupport row.

Now, here is the new table I'm creating but I need it to instead be a single query.

CREATE TABLE AS_Support_Count(
    AS_Id   INT,
    gaCount INT,
    gpCount  INT,
    PRIMARY KEY(AS_Id),
    FOREIGN KEY(AS_Id) REFERENCES AS_Event(AS_Id) ON UPDATE CASCADE ON DELETE CASCADE
)ENGINE=INNODB;

INSERT INTO AS_Support_Count(AS_Id) SELECT AS_Id FROM AS_Event

CREATE OR REPLACE VIEW TEMP AS SELECT e.AS_Id, COUNT(e.genePresentId) AS gpCount FROM AS_Event e, AS_ChoiceSupport c WHERE (e.AS_Id = c.AS_Id) AND e.genePresentId = c.GeneId GROUP BY e.AS_Id

UPDATE AS_Support_Count a, TEMP t
SET a.gpCount = t.gpCount
WHERE a.AS_Id = t.AS_Id

UPDATE AS_Support_Count
SET gpCount = 0
WHERE gpCount IS NULL

CREATE OR REPLACE VIEW TEMP AS SELECT e.AS_Id, COUNT(e.geneAbsentId) AS gaCount FROM AS_Event e, AS_ChoiceSupport c WHERE (e.AS_Id = c.AS_Id) AND e.geneAbsentId = c.GeneId GROUP BY e.AS_Id

UPDATE AS_Support_Count a, TEMP t
SET a.gaCount = t.gaCount
WHERE a.AS_Id = t.AS_Id

UPDATE AS_Support_Count
SET gaCount = 0
WHERE gaCount IS NULL

The reason I need this condensed to a single query is that I need to sometimes get information for the events based on the library id. i.e. I need to know for AS_Event x how many AS_ChoiceSupports there are from LibraryId y.

I know this is a huge problem so I'll transfer $5 on PokerStars to whoever gives me the correct answer.

Answer 1

I discovered how to transpose data from an online tutorial link text

And here's the query I came up with

SELECT
    COUNT(CASE WHEN e.genePresentId = c.GeneId THEN c.LibraryId ELSE NULL END) AS 'gpCount',
    COUNT(CASE WHEN e.geneAbsentId = c.GeneId THEN c.LibraryId ELSE NULL END) AS 'gaCount'
FROM
    AS_Event e, AS_ChoiceSupport c
WHERE e.AS_Id = c.AS_Id AND c.LibraryId = ? <sql:param value="${LibraryId}"/>
GROUP BY
    c.AS_Id
ORDER BY
    e.AS_Id

Deals off on the PokerStars award. However, if you have a better answer, please provide it for the community.

Answer 2

does this yield the same results as select * from AS_Support_Count?

SELECT e.AS_Id
     , COUNT(e.geneAbsentId) as gaGount
     , COUNT(e.GenePresentId) as gpCount
  FROM AS_Event e
  JOIN AS_ChoiceSupport c
    ON c.AS_Id = e.AS_Id
   AND c.GeneId IN (e.geneAbesntId, e.genePresentId)
GROUP
    BY e.AS_Id