>-----Original Message----- >From: Chris W [mailto:[EMAIL PROTECTED] >Sent: Wednesday, May 21, 2008 1:14 PM >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. > [JS] Here's the thing. There is only one table in the data base. So far as this matter is concerned, it has two columns: prod_num and code. What I thought might be possible is to derive a table from a list of codes. That might not be possible.
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
