chris, you're going to need a source for all the distinct codes that you may see in your product table....ie: if you don't have a full list somewhere (like your temp table) you cannot do your query because you have nothing to compare against.
assuming you had some table X which had a list of all the possible codes and you could generate the complete list by doing something like : select distinct code from X; you could use this in your sql statement like : select p.prod_num, p.code from products p left join (select distinct code from X) as codes on p.code = codes.code; Yong. -----Original Message----- From: Chris W [mailto:[EMAIL PROTECTED] Sent: May 21, 2008 10:14 AM To: Jerry Schwartz; MYSQL General List Subject: Re: Match/No Match query Jerry Schwartz wrote: >> 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. > In that case I must be missing something. What data do you have in the database that can be used to create the result. Some table structure would help and some more sample data that you want to use. -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
