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]
