Match/No Match query
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: The rows in the product table look like prod_num code 222 333 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 222 xxx 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 is mostly a curiosity question. Thanks. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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: The rows in the product table look like prod_num code 222 333 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 222 xxx 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]
RE: Match/No Match query
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: The rows in the product table look like prod_num code 222 333 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 222 xxx 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]
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: The rows in the product table look like prod_num code 222 333 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 222 xxx 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]
RE: Match/No Match query
chris, you're going to need a source for all the distinct codes that you may see in your product tableie: 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: The rows in the product table look like prod_num code 222 333 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 222 xxx 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]
RE: Match/No Match query
-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: The rows in the product table look like prod_num code 222 333 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 222 xxx 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]
RE: Match/No Match query
-Original Message- From: Yong Lee [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 21, 2008 3:10 PM To: 'Chris W'; 'Jerry Schwartz'; 'MYSQL General List' Subject: RE: Match/No Match query chris, you're going to need a source for all the distinct codes that you may see in your product tableie: 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. [JS] That's the conclusion I reached, but I wasn't sure that there wasn't some trick I was missing. 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; [JS] Actually, I don't want distinct values; I'm actually looking for duplicates, so I use GROUP BY ... HAVING Thanks for confirming my conclusions. Yong. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
