Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2011
    Posts
    9

    Unanswered: Help - Newby using Oracle

    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!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I'm getting results that are not correct, for example, one group_id has one start_date, but the data is just not matching!

    Without any join criteria (WHERE clause) the result set is Cartesian Product.
    When posting it is help if you use COPY & PASTE so we can see what you do & how Oracle responds.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Feb 2011
    Posts
    9
    Okay, I'll try and give as much information as possible...

    Bookings
    booking_id ----- start_date ----- end_date
    1.......................01-APR-11........14-APR-11
    2.......................12-APR-11........19-APR-11
    3.......................21-APR-11........28-APR-11

    Groups
    group_id ----- booking_id -----
    20..................1
    21..................2
    22..................3


    What I want is to select group_id and to be able to show the start date
    I've tried

    select groups.group_id, groups.booking_id, bookings.start_date
    from groups, bookings
    where groups.group_id = 21;

    I expected to get a result of

    group_id........booking_id.......start_date
    21.................2.....................12-APR-11

    but I'm getting

    group_id........booking_id.......start_date
    21...................2....................01-APR-11
    21...................2....................12-APR-11
    21...................2....................21-APR-11


    Any ideas?
    Last edited by ls7897; 02-20-11 at 17:05.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    folks here speak SQL.
    post DDL to create tables
    post DML (INSERT) to populate tables with test data.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    select groups.group_id, groups.booking_id, bookings.start_date
    from groups gg, bookings bb
    where groups.group_id = 21
    and bb.booking_id = gg.booking_id;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Feb 2011
    Posts
    9
    Thank you!

    I just tried this with the

    inner join statement as well and it seems to work. I believe what you wrote is another way of writing a joing statement?

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Yes, it is.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •