How to write SQL query using a join on 3 tables?

Question

I have created a hypothetical scenario with some dummy data. Below are 3 basic tables and I'm looking to write a query to find what property/properties Mary has viewed. In this scenario Mary has viewed 1 property a flat in Glasgow.

Table 1: Client

=====================
ID  Name        
=====================
5   Tom
6   Mary
7   John

Table 2: Property

=====================
ID  CITY        TYPE    
=====================
14  Aberdeen    House
16  Glasgow     Flat
21  Glasgow     House
94  London      Flat

Table 3: Viewing

========================
Client  Property    Date    
========================
5       14      01-12-2016
5       21      08-12-2016
6       16      10-10-2016

INNER JOIN would be my first thought, particularly for someone learning SQL. — Gordon Linoff
– Gordon Linoff, Commented Oct 5, 2017 at 20:21
SEarle1986 I was just trying to see how I would go about seeing what property Mary viewed — Super Hans
– Super Hans, Commented Oct 5, 2017 at 20:27
Sorry, missed that bit! The question has answers now but I agree with them that JOIN (Or INNER JOIN) is the way to go — SE1986
– SE1986, Commented Oct 5, 2017 at 20:29
Thanks SEarle1986 I see them, i'm just looking at the two answers now and trying figure them out as one uses join and the other inner Join — Super Hans
– Super Hans, Commented Oct 5, 2017 at 20:34

Super Hans · Accepted Answer · 2017-10-05 21:22:15Z

4

Definitely use inner joins for this, a quick example of this could be

SELECT c.Name, p.Type, p.City, v.Date
FROM Viewing v 
JOIN Client c ON v.client = c.ID
JOIN Property p ON v.property = p.ID
WHERE c.Name = 'Mary'

That should show you who view what and when. I've used aliases on the table names just to keep it neat.

edited Oct 5, 2017 at 21:22

Super Hans

1282 silver badges11 bronze badges

answered Oct 5, 2017 at 20:25

Dale

1,95111 silver badges18 bronze badges

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

Collectives™ on Stack Overflow

How to write SQL query using a join on 3 tables?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related