This works, though I am not getting all rows returned and I haven't quite
figured why yet. I hate to rely on the titles being exactly the same. I
have data where one title contains the words DVD while the VHS version
doesn't.
Thanks
>From: "Cornillon, Matthieu (Consultant)" <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: CF-Talk <[EMAIL PROTECTED]>
>Subject: RE: Comparing two records within the query
>Date: Wed, 8 Sep 2004 13:12:36 -0400
>
>Another option is to do a self-join. I'll make some assumptions here that
>may not be true, but which you can modify to fit the truth. Key among
>those
>is that you have column called VID_MediaFormat holding either DVD or VHS.
>(I know you don't have this, but you seem to have some way of determining
>media_type, so you can just edit this to fit.)
>
>SELECT V1.pkVID AS pkVID_DVD, V1.VID_Title AS VID_Title_DVD, V2.pkVID AS
>pkVID_VHS, V2.VID_Title AS VID_Title_VHS, V1.VID_Title AS SortTitle
>FROM VIDEOS V1, VIDEOS V2
>WHERE V1.VID_MediaFormat = 'DVD' AND
> V2.VID_MediaFormat = 'VHS' AND
> V1.pkVID <> V2.pkVID AND
> V1.VID_Title = V2.VID_Title (+)
>
>UNION
>
>SELECT V1.pkVID AS pkVID_DVD, V1.VID_Title AS VID_Title_DVD, V2.pkVID AS
>pkVID_VHS, V2.VID_Title AS VID_Title_VHS, V2.VID_Title AS SortTitle
>WHERE V1.VID_MediaFormat = 'DVD' AND
> V2.VID_MediaFormat = 'VHS' AND
> V1.pkVID <> V2.pkVID AND
> V2.VID_Title = V1.VID_Title (+) AND
> V1.VID_Title IS NULL
>
>ORDER BY SortTitle
>
>Here's how it works. The first SELECT statement pulls out all DVD titles
>in
>the column called VID_Title_DVD, with matching VHS titles (if they exist)
>in
>VID_Title_VHS. The second SELECT statement puts all VHS titles WITHOUT a
>matching DVD title into VID_Title_VHS, and leaves VID_Title_DVD null. So,
>the first half gets titles with DVD only or DVD and VHS, and the second
>half
>gets VHS only. Finally, the ORDER BY clause uses the SortTitle column
>(which is the same as VID_Title_DVD in the first SELECT statement and the
>same as VID_Title_VHS in the second SELECT statement) to order it together.
>
>It should return a results list with two columns (plus other stuff) like
>this:
>
>VID_Title_DVD VID_Title_VHS
>Arthur (DVD) Arthur(VHS)
>Benji (DVD) Benji (VHS)
> CHUD (VHS)
>Dave (DVD)
>ET (DVD) ET (VHS)
>Fresh (DVD)
>Go (DVD)
>Heat (DVD) Heat(VHS)
> Ishtar (VHS)
>Juice (DVD) Juice (VHS)
>
>Processing this on output should be easy.
>
>HTH,
>Matthieu
>
>
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
