Combine 3 queries using inner join

Question

I have 3 tables that I am trying to pull information from. Sample data shown below

TABLE Trip
idTrip  Title        Date
1       Ben Lomond   08-08-2016

TABLE Person_has_trip
Trip_idTrip   Person_idPerson
1             1
1             2

TABLE Person
idPerson   Forename   Surname
1          David      Jack
2          Colin      McAlpine

I am trying to get the name of each trip and the name of each person that has been on that trip. Separately the queries look like this

1.  SELECT idTrip, title, Date from Trip  
2.  SELECT Person_idPerson from Person_has_Trip where Trip_idTrip = $idTrip  
3.  SELECT forename, surname from person where idPerson = $idPerson

Is there a way I can combine these 3 queries? I thought I had a working solution, but discovered today that it was missing some data.

The query I thought was working but isn't working is below

select trip.idTrip, trip.title, trip.date, Person_has_Trip.Person_idPerson, person.forename, person.surname 
        from trip
        inner join
            Person_has_Trip
            on trip.idTrip = Person_has_Trip.trip_idTrip
        inner join 
            person
            on Person_has_Trip.Person_idPerson = person.idPerson
        ORDER BY trip.date

Any suggestions would be excellent. I am currently learning SQL as I go, so some of the more advanced features like joins are, at the moment, a little over my head.

When I say it isn't working, it isn't display all the data I expect. It displays some, but not all.

When you say that the query "isn't working", what do you mean? No results? Wrong results? Syntax error? Please [edit] your question to include some additional information. — Mr. Llama, Jun 21 '17 at 16:17
Edited. Thanks for pointing this out. Not receiving the correct output, some data seems to be missing and not being picked up by the query. — David Jack, Jun 21 '17 at 16:22
The only way I can see you would be "missing" data is if you have records in person_has_trip that are not represented in Trip or person; but your sample data shows no such circumstance. you would need to show an example of what's "missing" so we can help. For example using your sample data I would only expect to see 2 records and data in 6 columns. is a column missing data? is a record Missing? what? Perhaps you expect to see 10 trips; but one trip has no people on it so that trip would be omitted from the results due to the nature of inner joins. Perhaps you need to use left joins? — xQbert, Jun 21 '17 at 16:22
Great visual aid on understanding joins: https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/ — xQbert, Jun 21 '17 at 16:26
You need to describe what output you want in terms of the input. We can't read your mind. Please read and act on [mcve]. — philipxy, Jun 22 '17 at 09:55
@xQbert & DavidJack That blog post is poor. In the comments *the author themself repudiates it*. Venn diagrams can illustrate the difference between outer & inner joins. They don't illustrate inner or outer joins in terms of their inputs. [Re SQL JOINs.](https://stackoverflow.com/a/25957600/3404097) — philipxy, Jun 22 '17 at 10:06

score 1 · Answer 1 · answered Jun 21 '17 at 16:30

Perhaps the issue is you want to see all trips regardless if they have any people. You could use a left join in this case

SELECT trip.idTrip
     , trip.title
     , trip.date
     , Person_has_Trip.Person_idPerson
     , person.forename
     , person.surname 
FROM trip
LEFT JOIN Person_has_Trip
   on trip.idTrip = Person_has_Trip.trip_idTrip
LEFT JOIN person
   on Person_has_Trip.Person_idPerson = person.idPerson
ORDER BY trip.date

or you want to see all people even if they have not been on a trip...

SELECT trip.idTrip
     , trip.title
     , trip.date
     , Person_has_Trip.Person_idPerson
     , person.forename
     , person.surname 
FROM trip
RIGHT JOIN Person_has_Trip
   on trip.idTrip = Person_has_Trip.trip_idTrip
RIGHT JOIN person
   on Person_has_Trip.Person_idPerson = person.idPerson
ORDER BY trip.date

or perhaps both... (mySQL doesn't support full outer joins so we simulate it)

SELECT trip.idTrip
     , trip.title
     , trip.date
     , Person_has_Trip.Person_idPerson
     , person.forename
     , person.surname 
FROM trip
LEFT JOIN Person_has_Trip
   on trip.idTrip = Person_has_Trip.trip_idTrip
LEFT JOIN person
   on Person_has_Trip.Person_idPerson = person.idPerson
UNION 
SELECT trip.idTrip
     , trip.title
     , trip.date
     , Person_has_Trip.Person_idPerson
     , person.forename
     , person.surname 
FROM trip
RIGHT JOIN Person_has_Trip
   on trip.idTrip = Person_has_Trip.trip_idTrip
RIGHT JOIN person
   on Person_has_Trip.Person_idPerson = person.idPerson
ORDER BY trip.date

Wayne F. Kaskie · Answer 2 · 2017-06-21T16:24:26.363

0

In case this is a quick answer, you have an extra . in the query.

.Person_has_trip.Person_idPerson

should be

Person_has_trip.Person_idPerson

Also, watch the character case. In many platforms, this is important with column and table names. Note you use both Person and person to refer to the table.

https://dev.mysql.com/doc/refman/5.7/en/identifier-case-sensitivity.html

If that doesn't fix it, what platform are you using? MSQL, Oracle?

edited Jun 21 '17 at 16:24

answered Jun 21 '17 at 16:17

Wayne F. Kaskie

3,257
5
33
43

Thanks for spotting this. Didn't fix it though. Using MySQL and PHP to format and display the data at the front end. – David Jack Jun 21 '17 at 16:19
I noticed some capitalization discrepancies as well. For example, Person_has_trip vs. Person_has_Trip. I'm pointing out these things because it otherwise, looks correct. – Wayne F. Kaskie Jun 21 '17 at 16:20
Tidied up any capitalization issues. Thanks again. – David Jack Jun 21 '17 at 16:24
Did that fix it? – Wayne F. Kaskie Jun 23 '17 at 15:23

score 0 · Answer 3 · answered Jun 21 '17 at 16:24

will return as you wish: Returns two rows

CREATE Table Trip
( idTrip int
 ,Title varchar(20)
 ,Date date
  )
 ;

 CREATE Table Person_has_trip
( Trip_idTrip  int
 ,Person_idPerson int
)
 ;

CREATE TABLE Person
 ( idPerson int
   ,Forename varchar(20)
   ,Surname varchar(20)
);

INSERT INTO Trip VALUES (1, 'Ben Lomond', '2016-08-08' );

INSERT INTO Person_has_trip VALUES (1, 1);
INSERT INTO Person_has_trip VALUES (1, 2);

INSERT INTO Person VALUES (1, 'David', 'Jack')
INSERT INTO Person VALUES (2, 'Colin', 'McAlpine')


SELECT idTrip
      ,Title
      ,Date
      ,Person_idPerson
      ,Forename
      ,Surname 
  FROM Trip
 INNER JOIN Person_has_trip
    ON Trip_idTrip = idTrip  
 INNER JOIN Person
    ON Person_idPerson = idPerson

Returns:

idTrip  Title       Date        Person_idPerson Forename    Surname
1       Ben Lomond  2016-08-08  1               David       Jack
1       Ben Lomond  2016-08-08  2               Colin       McAlpine

Wow, thanks for the effort and the code. Just tested out your query and I'm having the same results. I'm now starting to assume that there must be another issue with my DB. — David Jack, Jun 21 '17 at 16:27
could you please be a little more detailed. whats exactly your "issue"? what do you expect and what do you currently receive as result? just with the testdata you listed above or with your real data - which we don't know — Esteban P., Jun 21 '17 at 16:35

score 0 · Answer 4 · answered Jun 21 '17 at 16:28

You can use the reference table "TABLE Person_has_trip" as base to get values you want from the Person and Trip table. You can manipulate the tables as you wish to show what you want/don't want

This is a little easier to understand on how JOINS work. This visual guide is always useful: https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

SELECT a.Trip_idTrip ,
       a.Person_idPerson,
       b.Forname,
       b.Surname,
       c.Title,
       d.Date

 FROM Person_has_trip a

 --Join here to get their names based on person_id
  JOIN (SELECT idperson,
             forename,
             surname
      From Person) b on b.idperson = a.Person_idPerson

 --Join here to get their trip information based on trip ID
   JOIN (SELECT idtrip,
             title,
             date
      From Trip) c on c.idtrip= a.Trip_idTrip

score 0 · Accepted Answer · answered Jun 21 '17 at 16:31

0

xQbert got it.

The Person_has_trip table was missing data so the query was then missing trips as it wasn't listed properly in Person_has_trip.

Thank you for all your help. As soon as people started saying that my query looked good and that it should work I had to have been something wrong with the data.

Excellent, thank you once again! :)

answered Jun 21 '17 at 16:31

David Jack

87
6

Comments are ephemeral. Please edit this to be self-contained. Also please accept it to show that your question has been answered satisfactorily. – philipxy Jun 22 '17 at 10:09

Combine 3 queries using inner join

5 Answers5