SQL Table Joins - What am I doing wrong?

Question

I'm in a intro to database class, and one of my queries is seriously giving me trouble. The assignment says to: Write a query to display the Passenger name, Seat No and Destination. Display this in one column title Travellers_info. This column should display data in the following format “ Mary Ann Jenkins is assigned to Seat 15 on the way to Bellmead”

This is the relationship view: http://prntscr.com/1jsoay

Can somebody please help me out, I'm not sure where I've gone wrong.

SELECT passenger.name + 'is assigned to Seat'
     + seat_info.seat_no + 'on the way to'
     + departure_info.destination AS Travellers_info
FROM passenger, seat_info, departure_info, seat_passenger, manages
WHERE passenger.Pass_id=seat_passenger.pass_id
  AND seat_passenger.Seat_id=seat_info.Seat_id
  AND seat_info.seat_id=manages.Seat_id
  AND manages.Dept_id=departure_info.dept_id

What is happening? Do you get an error, or no results, or too many results? Also, what's with the random field capitalisation? — Amadan, Aug 06 '13 at 22:32
I'm using SQL server, and it likes to complete my column names with capital letters. — Cody Robinson, Aug 06 '13 at 22:36
and @Amadan no error, just no result at all. It says the column name 'Travellers_info', but that is all. — Cody Robinson, Aug 06 '13 at 22:37
Take a look at ANSI v non-ANSI joins http://stackoverflow.com/questions/1599050/ansi-vs-non-ansi-sql-join-syntax — Christian Phillips, Aug 06 '13 at 22:39
From what you've been saying in the other answers, this seems to be a data problem. Can you post screenshots of the contents of the 5 tables (or first dozen records or so of each)? — Kcoder, Aug 06 '13 at 23:45

score 1 · Answer 1 · answered Aug 06 '13 at 22:33

You cant "add" text values using the "+" operator.

Without knowing which database you are using, the solution is probably either using CONCAT():

SELECT concat(passenger.name, 'is assigned to Seat', seat_info.seat_no,
    'on the way to', departure_info.destination) AS Travellers_info
FROM passenger, seat_info, departure_info, seat_passenger, manages
WHERE passenger.Pass_id=seat_passenger.pass_id
  AND seat_passenger.Seat_id=seat_info.Seat_id
  AND seat_info.seat_id=manages.Seat_id
  AND manages.Dept_id=departure_info.dept_id

or using the || operator:

SELECT passenger.name || 'is assigned to Seat'
     || seat_info.seat_no || 'on the way to'
     || departure_info.destination AS Travellers_info
FROM passenger, seat_info, departure_info, seat_passenger, manages
WHERE passenger.Pass_id=seat_passenger.pass_id
  AND seat_passenger.Seat_id=seat_info.Seat_id
  AND seat_info.seat_id=manages.Seat_id
  AND manages.Dept_id=departure_info.dept_id

You can in MSSQL and Access. In fact, || doesn't work in MSSQL. — Kcoder, Aug 06 '13 at 22:34
|| is used in informix, postgres. concat() is used mysql and sqlserver. don't know about oracle — Bohemian, Aug 06 '13 at 23:16

Kcoder · Answer 2 · 2013-08-06T22:45:12.780

1

Have you tried double-checking all your tables (especially the joining tables like "manages" and "seat_passenger") to make sure you have valid data in them that would appropriately join up?

Also, make sure you have spaces in your text.

e.g.: 'is assigned to Seat' should be ' is assigned to Seat '

edited Aug 06 '13 at 22:45

answered Aug 06 '13 at 22:39

Kcoder

3,422
4
37
56

score 1 · Answer 3 · answered Aug 06 '13 at 22:47

1

If you are getting no data it must be in your join criteria. Reduce your select to just a column from your first table and then join each table one at a time and see when it is that you stop getting data back

answered Aug 06 '13 at 22:47

Gratzy

9,164
4
30
45

In fact, you don't even need to select a column. You can select count(*). – Dan Bracuk Aug 06 '13 at 22:51
when i try with even just the first where condition, i still get nothing. – Cody Robinson Aug 06 '13 at 22:56
1

Well then there is no data that evaluates to true for this criterial passenger.Pass_id=seat_passenger.pass_id – Gratzy Aug 06 '13 at 22:57
Try it with just one table and no where condition. – Dan Bracuk Aug 06 '13 at 23:52

score 0 · Answer 4 · edited Aug 06 '13 at 23:02

First off, you're going to want to put a space before is so that it won't look mushed together.

    passenger.name + ' is assigned to seat'

Typically, you use subqueries and left outer joins for something of this sort. You do not just want to pull out all the data at once from all those tables. Let me see if I can explain this with some SQL. If you don't understand my example, go here: http://thenewboston.org/watch.php?cat=49&number=20 and watch 20-23 and you'll for sure understand it.

SELECT c.name + ' is assigned to Seat ' + c.seat_no + ' on the way to ' + departure_info.destination as Travellers_info 
FROM departure_info 
LEFT OUTER JOIN (SELECT b.Pass_id, b.name, b.seat_id, b.seat_no 
                 FROM seat_no 
                 LEFT OUTER JOIN (SELECT a.Pass_id, a.name, seat_passenger.seat_id 
                                  FROM seat_passenger 
                                  LEFT OUTER JOIN (SELECT passenger.name, passenger.Pass_id 
                                                   from passenger 
                                                   LEFT OUTER JOIN 
                                                     ON passenger.Pass_id = seat_passenger.Pass_id) a seat_info 
                                    ON a.seat_id = seat_info.seat_id) b manages 
                   ON b.seat_id = manages.seat_id) c departure_info 
  ON c.Dept_id = departure_info.Dept_id

I do not believe this will yield you the correct answer. But, it will get you working towards the right answer. I think I made a mistake after the third subquery. Remember that SQL evaluates things from the inside, then goes out. Just keep that in mind. Hope this helps.

Syntax errors aside, why would you favour derived tables over simple joins? — Nikola Markovinović, Aug 06 '13 at 23:11
That's how I was taught to do it when I started my internship when I was 17. It worked well for me. — Mardin Yadegar, Aug 06 '13 at 23:13

score 0 · Answer 5 · answered Aug 06 '13 at 23:10

try this in MSSQL or MSAccess.

for query optimization use ON (Join)

SELECT passenger.name + 'is assigned to Seat '
 + ltrim(rtrim(convert(char,seat_info.seat_no))) + ' on the way to'
 + departure_info.destination AS Travellers_info
FROM passenger p
join    seat_passenger sp on sp.pass_id = p.Pass_id
join    seat_info s on s.Seat_id = sp.Seat_id
join    manages m on m.Seat_id = s.Seat_id
join    departure_info d on d.dept_id = m.dept_id

concat in 2008R2 is not a valid function so you need to convert a number data to character. while in 2012 it is available.

SQL Table Joins - What am I doing wrong?

5 Answers5