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]
