Help with SQL (Oracle) Database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with SQL (Oracle) Database

Author

Topic

ls7897
Starting Member

1 Post

Posted - 2011-02-20 : 15:30:21

I need a little help! I've just started to learn SQL.

I've created 7 tables, and inserted primary and foreign keys, but for some reason, I'm not getting the expected results when using a select query.

I'll give an example...


CREATE TABLE bookings
(booking_id NUMBER(5) PRIMARY KEY,
start_date DATE,
end_date DATE);

CREATE TABLE groups
(group_id NUMBER(6) PRIMARY KEY,
group_name VARCHAR2(30),
number_in_group NUMBER(4) CHECK (number_in_group > 3),
booking_id NUMBER(5) NOT NULL,
group_leader CHAR(30) NOT NULL,
FOREIGN KEY (booking_id) REFERENCES bookings(booking_id));

If I do this, insert data and run the following select statement...

select groups.group_id, groups.booking_id, bookings.start_date
from groups, bookings;

I'm getting results that are not correct, for example, one group_id has one start_date, but the data is just not matching!

Any help! I'm so confused! :D

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-02-20 : 16:00:40

This forum is for Microsoft SQL Server. Not many people here are going to know Oracle. You may be better asking this on an Oracle forum

But the answer to your question is that you haven't joined the tables. I have no idea what the join syntax is for Oracle, but you must tell the DB engine how the two tables are related. In the query you give, you haven't done that, so it returns you a cartesian product of the two tablle

--
Gail Shaw
SQL Server MVP

Devart
Posting Yak Master

102 Posts

Posted - 2011-02-21 : 03:05:22

Hello,

SELECT groups.group_id, groups.booking_id, bookings.start_date
FROM groups, bookings
WHERE groups.booking_id=bookings.booking_id;

OR

SELECT groups.group_id, groups.booking_id, bookings.start_date
FROM groups INNER JOIN bookings
ON groups.booking_id=bookings.booking_id;

Devart,
SQL Server Tools:
dbForge Data Studio
dbForge Schema Compare
dbForge Data Compare
dbForge SQL Complete

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources