CODEWITHSUNDEEP
databasesqlitedatabase-design
Leonardo Amigoni
Leonardo Amigoni

Reputation: 2317

Multiple to Multiple, Junction Tables, Newbie on Database Structure

Please feel free to comment on this as I am new and very confused on how to structure this. I want to create a database of people with interests. I want to record their interests and then see what people have common interests and display them.

I have 3 tables: Person, Interest, InterestType

Person is a table of people Interest is an interest that a person can have. InterestType is the name of the interest, say Skiing or Biking. (I separated it because I want all person to use a common typeset of interests)

My setup is as follow:

personTable: id, name, interestID

interestTable: id, interestType, personID

interestType: id, name

How do I get the list of people with the same interest?

Upvotes: 0

Views: 1577

Answers (2)

p.marino
p.marino

Reputation: 6252

I have made a simple model in Access, but you should be able to "translate" this to SQLite without too many problems.

Given:

PersonTable
personId    Name
1           Paolo
2           Carla
3           Angelo
4           Franco
5           John
6           Lisa

InterestType
interestId  Name
1           Calligraphy
2           Karate
3           Chess
4           Movies
5           Hiking

InterestTable
interestId  personId
1             1
2             1
3             1
2             2
3             2
4             2
1             3
2             3
1             5

A simple query sorted by Interest Name and then by Person Name should do the trick:

SELECT interestType.Name, personTable.Name
FROM personTable INNER JOIN 
(interestType INNER JOIN interestTable ON
 interestType.interestId=interestTable.interestId) 
ON personTable.personId=interestTable.personId
ORDER BY 1, 2;

will return:

interestType.Name   personTable.Name
Calligraphy            Angelo
Calligraphy            John
Calligraphy            Paolo
Chess                  Carla
Chess                  Paolo
Karate                 Angelo
Karate                 Carla
Karate                 Paolo
Movies                 Carla

If you want to look for a specific interest, just add a where clause:

SELECT interestType.Name, personTable.Name
FROM personTable INNER JOIN 
(interestType INNER JOIN interestTable ON interestType.interestId=interestTable.interestId) 
ON personTable.personId=interestTable.personId
WHERE interestType.Name="Karate"
ORDER BY 1, 2;


interestType.Name   personTable.Name
Karate                 Angelo
Karate                 Carla
Karate                 Paolo

Upvotes: 1

Sam Casil
Sam Casil

Reputation: 948

Try this..

SELECT * FROM personTable pt
 INNER JOIN interestTable it
    ON pt.id = it.id
 WHERE it.interestType = "theInterestType";

Upvotes: 0

Related Questions