Selecting data from another table using a foreign key
I have two tables A & B.
id | name | num
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.
I did google and found out some weird keywords LEFT, JOIN but I couldn't understand it.
I need to get date and roll from Table B and name from table A in a single query. (id is a foreign key)
The plugin I am using sadly doesn't allow multiple queries separated by commas so I need to do both the
SELECTs in one query.
Joining tables is a fundamental principle of relational databases. In your case, A and B are related with the
idcolumn, 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 JOINmeans 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
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
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.