How do I place data from 2 table queries on same table row?

Question

0.00/5 (No votes)

See more:

I am trying to get scores from two separate bowling teams on one output table line.
Think i can do it with 2 separate select queries using joins as i showed below.

Input tables that i joined are
Schedule Table

DATE	    HOME TEAM #	      AWAY TEAM #
2017-02-13	     6	                7
2017-02-13	     3	                2
2017-02-13	     9           	    8
2017-02-13	     4	                5
2017-02-13	    10	                1

Data Table:

Date	TeamNum	Gm1	Gm2
12-09-16	4	726	811
12-09-16	6	540	573
12-09-16	5	450	480
12-09-16	7	640	497

Hope this is not too confusing.
Any help would be appreciated.
Thank you

What I have tried:

SELECT 'DATE','Match','TEAM', 'HmTeamNo' ,'Game 1', 'Game 2', 'Game 3', 'Total' , 'Away Tm' 
UNION (SELECT `MatchDate`, 
                   FORMAT(MatchNumber,0), 
       				TEAM, 
                   FORMAT(HmTeamNo,0), 
                   FORMAT(SUM(gm1),0), 
                   FORMAT(SUM(gm2),0), 
                   FORMAT(SUM(gm3),0), 
                   FORMAT((SUM(gm1)+SUM(gm2)+SUM(gm3)),0) as "Ttl" , 
                   FORMAT(AwayTeamNo,0) 
FROM Rainbow_Lanes_Matches 
      JOIN Rainbow_Lanes ON 
week = MatchWeek AND TEAMNUM = HmTeamNo 
WHERE MatchWeek =21
 			GROUP BY team 
ORDER BY MatchNumber
      
      )

Think it should work if i repeat the querie and change the hometeam variable to awayteam team and repeat the join but get errors.

UNION (SELECT `MatchDate`, 
                   FORMAT(MatchNumber,0), 
       				TEAM, 
                   FORMAT(HmTeamNo,0), 
                   FORMAT(SUM(gm1),0), 
                   FORMAT(SUM(gm2),0), 
                   FORMAT(SUM(gm3),0), 
                   FORMAT((SUM(gm1)+SUM(gm2)+SUM(gm3)),0) as "Ttl" , 
FROM Rainbow_Lanes_Matches 
      JOIN Rainbow_Lanes ON 
week = MatchWeek AND TEAMNUM = AwayTeamNo 
WHERE MatchWeek =21
 			GROUP BY team 
ORDER BY MatchNumber
      
      )

The second table includes the scores from the teams.

Output or Results Table will look like this:

DATE	Match	HmTm	 Gm1	 Gm2	   AwayTeam	         Gm1	Gm2	
13-02-17	1	4	     726	811	      9                  480	433
13-02-17	2	6	     540	573	      7	                 640	497

Posted 21-Feb-17 2:26am

Member 12995602

Updated 23-Feb-17 14:13pm

Nelek

Add a Solution

2 solutions

Solution 3

SELECT  "Date", "Wk", "Match", "Hm Team", "HDCP", "Gm 1", "Gm 2", "Gm 3", "Ttl","Away Team","HDCP", "Gm 1", "Gm 2", "Gm 3", "Ttl"   
UNION     

(SELECT 	
sub_home.MatchDate,
sub_home.MatchWeek,
sub_home.Matchnumber,
sub_home.HomeTeamNo,
sub_home.HDCP,
/*  sub_home.Bowler,  */
sub_home.Game1,
sub_home.Game2,
sub_home.Game3,
sub_home.TotalScore,

sub_away.AwayTeamNo,
sub_away.HDCP,
sub_away.Game1,
sub_away.Game2,
sub_away.Game3,
sub_away.TotalScore

 FROM 
(SELECT 
	`MatchDate`, 
 	FORMAT(MatchWeek,0) AS MatchWeek, 	
 	FORMAT(MatchNumber,0) AS MatchNumber, 
	TEAM, 
	FORMAT(HmTeamNo,0) AS HomeTeamNo, 

FORMAT ((250 -( SELECT SUM(Gm1+Gm2+Gm3) / ( SELECT COUNT(Gm1)*3)*0.8)),0) AS HDCP,
 
 
        FORMAT(SUM(gm1),0) AS Game1, 
	FORMAT(SUM(gm2),0) AS Game2, 
	FORMAT(SUM(gm3),0) AS Game3, 
	FORMAT((SUM(gm1)+SUM(gm2)+SUM(gm3)),0) AS TotalScore
	FROM Rainbow_Lanes_Matches 
	JOIN Rainbow_Lanes ON week = MatchWeek 
		AND TEAMNUM = HmTeamNo 
 									WHERE `Week` >= 19 
	GROUP BY `Match_id` 

 ORDER BY `MatchWeek` AND `MatchNumber`
) 
sub_home
JOIN (SELECT 
	`MatchDate`, 
 	FORMAT(MatchWeek,0) AS MatchWeek, 
    FORMAT(MatchNumber,0) AS MatchNumber, 
	TEAM, 
	FORMAT(AwayTeamNo,0) AS AwayTeamNo, 
FORMAT ((250 -( SELECT SUM(Gm1+Gm2+Gm3) / ( SELECT COUNT(Gm1)*3)*0.8)),0) AS HDCP,
      FORMAT(SUM(gm1),0) AS Game1, 
	FORMAT(SUM(gm2),0) AS Game2, 
	FORMAT(SUM(gm3),0) AS Game3, 
	FORMAT((SUM(gm1)+SUM(gm2)+SUM(gm3)),0) AS TotalScore 
	FROM Rainbow_Lanes_Matches 
	JOIN Rainbow_Lanes ON week = MatchWeek 
      AND TEAMNUM = AwayTeamNo
       								WHERE `Week` >= 19 
	GROUP BY `Match_id`
	ORDER BY `MatchWeek` AND `MatchNumber`
) 

sub_away
ON sub_home.matchnumber = sub_away.MatchNumber
AND sub_home.MatchWeek = sub_away.MatchWeek);

Posted 23-Feb-17 14:13pm

Member 12995602

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Database Star · Accepted Answer · 2017-02-21T11:46:00

I would suggest using two subqueries, like what you have done. But, instead of using UNION, I would use a JOIN, because you need to treat them as two separate tables of data and only get what you need from each of them. Using UNION could show empty results for some records.

Try this query. The first SELECT will define your output, and the subqueries get the data you need for the home and away teams.

Hope this works for you - I'm not sure of your table structure so it might throw some initial syntax errors.

SELECT sub_home.MatchDate,
sub_home.MatchWeek,
sub_home.Team,
sub_home.HomeTeamNo,
sub_home.Game1,
sub_home.Game2,
sub_home.Game3,
sub_home.TotalScore
sub_away.AwayTeamNo,
sub_away.Game1,
sub_away.Game2,
sub_away.Game3,
sub_away.TotalScore
FROM (
	SELECT `MatchDate`, 
	FORMAT(MatchNumber,0) AS MatchNumber, 
	TEAM, 
	FORMAT(HmTeamNo,0) AS HomeTeamNo, 
	FORMAT(SUM(gm1),0) AS Game1, 
	FORMAT(SUM(gm2),0) AS Game2, 
	FORMAT(SUM(gm3),0) AS Game3, 
	FORMAT((SUM(gm1)+SUM(gm2)+SUM(gm3)),0) AS TotalScore
	FROM Rainbow_Lanes_Matches 
	JOIN Rainbow_Lanes ON week = MatchWeek AND TEAMNUM = HmTeamNo 
	GROUP BY team  
) sub_home
JOIN (
	SELECT `MatchDate`, 
	FORMAT(MatchNumber,0) AS MatchNumber, 
	TEAM, 
	FORMAT(AwayTeamNo,0) AS AwayTeamNo, 
	FORMAT(SUM(gm1),0) AS Game1, 
	FORMAT(SUM(gm2),0) AS Game2, 
	FORMAT(SUM(gm3),0) AS Game3, 
	FORMAT((SUM(gm1)+SUM(gm2)+SUM(gm3)),0) AS TotalScore 
	FROM Rainbow_Lanes_Matches 
	JOIN Rainbow_Lanes ON week = MatchWeek AND TEAMNUM = AwayTeamNo 
	GROUP BY team 
	ORDER BY MatchNumber
) sub_away
ON sub_home.matchnumber = sub_away.MatchNumber
AND sub_home.MatchWeek = sub_away.MatchWeek;