Basic SQL Help Needed Urgently

[Tobey]

This seems to be the basic of basic, from what else I've seen on your forums but if anyone can help here it'd be greatlt appreciated as this project is due in an hour.

I have a sql statement that runs fine in any query analyzer (below):

select
C.CustomerID, C.CustomerName, C.Phone#, B.Flight#, F.Date, F.Time
from
Customer C, Bookings B, Flight F
where
B.Flight# = F.Flight# and C.CustomerID = B.CustomerID
order by
C.CustomerID

but when i run it through a simple java program it returns a syntax error near C.

Unfortuneatly inner joins are not an option, but I have the exact same problem with them anyway, a syntax error near something or other.

Anyone know what the problem is??

[slimdave]

no idea what the problem is, but try ...

select 
C.CustomerID, C.CustomerName, C.Phone#, B.Flight#, F.Date, F.Time
from 
Customer C, Bookings B, Flight F
where 
B.Flight# = F.Flight# and C.CustomerID = B.CustomerID
order by 
1

[Tobey]

Well I'll be...

Why did that work?

Not that it matters, it works which is the main thing.

Thank you very much, if you know why that worked and the other didn't I'd love to know, this has been holding me up all day.

Great! Thank You!

[Tobey]

While I'm here...

any suggestions on a a single large table, a suitable join of all tables) from the below tables??

Flight(Flight#, Route)
Customer(CustomerID, CustomerName, Number)
Employee(EmployeeID, EmployeeName, Title)
Bookings(Flight#, CustomerID)
WorkSchedule(Flight#, EmployeeID)

Not Urgent but it would be nice if someone out there could help.

[slimdave]

It's a mystery to me why one works and the other doesn't - maybe it's to do with some Java thing.

Why do you want to create one table out of all those others?

[Tobey]

Thats a very good question and one I've asked my lecturer, his response was 'Just do what it says in the assignment'.

I really have no other motivation, but it's not necessary, I've done what I've been given to do(everything with some logic behind it anyway).

This is just an exercise, not any sort of application in real life, I'm told to create a single large table, a suitable join of all the tables, but as of yet have not come to any conclusion why. I can cross join a few things here and there and select anything with any relevance, and I'd be happy. But as you may have noticed I'm just a novice, and any advice you could give me would be appreciated.

Thanks again.

[slimdave]

Since customer has to join to bookings, and employee has to join to workschedule, that just leaves the question of how to join flight, bookings and work_schedule.

The flight table must be a parent of both the bookings and the workschedule tables -- you can't have a booking or a workschedule for a flight that doesn't exist, but a flight has to have neither bookings nor workschedule, so i would join flight-to-workschedule and flight-to-bookings.

You might want to get involved in outerjoins if the purpose of the table was to display the customers per flight, or the employees per flight.

The reason that this is an asinine example is that the logical joins from flight to bookings and flight to workschedule are both 1-to-many. Joining the three tables like this establishes a fan trap, which you could think of as a partial cartesian product.

Consider a flight having 200 customers and 6 employees -- the single table view yields a 1200 row table that is meaningless except for the purpose maybe of finding out all the possible combinations of sexual relationship that could be established between customer and employee during a flight.

I guess it's true -- those who can, do, and those who can't, teach. Maybe fan traps are taught next year.

[stecal]

200C6 = 200!/194!6! = 8.2409E10. Quite a few.

[stecal]

create or replace view where_is_harry as (
passenger_id,
flight#,
route,
name,
phone#,
title,
cust_flag, -- or use one flag coded as C or E
emp_flag)
as
select
... (for you to figure out)

bookings and workschedule are the same type of table. Make a view out of that with a union.

customer and employee are the same type of table after you match up some columns and add a column to each (cust_flag, emp_flag). You can make a view via a union, nvl, and maybe a decode.

Give me the ID, and I can tell you everything about him/her by looking at one view - which would be great for a Form data block.

[slimdave]

Originally posted by stecal
200C6 = 200!/194!6! = 8.2409E10. Quite a few.

Ah, i was only considering matchups of 1 employee + 1 customer.