Home » RDBMS Server » Server Administration » query
query [message #371304] Sat, 30 September 2000 12:08 Go to next message
christopher
Messages: 25
Registered: September 2000
Junior Member
I am doing a query from three different tables and when I call up the data I get the same last name twice and destination. I have two people going to Miami but only one shows up on the query. Any suggestions?

SELECT PASSENGER.DEPARTURE_CITY,PASSENGER.LAST_NAME,PASSENGER.DESTINATION,
TICKET.MEAL_TYPE,FLIGHT.ARRIVAL_TIME
FROM PASSENGER,TICKET,FLIGHT
WHERE PASSENGER.TICKET_NUMBER=TICKET.TICKET_NUMBER
AND TICKET.FLIGHT_NUMBER=FLIGHT.FLIGHT_NUMBER;

Report message to a moderator

Re: query [message #371305 is a reply to message #371304] Sat, 30 September 2000 12:09 Go to previous messageGo to next message
christopher
Messages: 25
Registered: September 2000
Junior Member
This is what is coming up.

DEPARTURE_CITY LAST_NAME DESTINATION MEAL_TYPE ARRIVAL_TI
-------------------- --------------- -------------------- ---------- ----------
DENVER SMITH CHICAGO VEGETARIAN 10:30PM
DENVER ELWAY MIAMI STEAK 11:25PM
DENVER ELWAY MIAMI STEAK 11:25PM
DENVER JACKSON AUSTIN STEAK 1:30AM
DENVER BROWN HOUSTON STEAK 12:30AM

Report message to a moderator

Re: query [message #371310 is a reply to message #371304] Mon, 02 October 2000 12:35 Go to previous messageGo to next message
Bala
Messages: 205
Registered: November 1999
Senior Member
Hi,
One of the tables have multiple records.
The passenger table may have two passengers
with the same ticket number, so when you use
ticket_number to join you will get two records.

To avoid this use group by.

SELECT PASSENGER.DEPARTURE_CITY,PASSENGER.LAST_NAME,PASSENGER.DESTINATION,
TICKET.MEAL_TYPE,FLIGHT.ARRIVAL_TIME
FROM PASSENGER,TICKET,FLIGHT
WHERE PASSENGER.TICKET_NUMBER=TICKET.TICKET_NUMBER
AND TICKET.FLIGHT_NUMBER=FLIGHT.FLIGHT_NUMBER
GROUP BY
PASSENGER.DEPARTURE_CITY,PASSENGER.LAST_NAME,PASSENGER.DESTINATION,TICKET.MEAL_TYPE,FLIGHT.ARRIVAL_TIME
;

Thanks
Bala

Report message to a moderator

Re: query [message #371313 is a reply to message #371304] Mon, 02 October 2000 19:41 Go to previous messageGo to next message
christopher
Messages: 25
Registered: September 2000
Junior Member
Thanks Bala, But it didnt work because it didnt show both records. Here is the entire project. Thanks fro you input!

CREATE TABLE PASSENGER(
TICKET_NUMBER NUMBER(10),
DESTINATION VARCHAR2 (20),
DEPARTURE_CITY VARCHAR2 (20),
LAST_NAME VARCHAR2 (15));

CREATE TABLE TICKET(
TICKET_NUMBER NUMBER(10),
SEAT_NUMBER VARCHAR (5),
FLIGHT_NUMBER NUMBER (5),
MEAL_TYPE VARCHAR (10));

CREATE TABLE FLIGHT(
FLIGHT_NUMBER NUMBER(5),
AIRCRAFT_TYPE VARCHAR (5),
ARRIVAL_TIME VARCHAR (10),
GATE_NUMBER VARCHAR (5));

INSERT INTO PASSENGER VALUES(23456,'CHICAGO','DENVER','SMITH');
INSERT INTO PASSENGER VALUES(45598,'MIAMI','DENVER','ELWAY');
INSERT INTO PASSENGER VALUES(45598,'MIAMI','DENVER','DAVIS');
INSERT INTO PASSENGER VALUES(98495,'HOUSTON','DENVER','BROWN');
INSERT INTO PASSENGER VALUES(47485,'AUSTIN','DENVER','JACKSON');

INSERT INTO TICKET VALUES(23456,'5B',201,'VEGETARIAN');
INSERT INTO TICKET VALUES(45598,'3A',455,'STEAK');
INSERT INTO TICKET VALUES(45598,'3B',455,'VEGETARIAN');
INSERT INTO TICKET VALUES(98495,'6C',345,'STEAK');
INSERT INTO TICKET VALUES(47485,'2A',451,'STEAK');

INSERT INTO FLIGHT VALUES(201,'737','10:30PM','1A');
INSERT INTO FLIGHT VALUES(455,'MD11','11:25PM','4G');
INSERT INTO FLIGHT VALUES(455,'MD11','11:25PM','4G');
INSERT INTO FLIGHT VALUES(345,'727','12:30AM','7F');
INSERT INTO FLIGHT VALUES(451,'727','1:30AM','J11');

PART A

SELECT PASSENGER.DEPARTURE_CITY,PASSENGER.LAST_NAME,
PASSENGER.DESTINATION,TICKET.MEAL_TYPE,FLIGHT.ARRIVAL_TIME
FROM PASSENGER,TICKET,FLIGHT
WHERE PASSENGER.TICKET_NUMBER=TICKET.TICKET_NUMBER
AND TICKET.FLIGHT_NUMBER=FLIGHT.FLIGHT_NUMBER
GROUP BY PASSENGER.DEPARTURE_CITY,PASSENGER.LAST_NAME,PASSENGER.DESTINATION,
TICKET.MEAL_TYPE,FLIGHT.ARRIVAL_TIME;

SQL> SELECT PASSENGER.DEPARTURE_CITY,PASSENGER.LAST_NAME,
2 PASSENGER.DESTINATION,TICKET.MEAL_TYPE,FLIGHT.ARRIVAL_TIME
3 FROM PASSENGER,TICKET,FLIGHT
4 WHERE PASSENGER.TICKET_NUMBER=TICKET.TICKET_NUMBER
5 AND TICKET.FLIGHT_NUMBER=FLIGHT.FLIGHT_NUMBER
6 GROUP BY PASSENGER.DEPARTURE_CITY,PASSENGER.LAST_NAME,PASSENGER.DESTINATION,TICKET.MEAL_TYPE,FL
IGHT.ARRIVAL_TIME
7 ;

DEPARTURE_CITY LAST_NAME DESTINATION MEAL_TYPE ARRIVAL_TI
-------------------- --------------- -------------------- ---------- ----------
DENVER BROWN HOUSTON STEAK 12:30AM
DENVER ELWAY MIAMI STEAK 11:25PM
DENVER JACKSON AUSTIN STEAK 1:30AM
DENVER SMITH CHICAGO VEGETARIAN 10:30PM

Report message to a moderator

Re: query [message #371318 is a reply to message #371304] Tue, 03 October 2000 08:21 Go to previous message
Bala
Messages: 205
Registered: November 1999
Senior Member
Hi,

This is a design issue.
There is no unique key in the passenger table.
There are two passengers are traveling in the same ticket number 45598, this can be under stood only from the different last_name by looking at the passenger table.
But from the ticket table you can't tell who is who.

Either you can add unique column to the passenger table and use it as foreign key.
Or add last_name column in the ticket table also.

Thanks
Bala

Report message to a moderator

Previous Topic: Re: Regarding internal Mechanism of oracle
Next Topic: Change format of date in SQL View
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Apr 26 22:02:25 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.