Selecting data from another table using a foreign key

Question

I have two tables A & B.

Table A

id | name | num

Table B

id | date | roll

id in table B is a foreign key which relates to the id in table A.

I want to get the contents of table B where id is given and also along with that data, I need the name whose id is given from table A.

NOTE: cannot use multiple queries separated by commas; need to do both the SELECTs in one query.

How can a question with 177k views be closed in favor of a question with 3k views? — Phani Rithvij, Dec 10 '20 at 10:55

score 25 · Answer 1 · answered Feb 11 '16 at 18:18

Joining tables is a fundamental principle of relational databases. In your case, A and B are related with the id column, which means that you can use a syntax similar to this one:

SELECT a.id, a.name, a.num, b.date, b.roll
FROM a
INNER JOIN b ON a.id=b.id;

INNER JOIN means that you'll only see rows where there are matching records in A and B. If you want all the rows in A and matching records in B, you could change INNER JOIN to LEFT JOIN. Conversely, if you want all the records from B and only the matching ones from A, use RIGHT JOIN. Finally, if you need everything from both tables, matching or not, you can use FULL JOIN.

Here is a visualisation of joins based on sets to help better understand it: https://external-content.duckduckgo.com/iu/?u=https%3A%2F%2Fupload-images.jianshu.io%2Fupload_images%2F20841286-f8e223bfa2c187f9.png&f=1&nofb=1&ipt=8c588d95db3d97ec305d05cf183c496ce0574d78c9cc3819a73de93aeca034bc&ipo=images — Gleichmut, Mar 22 '23 at 10:44

score 5 · Answer 2 · edited Feb 12 '16 at 07:08

5

For getting the result from the two table associated with the foreign key, you need to join the tables.

Find below query as like you want the result:

SELECT name, date, roll
FROM A
INNER JOIN B
ON A.id = B.id;

edited Feb 12 '16 at 07:08

Julien Vavasseur

10,109
2
27
46

answered Feb 12 '16 at 05:04

Govind

133
2
3
13

sweet and simple. thanks – Navin Singh rangar Mar 09 '23 at 06:51

score 4 · Answer 3 · answered Feb 11 '16 at 18:23

You need to join these tables to get the result that you want.

SELECT b.*, a.name FROM tableB AS b INNER JOIN tableA as A ON (b.id=a.id);

This query will return everything from Table B and name from Table A where the ID from Table B is the same as the ID from Table A.

score 0 · Answer 4 · answered Feb 12 '16 at 05:28

To retrieve data from both table associated with foreign key i.e(common column) you have to join both the tables.

if you matching data from both table then use INNER JOIN.

> SELECT A.List_Of_columns,B.List_Of_columns  FROM Table1 AS A   INNER
> JOIN Table2 as B   ON A.ID=B.ID (Here Id is Common in both table).

If you want matching data from both table along with all records from table1 then use left join instead of Inner Join. if you want all records from table2 use right join.

Selecting data from another table using a foreign key

Table A

Table B

4 Answers4