>From: Chris W [mailto:[EMAIL PROTECTED] >Sent: Wednesday, May 21, 2008 12:25 PM >To: Jerry Schwartz >Cc: mysql@lists.mysql.com >Subject: Re: Match/No Match query > >Jerry Schwartz wrote: >> I have a list of codes. Some, but not all, of these codes will match >the >> entries in a product table. Here's what the data would look like: >> >> List of Codes: >> AAAA >> BBBB >> CCCC >> >> The rows in the product table look like >> >> prod_num code >> ======== ==== >> 222 AAAA >> 333 CCCC >> >> What I want to is get a list of ALL of the codes, with the associated >> prod_num if it exists or a flag if it does not: >> >> code prod_num >> ==== ======== >> AAAA 222 >> BBBB xxx >> CCCC 333 >> >> I need to preserve the "empty" rows in order to match the data against >an >> Excel worksheet (which is where the list of codes came from). >> >> I have done this by putting all of the codes into a temporary table >and >> doing a LEFT JOIN against the product table. Works fine, lasts a long >time. >> However, it seems that I ought to be able to do this without the >temporary >> table, by using derived tables. I just can't figure out how. >> > > >This would be easier if you gave your table structure. But something >like this would work >SELECT c.code, p.prod_num >FROM CodeTable c >LEFT OUTER JOIN ProductNumTable p USING (code) > [JS] That is what I am doing now. I was wondering if I could eliminate what you have designated as "CodeTable", and do this all in a single (probably nested) query. > >This will return null for prod_num if there is no association in the >ProductNumTable. Not having more details on your data I can't say for >sure but I am guessing a group by *might* be needed. > > > >-- >Chris W >KE5GIX > >"Protect your digital freedom and privacy, eliminate DRM, >learn more at http://www.defectivebydesign.org/what_is_drm"; > >Ham Radio Repeater Database. >http://hrrdb.com
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
