How to get two tables data using single mysql query?

Question

Following is my tables which is student id is common field in both tables. I want to get both tables data in single query.Also get recent data of student.

 table A:

        student_id    name     surname        email
        ------------------------------------------------
              1       ABC       LLL       [email protected]
              2       PQR       SSS       [email protected]


        Table B:

          student_id  Assignment_Id  Assignment_Name      last_submited   
         ---------------------------------------------------------------------
              2            1             asign_1            sub_0001
              1            2             asign_2            sub_0002 
              2            3             asign_2            sub_0003

I want exact output like:-

student_id   Assignment_Id         email      last_submited
--------------------------------------------------------------
    2             3               [email protected]      sub_0003

I have used following query for getting recent record but confused how to get email id along with this.

SELECT assignment_id,
       student_id,
       last_submited
FROM tableB
WHERE student_id= '2'
ORDER BY assignment_id DESC LIMIT 1

mysql or sql server ... can't be both

Mudassir Hasan
– Mudassir Hasan

2014-04-16 08:06:55 +00:00
Commented Apr 16, 2014 at 8:06 — Mudassir Hasan
– Mudassir Hasan, Commented Apr 16, 2014 at 8:06

Adriaan Stander · Accepted Answer · 2014-04-16 08:10:50Z

2

You can make use of a JOIN

select  tableB.assignment_id, 
        tableB.student_id, 
        tableB.last_submited,
        tableA.email
from    tableB INNER JOIN
        tableA ON tableB.student_id = tableA.student_id
where   tableB.student_id= '2' 
order by tableB.assignment_id desc 
limit 1

INNER JOINs are used to return data where the data is in both tables (so en entry would exist in tableA and tableB).

LEFT JOINs are used when you wish to retrieve all data from tableA and those values that are available in tableB.

So, lets say you had

TABLEA
-------
1
2

and

TABLEB
-------
1

SELECT *
FROM TABLEA INNER JOIN
TABLEB ON TABLEA.ID = TABLEB.ID

would return

1,1

Whereas

SELECT *
FROM TABLEA LEFT JOIN
TABLEB ON TABLEA.ID = TABLEB.ID

would return

1,1
2,NULL

answered Apr 16, 2014 at 8:10

Adriaan Stander

167k32 gold badges294 silver badges288 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3345596 Over a year ago

Thanks Astander its working for me perfectly and thanks for explaining nicely

Sadikhasan · Accepted Answer · 2014-04-16 08:13:12Z

1

You need to use join

SELECT a.student_id,
       b.Assignment_id,
       a.email,
       b.last_submitted
FROM a
INNER JOIN b ON a.student_id = b.student_id
WHERE a.student_id= '2'
ORDER BY b.assignment_id DESC LIMIT 1

edited Apr 16, 2014 at 8:13

Sadikhasan

18.6k23 gold badges85 silver badges126 bronze badges

answered Apr 16, 2014 at 8:10

Bender

71511 silver badges26 bronze badges

Comments

Sadikhasan · Accepted Answer · 2014-04-16 08:13:17Z

1

Join both the tables,

SELECT B.assignment_id,
       B.student_id,
       A.email_id ,
       B.last_submited
FROM tableB 'B',
            tableA 'A'
WHERE B.student_id= '2'
  AND A.student_id=B.student_id
ORDER BY assignment_id DESC LIMIT 1

edited Apr 16, 2014 at 8:13

Sadikhasan

18.6k23 gold badges85 silver badges126 bronze badges

answered Apr 16, 2014 at 8:09

Veer Shrivastav

5,49611 gold badges58 silver badges84 bronze badges

Collectives™ on Stack Overflow

How to get two tables data using single mysql query?

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related