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)
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]