MySQL SQL Subquery?

Question

Given the following schema / data / output how would I format a SQL query to give the resulting output?

CREATE TABLE report (
id BIGINT AUTO_INCREMENT,
name VARCHAR(255) NOT NULL UNIQUE,
source VARCHAR(255) NOT NULL UNIQUE,
PRIMARY KEY(id)
) ENGINE = INNODB;

CREATE TABLE field (
id BIGINT AUTO_INCREMENT,
name VARCHAR(255) NOT NULL UNIQUE,
report_id BIGINT,
PRIMARY KEY(id)
) ENGINE = INNODB;

ALTER TABLE filed ADD FOREIGN KEY (report_id) REFERENCES report(id) ON DELETE CASCADE;

reports:
id, name, source
1 report1 source1
2 report2 source2
3 report3 source3
4 report4 source4

field:
id, name, report_id
1 firstname 3
2 lastname 3
3 age 3
4 state 4
5 age 4
6 rank 4

Expected output for search term "age rank"
report_id, report_name, num_fields_matched
3 report3 1
4 report4 2

Thanks in advance!

Btw, how can you put two "Age" values in the table "Field" when the column "name" is UNIQUE? — Lukasz Lysik
That was my mistake. I got ahead of myself. Upon testing your code I had to edit the schema to remove a couple of unique statements. Thanks again. — John

Lukasz Lysik Lukasz Lysik · Accepted Answer · 2009-09-10T16:09:22

This query will return all the reports with words you need.

SELECT *
FROM report r
INNER JOIN field f ON r.id = f.report_id
WHERE name IN ('age','rank')

You have to nest it. So the final query is:

SELECT a.id, a.name, COUNT(*) 
FROM 
(
    SELECT r.id, r.name
    FROM report r
    INNER JOIN field f ON r.id = f.report_id
    WHERE f.name
    IN ('age', 'rank')
)a
GROUP BY a.id, a.name

MySQL SQL Subquery?

1 Answers