RE: Comparing two records within the query
Thanks for your help on this. I am pretty sure I have the query down as I need. I used a variation of your UNION'ed query. Since we don't have one ISDN for each title, I stripped the suffix off of the ID (-01 or -03) to artificially create a unique ID for each title. Thanks again! >From: "Cornillon, Matthieu (Consultant)" <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: CF-Talk <[EMAIL PROTECTED]> >Subject: RE: Comparing two records within the query >Date: Thu, 9 Sep 2004 11:54:22 -0400 > > >> Another option is to do a self-join. > > > 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. > > Unfortunately, you are running into a fundamental data identity issue >here. >Here, identity is "a particular movie, regardless of format". The goal of >your application is to return a list of identities (as defined above), with >available format information included. > >If you had some unique identifier (similar to ISBN) for movies, something >that said 002943018 = "What's Eating Gilbert Grape?" regardless of whether >it was VHS, Beta, DVD, Laserdisc, or celluloid, you could do this >comparison >flawlessly and easily. Unfortunately, I am guessing that you have no such >unique identifier, and further that no such identifier even exists. > >(Interesting idea: use IMDBs data. They identify "What's Eating Gilbert >Grape" as tt0108550. If you used this, you could even dynamically generate >links to IMDB for more information. You would have to create a table of >titles and these unique IDs somehow, and this would be a bunch of work, >but--as I said--interesting idea. If you link to them, they might not even >mind, in which case you could ask them to send you a list of titles and >IDs. >Again, just a brainstorm.) > >Back to the issue at hand: if you had such an identifier, you would be all >set. But you don't. So, you are using title, which is pretty good. But >you have problems. "The Manchurian Candidate" is not good as an identity: >it could mean either the 1962 or the 2004 version. Also, you have this >format data appended. > >I recommend that you add a column to the table that is "cleaned title". >You >then cycle through the database and remove that VHS and DVD note. While >you >do it, you could create a mediaformat column as suggested before. You >could >leave the original title as is, if you want, or go to the cleaned title. I >see no way around doing at least this. > >Of course, once you have done that, the question is why you don't have a >relational database with one table that holds videos where each row is a >unique identity, and another table that holds stock, indicating, among >other >things, what media format the stock item is. > >If you don't split the table like this, I would still recommend that you >create some sort of internal unique ID that you use to identify a movie >entity. Others might know more on this issue, but my guess is that >comparing titles (even after the cleaning described above) will be slower >than comparing numeric values, thereby leading to faster joins. > >HTH, >Matthieu > > > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: Comparing two records within the query
>> Another option is to do a self-join. > 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. Unfortunately, you are running into a fundamental data identity issue here. Here, identity is "a particular movie, regardless of format". The goal of your application is to return a list of identities (as defined above), with available format information included. If you had some unique identifier (similar to ISBN) for movies, something that said 002943018 = "What's Eating Gilbert Grape?" regardless of whether it was VHS, Beta, DVD, Laserdisc, or celluloid, you could do this comparison flawlessly and easily. Unfortunately, I am guessing that you have no such unique identifier, and further that no such identifier even exists. (Interesting idea: use IMDBs data. They identify "What's Eating Gilbert Grape" as tt0108550. If you used this, you could even dynamically generate links to IMDB for more information. You would have to create a table of titles and these unique IDs somehow, and this would be a bunch of work, but--as I said--interesting idea. If you link to them, they might not even mind, in which case you could ask them to send you a list of titles and IDs. Again, just a brainstorm.) Back to the issue at hand: if you had such an identifier, you would be all set. But you don't. So, you are using title, which is pretty good. But you have problems. "The Manchurian Candidate" is not good as an identity: it could mean either the 1962 or the 2004 version. Also, you have this format data appended. I recommend that you add a column to the table that is "cleaned title". You then cycle through the database and remove that VHS and DVD note. While you do it, you could create a mediaformat column as suggested before. You could leave the original title as is, if you want, or go to the cleaned title. I see no way around doing at least this. Of course, once you have done that, the question is why you don't have a relational database with one table that holds videos where each row is a unique identity, and another table that holds stock, indicating, among other things, what media format the stock item is. If you don't split the table like this, I would still recommend that you create some sort of internal unique ID that you use to identify a movie entity. Others might know more on this issue, but my guess is that comparing titles (even after the cleaning described above) will be slower than comparing numeric values, thereby leading to faster joins. HTH, Matthieu [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: Comparing two records within the query
Yes, this is exactly the thing I'm looking for. 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]
RE: Comparing two records within the query
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]
Re: Comparing two records within the query
Are you using sqlserver? Aren't there some substring functions? If the 01 and 03 indicate the media type and the number before the dash will match, couldn't you do something like this pseudocode: select left(id, 4) AS primaryID, right(id, 2) AS mediatype, othercolumnshere from yourtable #name# DVD 3>VHS - Original Message - From: "B G" < > That may be possible. Though it's not always up to me to get the marketing > dept and the DB people to do work so my life is easier. > > Nonetheless...The basic nature of this kind of compare is eluding me. Even > if I had two records, one with DVD and one with VHS in the MEDIA_TYPE field, > I'd still need to have some way to identify that while displaying the > records. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: Comparing two records within the query
It's probably too late for this, but I'd have a table just for the movies themselves, and another table (one-to-many) where you match up the movies with the various formats (a row for movie 1 in VHS, another row for movie 1 in DVD, etc). In addition to making your work easier, when the next big format comes around, it's easy to take into account. Scott -- - Scott Brady http://www.scottbrady.net/ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: Comparing two records within the query
How about ... A) query for all records B) create a new query object (adding some columns) C) loop through original query (and perhaps the new query, nested within it) and derive the result set you need to display your form to the user. maybe the new result set has a boolean to display/not display the radio buttons. Maybe you'd use a combination of both queries in your form to display and set the values of the choices. That's how i'd try and approach it. i'll let you think it through to the end. On Tue, 07 Sep 2004 17:35:24 -0500, B G <[EMAIL PROTECTED]> wrote: > That may be possible. Though it's not always up to me to get the marketing > dept and the DB people to do work so my life is easier. > > Nonetheless...The basic nature of this kind of compare is eluding me. Even > if I had two records, one with DVD and one with VHS in the MEDIA_TYPE field, > I'd still need to have some way to identify that while displaying the > records. > > >From: "Bryan Stevenson" <[EMAIL PROTECTED]> > >Reply-To: [EMAIL PROTECTED] > >To: CF-Talk <[EMAIL PROTECTED]> > >Subject: Re: Comparing two records within the query > >Date: Tue, 7 Sep 2004 13:52:17 -0700 > > > >I'd strongly advise you add a column called Media_Type and use VHS or DVD > >as a media type codethat will make your life much easier ;-) > > > >Bryan Stevenson B.Comm. > >VP & Director of E-Commerce Development > >Electric Edge Systems Group Inc. > >phone: 250.480.0642 > >fax: 250.480.1264 > >cell: 250.920.8830 > >e-mail: [EMAIL PROTECTED] > >web: www.electricedgesystems.com > > > > > > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: Comparing two records within the query
That may be possible. Though it's not always up to me to get the marketing dept and the DB people to do work so my life is easier. Nonetheless...The basic nature of this kind of compare is eluding me. Even if I had two records, one with DVD and one with VHS in the MEDIA_TYPE field, I'd still need to have some way to identify that while displaying the records. >From: "Bryan Stevenson" <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: CF-Talk <[EMAIL PROTECTED]> >Subject: Re: Comparing two records within the query >Date: Tue, 7 Sep 2004 13:52:17 -0700 > >I'd strongly advise you add a column called Media_Type and use VHS or DVD >as a media type codethat will make your life much easier ;-) > >Bryan Stevenson B.Comm. >VP & Director of E-Commerce Development >Electric Edge Systems Group Inc. >phone: 250.480.0642 >fax: 250.480.1264 >cell: 250.920.8830 >e-mail: [EMAIL PROTECTED] >web: www.electricedgesystems.com > > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
OT (was "Re: Comparing two records within the query")
On Sep 7, 2004, at 4:48 PM, B G wrote: > For example -01 = The Teletubbies meet Jason (VHS) -03 = The > Teletubbies meet Jason (DVD). Personally, I can't wait for the sequel, The Teletubbies vs Jason vs Santa Claus. -- Damien McKenna - Web Developer - [EMAIL PROTECTED] The Limu Company - http://www.thelimucompany.com/ - 407-804-1014 "Nothing endures but change." - Heraclitus [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: Comparing two records within the query
I'd strongly advise you add a column called Media_Type and use VHS or DVD as a media type codethat will make your life much easier ;-) Bryan Stevenson B.Comm. VP & Director of E-Commerce Development Electric Edge Systems Group Inc. phone: 250.480.0642 fax: 250.480.1264 cell: 250.920.8830 e-mail: [EMAIL PROTECTED] web: www.electricedgesystems.com [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
