Re: Query to return each value only once

From: D Newsham <d_newsham_at_hotmail.com>
Date: 29 Mar 2003 02:53:21 -0800
Message-ID: <c883e8dd.0303290253.705ea2aa@posting.google.com>

Sybrand,

Thanks so much, it is returning exactly what I asked for. Now that I have this basic query, I need to pull information from other tables. When I try to add onto this query I am returned hundreds of records because the coursename is being repeated once for each set of records returned. Here are the table structures:

COURSEDATE

---

coursedate_id number(4) PK

session_no     number(4) FK references schedule
start_time     date
end_time       date

COURSE

---

course_id number(5) PK
course_name varchar2(100)

SCHEDULE

---

session_no     number(4)  PK
course_id      number(5)  FK references course

The result that I want - which I should have put in my original post, but I thought I could figure this out! - is:

session_no course_name start_time

What should I add to the query:

select session_no, start_time from coursedate c1 where start_time =
(select min(start_time) from coursedate c2 where c1.session_no = c2.session_no)
order by start_time

to get the course name returned once per session number, the results looking like:

202  Course Number 1    21-Aug-2003 0800 
203  Course Number 2    01-Sep-2003 1330     
204  Course Number 1    02-Sep-2003 0830 

This is what I have tried, without success:

select c1.session_no, c1.start_time, c.course_name from coursedate c1, course c, schedule s where start_time =
(select min(start_time) from coursedate c2 where c1.session_no = c2.session_no)
AND
s.course_id = c.course_id
order by start_time

This should return 12 records, and instead returns 144.

Thanks,
Dina

Sybrand Bakker <gooiditweg_at_nospam.demon.nl> wrote in message news:<8q398vou07ef8onnd93tg0icpt55lra7md_at_4ax.com>...
> On 28 Mar 2003 09:00:20 -0800, d_newsham_at_hotmail.com (D Newsham)
> wrote:
>
> >I have a table that is structured as:
> >
> >coursedate_id number(4) PK
> >session_no number(4)
> >start_time date
> >end_time date
> >
> >The data looks like this:
> >
> >10 202 21-Aug-2003 0800 21-Aug-2003 1200
> >11 202 22-Aug-2003 0800 22-Aug-2003 1200
> >12 202 23-Aug-2003 0800 23-Aug-2003 1200
> >13 203 01-Sep-2003 1330 01-Sep-2003 1430
> >14 204 02-Sep-2003 0830 02-Sep-2003 1200
> >15 204 03-Sep-2003 0830 03-Sep-2003 1200
> >
> >I can run this query which will bring back only the first of repeated
> >rows, but how can I change this to also return rows where the
> >session_no is only used once?
> >
> >select session_no, start_time from coursedate c1
> >where rowid not in
> >(select min(rowid) from coursedate c2
> >where c1.session_no = c2.session_no)
> >
> >returns:
> >202 21-Aug-2003 0800
> >204 02-Sep-2003 0830
> >
> >(session_no 203 is not returned because it only appears once in the
> >table)
> >
> >My goal is to return each session number ONCE, with the start time, so
> >the result would look like:
> >
> >202 21-Aug-2003 0800
> >203 01-Sep-2003 1330
> >204 02-Sep-2003 0830
> >
> >What do I need to add to this query to get these results?
>
>
> select session_no, start_time from coursedate c1
> >where start_time =
> >(select min(start_time) from coursedate c2
> where c1.session_no = c2.session_no)
> >
> >
> >Thanks,
> >Dina
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address
Received on Sat Mar 29 2003 - 04:53:21 CST