Re: Return extra columns in a SELECT

From: richardb (richardb_at_discussions.microsoft.com)
Date: 06/14/04 

Date: Sun, 13 Jun 2004 17:56:01 -0700

I will look for your book.

Could you please help me understand one more level of my problem. Let's say that the table testCoverage links via the CarrCode to another table called Carriers (JOIN tc.CarrCode = Carriers.CarrCode). I want to return the CarrierName column from Carriers. Thanks.

RichardB

"Tom Moreau" wrote:

> You mean other than the one I wrote? ;-)
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
> ..
> "richardb" <richardb@discussions.microsoft.com> wrote in message
> news:7CBCCE1D-CF6A-499A-93DC-58B270070DE9@microsoft.com...
> Tom, It was really thrilling to see my tables appear, exactly what I wanted.
> I'm going to be able to adapt this technique elsewhere. However, can you
> recommend a book with a nice structured approach to learnng these advanced
> (nested) SELECT technqiues?
>
> RichardB
>
> "Tom Moreau" wrote:
>
> > Your INSERT statements have many syntax errors. Please test before
> posting.
> > Also, you can merge the APT_DATE and START_TIME into a single column.
> > Here's your solution:
> >
> > select
> > ta.APPT_DATE
> > , ta.START_TIME
> > , ta.STATUS
> > , tp.CLIENT_NAME
> > , tr.RESOURCE_NAME
> > , tc.CARRIER_CODE
> > , tc.Subscriber_no
> > , tc.plan_number
> > from
> > TestAppointment ta
> > join TestPatient tp on tp.CLIENT_ID = ta.CLIENT_ID
> > join TestResource tr on tr.RESOURCE_ID = ta.RESOURCE_ID
> > left
> > join TestCoverage tc on tc.CLIENT_ID = ta.CLIENT_ID
> > and tc.plan_status = 'A'
> > and tc.plan_number =
> > (
> > select
> > min (tc2.plan_number)
> > from
> > TestCoverage tc2
> > where
> > tc2.CLIENT_ID = tc.CLIENT_ID
> > and tc2.plan_status = 'A')
> > where
> > ta.APPT_DATE BETWEEN '01/01/2004' AND '01/31/2004'
> > AND ta.STATUS < 200.
> >
> >
> > --
> > Tom
> >
> > ----------------------------------------------------
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinnaclepublishing.com/sql
> > ..
> > "richardb" <richardb@discussions.microsoft.com> wrote in message
> > news:4EAF78A2-4E85-4824-9640-3DE34C047F75@microsoft.com...
> > Aaron, Here are the statements to provide sample tables and data for the
> > problem:
> >
> >
> > --------------------------------------------------------------------------
> --
> > -------------------
> > CREATE TABLE [TestAppointment] (
> > [APPT_DATE] [datetime] NULL ,
> > [RESOURCE_ID] [int] NOT NULL ,
> > [START_TIME] [datetime] NULL ,
> > [STATUS] [smallint] NOT NULL ,
> > [CLIENT_ID] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [ts_timestamp] [datetime] NULL CONSTRAINT [DF__Appointme__ts_ti__023D5A04]
> > DEFAULT (getdate()),
> > [ts_user] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> > ) ON [PRIMARY]
> > GO
> >
> > INSERT INTO TestAppointment (APPT_DATE, RESOURCE_ID, START_TIME,
> > STATUS, CLIENT_ID)
> >
> > VALUES (01/15/2004, 01, 12:30 PM, 100, 13AAAAAA)
> > VALUES (01/15/2004, 01, 01:30 pm, 100, 14AAAAAA)
> >
> > CREATE TABLE TestPatient (
> > CLIENT_ID [varchar] (8) NOT NULL ,
> > CLIENT_NAME [varchar] (50) NULL
> > ) ON [PRIMARY]
> > GO
> >
> > INSERT INTO TestPatient (CLIENT_ID, CLIENT_NAME)
> > VALUES (13AAAAAA, Mary Jones)
> > VALUES (14AAAAAA, Jim Smith)
> >
> > CREATE TABLE TestCoverage (
> > CLIENT_ID [varchar] (8) NOT NULL,
> > CARRIER_CODE [varchar] (5) NULL ,
> > Subscriber_no [varchar] (15) NULL ,
> > [plan_number] [smallint] NOT NULL ,
> > [plan_status] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> > ) ON [PRIMARY]
> > GO
> >
> > INSERT INTO TestCoverage (CLIENT_ID, CARRIER_CODE, Subscriber_no,
> > plan_number, plan_status)
> > VALUES (13AAAAAA, MCR, 11223344, 1, A)
> > VALUES (13AAAAAA, GHI, 34567890, 2, A)
> > VALUES (14AAAAAA, BC1, ABC12345, 1, I)
> > VALUES (14AAAAAA, AET, XYZ98765, 2, A)
> >
> > CREATE TABLE TestResource (
> > RESOURCE_ID [int] NOT NULL ,
> > RESOURCE_NAME [varchar] (35) ,
> > ) ON [PRIMARY]
> >
> > INSERT INTO TestResource (RESOURCE_ID, RESOURCE_NAME)
> > VALUES (01, Richard Smith)
> >
> > --------------------------------------------------------------------------
> --
> > ------------------------------------------
> > TestAppointment, TestPatient and TestCoverage are joined by the CLIENT_ID.
> > TestResource is joined to TestAppointment by the RESOURCE_ID.
> >
> > Here's what I want to return in the SELECT:
> >
> > Every row in TestAppointments where APPT_DATE BETWEEN '01/01/2004' AND
> > '01/31/2004' AND STATUS < 200.
> > Columns to return are APPT_DATE, START_TIME, STATUS.
> > In addition I want the CLIENT_NAME from TestPatient and the Resource_Name
> > from TestResource.
> >
> > Now here's the tricky part:
> >
> > I also want the row from TestCoverage with the lowest (min) value of
> > plan_number where plan_status = 'A'. I want these columns returned:
> > CARRIER_CODE, Subscriber_no, plan_number.
> >
> > I hope that I have now provided the tools needed to help me learn this
> > coding technique. Thank you very much.
> >
> > rb
> >
> >
> >
> >
> >
> >
> > "richardb" wrote:
> >
> > > Aaron -- OK I may be finally catching on. As soon as I have something,
> > I'll get back in touch regarding how to send it to you. Please continue to
> > monitor this thread.
> > >
> >
> >
>
>

Relevant Pages
Loading