How to joint Two customs Queries with two Joins in only One Query in MySQL

Question

The Queries are working perfectly each one separatedly:

SELECT asf.surface_name, am.*
FROM atp_matchs_to_surfaces m2s
LEFT JOIN atp_surfaces asf ON m2s.surfaces_id = asf.surfaces_id
LEFT JOIN atp_matchs am ON am.matchs_id = m2s.matchs_id;

SELECT att.tournament_type_name, am.*
FROM atp_matchs_to_tournament_type m2s
LEFT JOIN atp_tournament_type att ON m2s.tournament_type_id = att.tournament_type_id
LEFT JOIN atp_matchs am ON am.matchs_id = m2s.matchs_id;

The tables 'atp_matchs_to_surfaces' and 'atp_matchs_to_tournament_type' are defined in that way:

CREATE TABLE IF NOT EXISTS `atp_matchs_to_tournament_type` (
  `tournament_type_id` int(4) NOT NULL,
  `matchs_id` int(6) NOT NULL,
  PRIMARY KEY (`tournament_type_id`,`matchs_id`)

CREATE TABLE IF NOT EXISTS `atp_matchs_to_surfaces` (
  `surfaces_id` int(4) NOT NULL,
  `matchs_id` int(6) NOT NULL,
  PRIMARY KEY (`surfaces_id`,`matchs_id`)

And the other Tables with all the data:

CREATE TABLE IF NOT EXISTS `atp_matchs` (
  `matchs_id` int(7) NOT NULL AUTO_INCREMENT,
  `tournament_name` varchar(36) NOT NULL,
  `tournament_year` year NOT NULL,-- DEFAULT '0000',
  `tournament_country` varchar(26) NOT NULL,
  `match_datetime` datetime NOT NULL,-- DEFAULT '0000-00-00 00:00:00',
  `match_link` varchar(85) NOT NULL,
  `prize_money` int(12) NOT NULL,
  `round` varchar(8) NOT NULL,-- DEFAULT '1R',
  `sets` varchar(34) NOT NULL,-- DEFAULT '0-0',
  `result` varchar(4) NOT NULL,-- DEFAULT '0-0',
  `p1_odd` decimal(4,2) NOT NULL,-- DEFAULT '0.00',
  `p2_odd` decimal(4,2) NOT NULL,-- DEFAULT '0.00',
  PRIMARY KEY (`matchs_id`)

CREATE TABLE IF NOT EXISTS `atp_surfaces` (
  `surfaces_id` int(4) NOT NULL AUTO_INCREMENT,
  `surface_name` varchar(24) NOT NULL,
  PRIMARY KEY (`surfaces_id`)

CREATE TABLE IF NOT EXISTS `atp_tournament_type` (
  `tournament_type_id` int(4) NOT NULL AUTO_INCREMENT,
  `tournament_type_name` varchar(22) NOT NULL,
  PRIMARY KEY (`tournament_type_id`)

I want in the same Query all the records of match and surface name+tournament type. It's clear? I hope...

I tried to implement this with SubQueries: http://www.w3resource.com/mysql/subqueries/ and How can an SQL query return data from multiple tables but i can't do it to work.

You should indicate the columns of all the tables involved, because we can't see any connection between the two tables you provided atp_matchs_to_tournament_type and atp_matchs_to_surfaces — CurseStacker, Mar 04 '15 at 09:48
Here we go. I added the other involved tables as requested. Thx. — bLIGU, Mar 04 '15 at 10:24

Vladislav Tesařík · Accepted Answer · 2015-03-04T20:58:53.493

2

OK, this is your current schema. As you can see, one match can be played on multiple surfaces and one match can be played within multiple tournament types.

Your current schema

If this schema is OK, you can get your result with this query:

SELECT am.*, asu.surface_name, att.tournament_type_name
FROM atp_matchs AS am
LEFT JOIN atp_matchs_to_surfaces AS m2s ON m2s.matchs_id = am.matchs_id
LEFT JOIN atp_surfaces AS asu ON asu.surfaces_id = m2s.surfaces_id
LEFT JOIN atp_matchs_to_tournament_type AS m2t ON m2t.matchs_id = am.matchs_id
LEFT JOIN atp_tournament_type AS att ON att.tournament_type_id = m2t.tournament_type_id

However, if one match can be played on one surface only and within one tournament type only, I would change your schema to:

Suggested schema

Tables atp_matchs_to_surfaces and atp_matchs_to_tournament_type are removed and fields surfaces_id and tournament_type_id moved to atp_matchs table. Your query is now:

SELECT am.*, asu.surface_name, att.tournament_type_name
FROM atp_matchs AS am
LEFT JOIN atp_surfaces AS asu ON asu.surfaces_id = am.surfaces_id
LEFT JOIN atp_tournament_type AS att ON att.tournament_type_id = am.tournament_type_id

edited Mar 04 '15 at 20:58

answered Mar 04 '15 at 09:50

Vladislav Tesařík

113
1
8

Thx for the answer! If I do UNION or UNION ALL, 'att.tournament_type_name' defined in the second SELECT is not displayed in the final result. It only show 'asf.surface_name' who's in the first SELECT. – bLIGU Mar 04 '15 at 10:16
Column names of the result are taken from the first SELECT, so that there are mixed values of 'att.tournament_type_name' and 'asf.surface_name' – Vladislav Tesařík Mar 04 '15 at 10:34
Ahhh ok, now I understand. So union doesn't do the job I want to do. – bLIGU Mar 04 '15 at 10:55
Sorry for misunderstanding, do you want to have these values in separate columns of the result? In other words, do you want to have list of matches and related tournament type and surface to each of them? – Vladislav Tesařík Mar 04 '15 at 11:08
I want 'att.tournament_type_name'+'asf.surface_name'+am.*(all atp_matchs records) in same Result. In human words, I want for every Match, the records of the match, the Surface (it can be hard, clay...) and the type of match (it can be ATP1000/ATP500/ATP250...). I'm more clear now? hehe Thx for your help! – bLIGU Mar 04 '15 at 11:41
I see, now, is this what you need? – Vladislav Tesařík Mar 04 '15 at 13:52
+1 @VladislavTesařík Your code work like a charm and now with the schema logic I understand how to do it. Great tanks. I can't give you reputation points because I need 15 points. – bLIGU Mar 04 '15 at 19:27

score 0 · Answer 2 · answered Mar 04 '15 at 09:45

0

The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2).

SELECT asf.surface_name, am.* FROM atp_matchs_to_surfaces m2s LEFT JOIN (SELECT att.tournament_type, am.* FROM atp_matchs_to_tournament_type m2s) as......

answered Mar 04 '15 at 09:45

Akash kumar

981
3
14
27

Sorry @Akash kumar but I can't do it works. Can you write a working code or a simplified working code of your solution example. thx. – bLIGU Mar 04 '15 at 10:46
please paste the query if you implemented it according to the suggested one. – Akash kumar Mar 04 '15 at 10:53

score 0 · Answer 3 · answered Mar 04 '15 at 11:00

SELECT asf.surface_name, am.*
FROM atp_matchs_to_surfaces m2s
LEFT JOIN atp_surfaces asf ON m2s.surfaces_id = asf.surfaces_id
LEFT JOIN atp_matchs am ON am.matchs_id = m2s.matchs_id
LEFT JOIN(
SELECT att.tournament_type, am.*
FROM atp_matchs_to_tournament_type m2s
LEFT JOIN atp_tournament_type att AS Q1 ON m2s.surfaces_id = att.surfaces_id
LEFT JOIN atp_matchs am AS Q2 ON am.matchs_id = m2s.matchs_id);

I added some "AS" because I had the error: Every derived table must have its own alias. I'm a little lost here!

How to joint Two customs Queries with two Joins in only One Query in MySQL

3 Answers3