Creating Views Joining Multiple Tables

Question

I am trying to create views of a database containing information about horses, more specifically horses, their owners, and the horse boarder. The view must join three tables. I have to create a view of "first name, last name, primary phone, and barnname". Then I have to join tables "boarder, horse, and boarder_horse" to create the relationship. I can't figure out how to connect the various tables together.

So far, this is what I have:

CREATE VIEW horse_owner
AS
SELECT b.boarder firstname, b.boarder lastname, b.boarder primaryphone, 
h.horse barname

FROM boarder b
INNER JOIN horse h
ON bh.horse_id = h.id
INNER JOIN boarder_horse
ON bh.boarder_id = b.id

ORDER BY LastName DESC;

I don't understand how to correctly link the appropriate tables together.

your alias for boarder is `b`, but you try to join it with `bh` — WillardSolutions, Nov 08 '18 at 20:59
`INNER JOIN horse h ON b.horse_id = h.id` and you have no alias for `boarder_horse` — WillardSolutions, Nov 08 '18 at 20:59
Can you give sample data for the three tables, and expected output for that sample? — trincot, Nov 08 '18 at 21:01
I tried posting pictures of the tables im referencing but they wont save — jsh_crpntr, Nov 08 '18 at 21:05
I got the pictures to post, those are the tables I'm trying to join — jsh_crpntr, Nov 08 '18 at 21:07
Possible duplicate of [INNER JOIN ON vs WHERE clause](https://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause) — jsh_crpntr, Nov 08 '18 at 21:20

Michał Turczyn · Accepted Answer · 2018-11-09T06:32:03.850

0

You had order of JOINs wrong. Also, you were missing alias bh. Try:

CREATE VIEW horse_owner
AS
SELECT b.firstname, b.lastname, b.primaryphone, 
h.barname

FROM boarder b
INNER JOIN boarder_horse bh
ON bh.boarder_id = b.id
INNER JOIN horse h
ON bh.horse_id = h.id

ORDER BY LastName DESC;

edited Nov 09 '18 at 06:32

answered Nov 08 '18 at 21:01

Michał Turczyn

32,028
14
47
69

Creating Views Joining Multiple Tables

1 Answers1