Query help request 
Author Message
 Query help request

   I'm hoping someone here can point me in the right direction on this one.
I'm fairly new to SQL, so maybe I'm just missing something easy.  I'm using
MS SQL Server 6.5.

I am trying to display a course schedule for a college.  I have a fairly
simple SELECT statement that gives me the following...

course_id    title        subtype    section    days    time
PS101        Gen. Psych.    LECT    01    MWF    10:00am - 12:00pm
PS101        Gen. Psych.    LECT    01    T           9:00am - 11:00am

The problem is that this is really only one class.  If course_id, title,
subtype, and section are the same for two consecutive records, I would like
to leave those fields blank on the second one.  I can't actually change
anything in the database, though.

Any suggestions?

P.S. Before Celko goes off, I'd just like to say that I didn't design this
database.  I'm just working with what I've got.  :)



Mon, 31 Mar 2003 03:00:00 GMT
 Query help request

Join the table to itself and only specifiy the column from the second table
where equal columns exist.

Jeff

Quote:
>    I'm hoping someone here can point me in the right direction on this
one.
> I'm fairly new to SQL, so maybe I'm just missing something easy.  I'm
using
> MS SQL Server 6.5.

> I am trying to display a course schedule for a college.  I have a fairly
> simple SELECT statement that gives me the following...

> course_id    title        subtype    section    days    time
> PS101        Gen. Psych.    LECT    01    MWF    10:00am - 12:00pm
> PS101        Gen. Psych.    LECT    01    T           9:00am - 11:00am

> The problem is that this is really only one class.  If course_id, title,
> subtype, and section are the same for two consecutive records, I would
like
> to leave those fields blank on the second one.  I can't actually change
> anything in the database, though.

> Any suggestions?

> P.S. Before Celko goes off, I'd just like to say that I didn't design this
> database.  I'm just working with what I've got.  :)



Mon, 31 Mar 2003 03:00:00 GMT
 Query help request

Quote:
>>  I'm hoping someone here can point me in the right direction on this

one.  I'm fairly new to SQL, so maybe I'm just missing something easy.
I'm using MS SQL Server 6.5.

 I am trying to display a course schedule for a college.  I have a
fairly imple SELECT statement that gives me the following...

 course_id    title        subtype    section    days    time
 PS101        Gen. Psych.    LECT    01    MWF    10:00am - 12:00pm
 PS101        Gen. Psych.    LECT    01    T       9:00am - 11:00am

 The problem is that this is really only one class.  If course_id,
title, subtype, and section are the same for two consecutive records, I
would like to leave those fields blank on the second one.  I can't
actually change anything in the database, though. <<

That is a report and not a query.  SQL is a query language, not a
report writer. You are also using reserved words for column names.
Having said that this is a dumb idea, here is one way to do it:

SELECT CS1.course_id, CS1.title, CS1.subtype, CS1.section,
       CASE WHEN CS1.course_days
                 = (SELECT MIN(course_days)
                      FROM ClassSchedule AS CS2
                     WHERE CS1.course_id = CS2.course_id
                       AND CS1.title = CS2.title
                       AND CS1.subtype = CS2.subtype
                       AND CS1.section = CS2.section)
            THEN CS1.course_days ELSE '  ' END,
       CASE WHEN CS1.scheduled_time
                 = (SELECT MIN(course_days)
                      FROM ClassSchedule AS CS2
                     WHERE CS1.course_id = CS2.course_id
                       AND CS1.title = CS2.title
                       AND CS1.subtype = CS2.subtype
                       AND CS1.section = CS2.section)
            THEN CS1.scheduled_time ELSE '  ' END
  FROM ClassSchedule AS CS1;

Quote:
> P.S. Before Celko goes off, I'd just like to say that I didn't design

this database.  I'm just working with what I've got. <<

How do I know how bad the database was?  You never posted any DDL or
DML for me to see.  Nah, I am going bash you for the dumb query idea
instead <G> ...

--CELKO--
Joe Celko, SQL Guru & DBA at Trilogy
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc)
which can be cut and pasted into Query Analyzer is appreciated.

Sent via Deja.com http://www.deja.com/
Before you buy.



Mon, 31 Mar 2003 03:00:00 GMT
 Query help request

   Thanks for the quick response!  Sorry, but I'm still missing the big
picture here.  Is there any way you could expalin it further or maybe give
me an example of how it would work?

Thank you.

Dave


Quote:
> Join the table to itself and only specifiy the column from the second
table
> where equal columns exist.

> Jeff


> >    I'm hoping someone here can point me in the right direction on this
> one.
> > I'm fairly new to SQL, so maybe I'm just missing something easy.  I'm
> using
> > MS SQL Server 6.5.

> > I am trying to display a course schedule for a college.  I have a fairly
> > simple SELECT statement that gives me the following...

> > course_id    title        subtype    section    days    time
> > PS101        Gen. Psych.    LECT    01    MWF    10:00am - 12:00pm
> > PS101        Gen. Psych.    LECT    01    T           9:00am - 11:00am

> > The problem is that this is really only one class.  If course_id, title,
> > subtype, and section are the same for two consecutive records, I would
> like
> > to leave those fields blank on the second one.  I can't actually change
> > anything in the database, though.

> > Any suggestions?

> > P.S. Before Celko goes off, I'd just like to say that I didn't design
this
> > database.  I'm just working with what I've got.  :)



Mon, 31 Mar 2003 03:00:00 GMT
 Query help request

Use a report generator. If you have Access, create an mdb with tables linked
to your SQL Server database, and then switch to the Reports tab and create a
report. There are options for suppressing repeated data. There are many
other report generators available. It is also fairly easy to do in VB by
looping through a recordset.


Quote:
>    I'm hoping someone here can point me in the right direction on this
one.
> I'm fairly new to SQL, so maybe I'm just missing something easy.  I'm
using
> MS SQL Server 6.5.

> I am trying to display a course schedule for a college.  I have a fairly
> simple SELECT statement that gives me the following...

> course_id    title        subtype    section    days    time
> PS101        Gen. Psych.    LECT    01    MWF    10:00am - 12:00pm
> PS101        Gen. Psych.    LECT    01    T           9:00am - 11:00am

> The problem is that this is really only one class.  If course_id, title,
> subtype, and section are the same for two consecutive records, I would
like
> to leave those fields blank on the second one.  I can't actually change
> anything in the database, though.

> Any suggestions?

> P.S. Before Celko goes off, I'd just like to say that I didn't design this
> database.  I'm just working with what I've got.  :)



Tue, 01 Apr 2003 03:00:00 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Query help requested for Login information

2. Query Help Request

3. Query help request !

4. Query Help Request

5. Query Help request

6. Query Help Requested!

7. SQL QUERY Help Requested

8. Query Help Requested

9. Request help with English Query for Analysis Services

10. Urgent help request: for a VERY complex query

11. Requesting help with complicated query


 
Powered by phpBB® Forum Software