Mysql query to get desired result

Question

I have been working with SQL joins and kinda stuck with following scenario:

Table 1: PK(ID, GRADE)

| ID | Grade | Student_Count |

|   1   |     10th     |               20                  |
|   1   |     9th       |               20                  |
|   2   |     10th     |               20                  |
|   2   |       9th     |               20                  |

Table 2:PK(ID, Grade, Visited_Date)

| ID | Grade | Visited | Visit_Date |

|   1 |    10th     |      Yes       | 25-Dec-2015 |
|   1 |    10th     |     No      | 26-Dec-2015 |
|   1 | 9th    |      Yes       | 28-Dec-2015 |
|   1 | 9th    |     No        | 29-Dec-2015 |
|   2 |    10th     |      Yes       | 27-Dec-2015 |
|   2 | 9th    |     No      | 30-Dec-2015 |

What i need is a SELECT query which returns data from both the tables for a given ID in such a way that output rows should match the data of the Table 2 (no all possible combinations like cross/dot product) along with non-common columns from Table 1.

For example for ID "1" the output should be:

| ID | Grade | Student_Count | Visited | Visit_date |

| 1 | 10th | 20 | Yes | 25-Dec-2015 |

| 1 | 10th | 20 | No | 26-Dec-2015 |

| 1 | 9th | 20 | Yes | 28-Dec-2015 |

| 1 | 9th | 20 | No | 29-Dec-2015 |

Note: There is no foreign key association between both the tables.

Refer: http://stackoverflow.com/questions/9460435/mysql-selecting-from-two-tables — Insane Skull, Jan 07 '16 at 12:48
Shouldn't the desired result row with date "29-Dec-2015" be "9th" grade? — ain, Jan 07 '16 at 12:50
@Prashantsoni Didn't understand last row of output for ID "1" - | 1 | 10th | 20 | No | 29-Dec-2015 |; I think grade should be 9 instead of 10 or I am getting it incorrect. — Nayan Wadekar, Jan 07 '16 at 12:51
I've removed the SQL-Server tag as this is a different product. SQL Server answers won't always work with MySQL. — David Rushton, Jan 07 '16 at 12:59

score 0 · Accepted Answer · answered Jan 07 '16 at 12:56

0

You have to JOIN both tables on the id and grade fields.

SELECT b.ID, b.Grade, a.Student_Count, b.Visited, b.Visit_date
FROM table2 b 
INNER JOIN table1 a 
ON a.ID = b.ID AND a.Grade = b.Grade
ORDER BY a.ID

answered Jan 07 '16 at 12:56

antoniodvr

1,259
1
14
15

Thanks, this works just fine. I missed Join on Grade in my original query – Prashant soni Jan 07 '16 at 13:52

Mysql query to get desired result

| ID | Grade | Student_Count |

| ID | Grade | Visited | Visit_Date |

| ID | Grade | Student_Count | Visited | Visit_date |

1 Answers1