w3resource logo


SQL WHERE CLAUSE PRIMAY AND FOREIGN KEY

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

Secondary Nav

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 :

SELECT foods.item_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.



Join our Question Answer community to learn and share your programming knowledge.

Solve these problems:

Java: How to convert a string to an integer in Java?

C#: Loops in c#

SQL: JOIN using more than 5 tables