Page 1 of 2 12 LastLast
Results 1 to 15 of 20

Thread: Query help?

  1. #1
    Join Date
    Apr 2011
    Posts
    40

    Unanswered: Query help?

    hi all
    I was trying to get a Query but couldn't managed, can anybody help me with this?

    Listst for each trip, the trip code, the holiday code, the holiday name, and how many staff members are assigned to the trip
    CREATE TABLE holidays
    (holiday_code VARCHAR2(4) NOT NULL,
    holiday_name VARCHAR2(25) NOT NULL,
    no_of_participants NUMBER(30,0) NOT NULL,
    CONSTRAINT "HIY_CODE_PK" PRIMARY KEY ("HOLIDAY_CODE") ENABLE,
    CONSTRAINT "HIY_NAME_UK" UNIQUE ("HOLIDAY_NAME") ENABLE,
    CONSTRAINT "HIY_MIN_MAX_PARTICIPANTS" CHECK (no_of_participants BETWEEN 5AND 30));



    CREATE TABLE trips
    (trip_code VARCHAR2(4) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    price NUMBER(7,2) NOT NULL,
    holiday_code VARCHAR2(4) NOT NULL,
    CONSTRAINT "TRP_CODE_PK" PRIMARY KEY ("TRIP_CODE") ENABLE);

    CREATE TABLE staff_trips
    (trip_code VARCHAR2(4) NOT NULL,
    staff_number VARCHAR2(4) NOT NULL,
    jobe_role VARCHAR2(25) NOT NULL,
    CONSTRAINT "STP_PK" PRIMARY KEY ("TRIP_CODE", "STAFF_NUMBER") ENABLE);
    SELECT t.trip_code, h.holiday_code, h.holiday_name,COUNT(*) (FROM staff_trips GROUP BY trip_code) AS number of staff

    from trips t inner join holidays h

    ON (t.holiday_code = h.holiday_code)

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by arayici View Post
    (FROM staff_trips GROUP BY trip_code) AS number of staff
    this is horrible

    you cannot assign an alias to a FROM clause

    look up the SELECT syntax in da manual for further help in structuring your query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2011
    Posts
    40
    Quote Originally Posted by r937 View Post
    this is horrible

    you cannot assign an alias to a FROM clause

    look up the SELECT syntax in da manual for further help in structuring your query
    ok i managed the get them in two separate part. Problem is how to join them together?


    this one gets the trip code, holiday code ant the holiday name!
    SELECT t.trip_code, h.holiday_code, h.holiday_name
    FROM trips t,holidays h
    where t.holiday_code = h.holiday_code
    this one gets how many staff assign to each trip
    SELECT trip_code, COUNT(*) FROM staff_trips GROUP BY trip_code
    any help please?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it looks like each holiday can have multiple trips, but each trip belongs to only one holiday

    therefore you should be able to join all three tables and still use the GROUP BY to get accurate counts
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2011
    Posts
    40
    Quote Originally Posted by r937 View Post
    it looks like each holiday can have multiple trips, but each trip belongs to only one holiday

    therefore you should be able to join all three tables and still use the GROUP BY to get accurate counts
    yes that's right each holiday can have multiple trips, but each trip belongs to only one holiday


    I am trying but still no luck

    SELECT t.trip_code, h.holiday_code, h.holiday_name,COUNT(r.trip_code) AS number of staff
    FROM trips t, holidays h join staff_trips r
    where t.holiday_code = h.holiday_code
    group by r.trip_code

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    first. don't mix comma style joins with explicit JOIN syntax -- use only JOIN syntax

    second, match your SELECT columns with your GROUP BY columns
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2011
    Posts
    40
    Quote Originally Posted by r937 View Post
    first. don't mix comma style joins with explicit JOIN syntax -- use only JOIN syntax

    second, match your SELECT columns with your GROUP BY columns
    Code:
    SELECT t.trip_code, h.holiday_code, h.holiday_name,COUNT(r.trip_code) AS number of staff
    FROM trips t inner  join holidays h join staff_trips r
    where t.holiday_code = h.holiday_code
    group by t.trip_code

    ORA-00923: FROM keyword not found where expected

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    SELECT t.trip_code, h.holiday_code, h.holiday_name,COUNT(r.trip_code) AS number_of_staff
    FROM trips t inner join holidays h join staff_trips r
    where t.holiday_code = h.holiday_code
    group by t.trip_code
    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.

  9. #9
    Join Date
    Apr 2011
    Posts
    40
    Quote Originally Posted by anacedent View Post
    SELECT t.trip_code, h.holiday_code, h.holiday_name,COUNT(r.trip_code) AS number_of_staff
    FROM trips t inner join holidays h join staff_trips r
    where t.holiday_code = h.holiday_code
    group by t.trip_code
    ORA-00905: missing keyword

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    what is JOIN condition for STAFF_TRIPS?
    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.

  11. #11
    Join Date
    Apr 2011
    Posts
    40
    Quote Originally Posted by anacedent View Post
    what is JOIN condition for STAFF_TRIPS?
    I just JOIN that to get count (r.trip_code) from it

  12. #12
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I just JOIN that to get count (r.trip_code) from it
    which rows get counted?
    Do all rows in STAFF_TRIPS get counted?
    if not every row in STAFF_TRIPS, which rows get counted?
    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.

  13. #13
    Join Date
    Apr 2011
    Posts
    40
    Quote Originally Posted by anacedent View Post
    >I just JOIN that to get count (r.trip_code) from it
    which rows get counted?
    Do all rows in STAFF_TRIPS get counted?
    if not every row in STAFF_TRIPS, which rows get counted?
    each holiday can have multiple trips, but each trip belongs to only one holiday

    i am trying to count TRIP_CODE as i am trying to count how many for per TRIP (5 for T001, 4 for T002)

    Code:
    Listst for each trip, the trip code, the holiday code, the holiday name, and how many staff members are assigned to the trip
    Code:
    TRIP_CODE	STAFF_NUMBER	JOBE_ROLE
    T001	S001	Leader
    T001	S002	Driver
    T001	S003	Cook
    T001	S004	Guide
    T001	S005	Instructer
    T002	S002	Leader
    T002	S006	Instructer
    T002	S007	Driver
    T003	S001	Leader
    T003	S008	Instructer
    T003	S009	Driver
    T004	S001	Leader
    T004	S008	Instructer
    T004	S010	Driver
    T005	S002	Leader
    T005	S007	Instructer
    T005	S010	Driver
    T005	S006	Cook
    T006	S003	Leader
    T006	S004	Instructer

  14. #14
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >i am trying to count TRIP_CODE as i am trying to count how many for per TRIP (5 for T001, 4 for T002)
    I already knew that.
    You don't need to tell me the requirement.
    You need to implement this requirement as part of the SQL statement.
    Does TRIPS.TRIP_ID need to match STAFF_TRIPS.TRIP_ID to be counted?
    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.

  15. #15
    Join Date
    Apr 2011
    Posts
    40
    Quote Originally Posted by anacedent View Post
    >i am trying to count TRIP_CODE as i am trying to count how many for per TRIP (5 for T001, 4 for T002)
    I already knew that.
    You don't need to tell me the requirement.
    You need to implement this requirement as part of the SQL statement.
    Does TRIPS.TRIP_ID need to match STAFF_TRIPS.TRIP_ID to be counted?
    yes it does need to match

Posting Permissions

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