Select within Select ? If that makes sence

Carline · October 25, 2011, 12:19pm

Hi,

As you guessed I’m new to PHP and I’m looking for some guidance on an SELECT command.

I have two tables (table1 = full names, DoB etc) and (table2 = full names, swim times, improvements, Medals etc). I would like to be able to run an SELECT command which will list all the people in Table1 and display columns next to them with data extracted from Table2.

i.e. t1.First Name, t1.Surname, t1.Aged, t2.PBs, t2.Medals

I have used the following SELECT to create my list from Table1

SELECT
t1.ChristianName AS "First Name",
t1.Surname AS Surname,
YEAR(DATE_SUB(NOW(), INTERVAL TO_DAYS(t1.DateofBirth) DAY)) AS Aged
FROM table1 t1
ORDER BY t1.Gender, t1.DateofBirth Desc, t1.Surname

And I have the following separate SELECT which gives me the results from Table2 which I need.

SELECT COUNT(*) FROM table2 t2 WHERE t2.Date BETWEEN "2011-09-01" and "2012-08-31" AND t2.Improvement >0 AS PBs;

AND

SELECT COUNT(*) FROM table2 t2 WHERE t2.Position BETWEEN "2011-09-01" and "2012-08-31" BETWEEN "1" and "3" AS Medals;

What I would like to be able to do is list the t2.improvement COUNT() and the t2.psotition COUNT() against each Line in Table1 which are for that particular person so the result would like similar to the below.

[table]
[tr]
[td]First Name[/td]
[td]Surname[/td]
[td]Aged[/td]
[td]PBs[/td]
[td]Medals[/td]
[/tr]
[tr]
[td]Clare[/td]
[td]Owen[/td]
[td]12[/td]
[td]32[/td]
[td]16[/td]
[/tr]
[tr]
[td]Joe[/td]
[td]Bloggs[/td]
[td]17[/td]
[td]15[/td]
[td]3[/td]
[/tr]
[tr]
[td]Bill[/td]
[td]Gates[/td]
[td]18[/td]
[td]10[/td]
[td]1[/td]
[/tr]
[/table]

Can this be done in a SELECT query?

I have a common link between each table which is [tt]T*.swim_id[/tt] if that helps!

I’ve tried UNIONS and JOINS but I’m getting mixed up with these and instead of the table listing the results for the individual person, it just lists the full results against everyone name! (see below)

[table]
[tr]
[td]First Name[/td]
[td]Surname[/td]
[td]Aged[/td]
[td]PBs[/td]
[td]Medals[/td]
[/tr]
[tr]
[td]Clare[/td]
[td]Owen[/td]
[td]12[/td]
[td]57[/td]
[td]20[/td]
[/tr]
[tr]
[td]Joe[/td]
[td]Bloggs[/td]
[td]17[/td]
[td]57[/td]
[td]20[/td]
[/tr]
[tr]
[td]Bill[/td]
[td]Gates[/td]
[td]18[/td]
[td]57[/td]
[td]20[/td]
[/tr]
[/table]

Any and all help or guidance greatly appreciated.

stlewis · October 26, 2011, 1:34am

What you’re likely looking for is an inner join of some sort:

SELECT t1.first_name, t1.last_name, t1.aged, t2.pbs, t2.medals FROM t1, t2 WHERE t1.swim_id = t2.swim_id;

Carline · October 27, 2011, 8:45am

Hi,

Thanks for your advice. I managed to create what I wanted in the end by using the CASE command. Below is the SQL I ended up with if anyone else has a similar issue.

SELECT
t1.FullName AS "Fullname",
(SELECT CASE t2.Gender WHEN 'F' THEN 'Female' WHEN 'M' THEN 'Male' END) AS Gender,
YEAR(DATE_SUB(t1.date, INTERVAL TO_DAYS(t2.DateofBirth) DAY)) AS 'Aged',
COUNT(DISTINCT(t1.Event)) AS "No. of<br />Events",
COUNT(DISTINCT(t1.time_id)) AS "No. of<br />Races",
COUNT(CASE t1.Improvement WHEN 0 THEN NULL ELSE 1 END) AS "Season<br />PBs",
COUNT(CASE t1.position WHEN 1 THEN 1 ELSE NULL END) as "Gold<br />Medals",
COUNT(CASE t1.position WHEN 2 THEN 1 ELSE NULL END) as "Silver<br />Medals",
COUNT(CASE t1.position WHEN 3 THEN 1 ELSE NULL END) as "Bronze<br />Medals"
FROM table1 t1
JOIN table2 t2 ON t1.swim_id = t2.swim_id
WHERE
(t1.Date BETWEEN "2011-10-01" and "2012-08-31")
AND
t2.Leftclub <> "Y"
GROUP BY 1

I even added a total at the bottom using the UNION command, which is where I think I was getting a little confused in the first place.

SELECT
t1.FullName AS "Fullname",
(SELECT CASE t2.Gender WHEN 'F' THEN 'Female' WHEN 'M' THEN 'Male' END) AS Gender,
YEAR(DATE_SUB(t1.date, INTERVAL TO_DAYS(t2.DateofBirth) DAY)) AS 'Aged',
COUNT(DISTINCT(t1.Event)) AS "No. of<br />Events",
COUNT(DISTINCT(t1.time_id)) AS "No. of<br />Races",
COUNT(CASE t1.Improvement WHEN 0 THEN NULL ELSE 1 END) AS "Season<br />PBs",
COUNT(CASE t1.position WHEN 1 THEN 1 ELSE NULL END) as "Gold<br />Medals",
COUNT(CASE t1.position WHEN 2 THEN 1 ELSE NULL END) as "Silver<br />Medals",
COUNT(CASE t1.position WHEN 3 THEN 1 ELSE NULL END) as "Bronze<br />Medals"
FROM table1 t1
JOIN table2 t2 ON t1.swim_id = t2.swim_id
WHERE
(t1.Date BETWEEN "2011-10-01" and "2012-08-31")
AND
t2.Leftclub <> "Y"
GROUP BY 1

UNION
SELECT
'','Totals','',
COUNT(DISTINCT(t1.Event)),
COUNT(DISTINCT(t1.time_id)),
COUNT(CASE t1.Improvement WHEN 0 THEN NULL ELSE 1 END),
COUNT(CASE t1.position WHEN 1 THEN 1 ELSE NULL END),
COUNT(CASE t1.position WHEN 2 THEN 1 ELSE NULL END),
COUNT(CASE t1.position WHEN 3 THEN 1 ELSE NULL END)
FROM table1 t
JOIN table2 s ON t1.swim_id = t2.swim_id
WHERE
(t1.Date BETWEEN "2011-10-01" and "2012-08-31")
AND
t2.Leftclub <> "Y"
ORDER BY 2,3,1

Thanks anyway.