SQL join two tables related by a single column primary key or foreign key pair using where clause

Description

In this page, we are going to discuss the usage of two or more tables in a joining with single column PRIMARY KEY and FOREIGN KEY.

Example:

Sample table: foods

Sample table: company

To get 'item_name' and 'item_unit' from 'foods' table and 'company_name' and 'company_city' form 'company' table after a joining with following conditions-

1. 'company_id' is primary key in 'company' table,

2. 'company_id' is foreign key in 'foods' table which is referencing to the, primary key of 'company' table,

3. 'company_id' of 'company' and 'foods' must be same,

4. 'company_city' of 'company' column must be 'London',

the following SQL statement can be used :

SQL Code:

SELECT foods.tem_name,foods.item_unit,
company.company_name, company.company_city
FROM foods ,company
WHERE  foods.company_id =company.company_id
AND company.company_city='London';

Output

ITEM_NAME                 ITEM_UNIT  COMPANY_NAME              COMPANY_CITY
------------------------- ---------- ------------------------- -------------
Cheez-It                  Pcs        Jack Hill Ltd             London
BN Biscuit                Pcs        Jack Hill Ltd             London
Mighty Munch              Pcs        Foodies.                  London
Pot Rice                  Pcs        Jack Hill Ltd             London

See our Model Database

Here is a new document which is a collection of questions with short and simple answers, useful for learning SQL as well as for interviews.

Practice SQL Exercises

• SQL Exercises, Practice, Solution
• SQL Retrieve data from tables [33 Exercises]
• SQL Boolean and Relational operators [12 Exercises]
• SQL Wildcard and Special operators [22 Exercises]
• SQL Aggregate Functions [25 Exercises]
• SQL Formatting query output [10 Exercises]
• SQL Quering on Multiple Tables [8 Exercises]
• FILTERING and SORTING on HR Database [38 Exercises]
• SQL JOINS
• SQL JOINS [29 Exercises]
• SQL JOINS on HR Database [27 Exercises]
• SQL SUBQUERIES
• SQL SUBQUERIES [39 Exercises]
• SQL SUBQUERIES on HR Database [55 Exercises]
• SQL Union[9 Exercises]
• SQL View[16 Exercises]
• SQL User Account Management [16 Exercise]
• Movie Database
• BASIC queries on movie Database [10 Exercises]
• SUBQUERIES on movie Database [16 Exercises]
• JOINS on movie Database [24 Exercises]
• Soccer Database
• Introduction
• BASIC queries on soccer Database [29 Exercises]
• SUBQUERIES on soccer Database [33 Exercises]
• JOINS queries on soccer Database [61 Exercises]
• Hospital Database
• Introduction
• BASIC, SUBQUERIES, and JOINS [39 Exercises]
• Employee Database
• BASIC queries on employee Database [115 Exercises]
• SUBQUERIES on employee Database [77 Exercises]
• More to come!

Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.

Previous: Joining tables with group by and order by
Next: Join two tables related by a composite primary key or foriegn key pair