Re: SELECT missing records
Try this SELECT prod.prod_num, price.prod_price FROM prod JOIN price WHERE prod.prod_id = price.prod_id AND price.prod_curr !='YEN'; On 7/12/07, Jerry Schwartz [EMAIL PROTECTED] wrote: I've been banging my head against the walls for hours, so I hope somebody can help. I know similar questions have been answered in the past. I have two tables, prod and price. Stripping out the non-essential fields, they are pretty simple: prod --- prod_num (int) prod_id (char 15) price - prod_price_id (char 15) prod_id (char 15) prod_price (decimal 10,2) prod_curr (varchar 10) Here's what I need to do: Find every prod.prod_num that has a corresponding price.prod_curr = USD but does NOT have a corresponding price.prod_curr = YEN. A product might have a price row with price.prod_curr = GBP or some other currency, so a product might 1, 2, 3, or more prices. I believe this query will do it, but can it be redone without the sub-query by using JOINs? Would that be more efficient? SELECT prod.prod_num, price.prod_price FROM prod JOIN price WHERE prod.prod_id = price.prod_id AND price.prod_id NOT IN (SELECT price.prod_id FROM price WHERE price.prod_curr = YEN); 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 http://www.the-infoshop.com/ www.giiexpress.com http://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: SELECT missing records
On 7/12/07, Jerry Schwartz [EMAIL PROTECTED] wrote: I believe this query will do it, but can it be redone without the sub-query by using JOINs? Yes, use a LEFT JOIN. Would that be more efficient? Yes. SELECT prod.prod_num, price.prod_price FROM prod JOIN price WHERE prod.prod_id = price.prod_id AND price.prod_id NOT IN (SELECT price.prod_id FROM price WHERE price.prod_curr = YEN); I don't think this does what you want. You said you wanted every product with a USD price but no YEN price. This one does that: SELECT prod.prod_num, usd_price.prod_price FROM prod JOIN price usd_price ON (prod.prod_id= usd_price.prod_id AND usd_price.prod_curr = 'USD') LEFT JOIN price yen_price ON (prod.prod_id= yen_price.prod_id AND yen_price.prod_curr = 'YEN') WHEREyen_price.prod_id IS NULL - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SELECT missing records
I think that will give me one record for every price that is not Yen, so if a product has a price in USD and a price in GBP it will show up twice. A GROUP BY might help, I'll have to chew on that. It seems too simple. 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 http://www.the-infoshop.com/ www.giiexpress.com http://www.giiexpress.com/ www.etudes-marche.com _ From: Ananda Kumar [mailto:[EMAIL PROTECTED] Sent: Thursday, July 12, 2007 12:05 PM To: Jerry Schwartz Cc: mysql@lists.mysql.com Subject: Re: SELECT missing records Try this SELECT prod.prod_num, price.prod_price FROM prod JOIN price WHERE prod.prod_id = price.prod_id AND price.prod_curr !='YEN'; On 7/12/07, Jerry Schwartz [EMAIL PROTECTED] wrote: I've been banging my head against the walls for hours, so I hope somebody can help. I know similar questions have been answered in the past. I have two tables, prod and price. Stripping out the non-essential fields, they are pretty simple: prod --- prod_num (int) prod_id (char 15) price - prod_price_id (char 15) prod_id (char 15) prod_price (decimal 10,2) prod_curr (varchar 10) Here's what I need to do: Find every prod.prod_num that has a corresponding price.prod_curr = USD but does NOT have a corresponding price.prod_curr = YEN. A product might have a price row with price.prod_curr = GBP or some other currency, so a product might 1, 2, 3, or more prices. I believe this query will do it, but can it be redone without the sub-query by using JOINs? Would that be more efficient? SELECT prod.prod_num, price.prod_price FROM prod JOIN price WHERE prod.prod_id = price.prod_id AND price.prod_id NOT IN (SELECT price.prod_id FROM price WHERE price.prod_curr = YEN); 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 http://www.the-infoshop.com/ www.giiexpress.com http://www.giiexpress.com/ http://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: SELECT missing records
I never thought of putting an additional condition on the LEFT JOIN. That
seems to do the trick.
My original query, with the sub-SELECT, does work. Both your technique and
mine generate identical results.
I did an EXPLAIN on each technique, but I don't know enough to interpret it.
Since the rows is identical except for the last bit, where mine is 4 and
yours is 2, does that mean yours is roughly more efficient by a 2:1 ratio?
My real query is rather more complicated that the one we originally
discussed, but I don't think that affects the comparison. You'll notice that
I changed one of your ON clauses to a WHERE clause, and that did not change
the EXPLAIN output one iota.
Here are the results of the EXPLAINs:
mysql explain
- SELECT prod.prod_num,
- prod_price.prod_price_del_format,
- USD,
- prod_price.prod_price_disp_price,
- YEN,
- prod_price.prod_price_end_price * @exchange AS yen_end_price
-
- FROM
- pub JOIN
- prod JOIN
- prod_price LEFT JOIN
- prod_price AS pp ON (prod.prod_id = pp.prod_id AND
pp.prod_price_end_cu
rr = 'YEN')
-
- WHERE
- pub.pub_code IN ('ENER', 'FIT', 'GOVT', 'HEAL', 'ID', 'LIFE',
'MANU
')
- AND pub.pub_id = prod.pub_id
- AND prod.prod_id = prod_price.prod_id
- AND prod.prod_discont = 0
- AND prod_price.prod_price_end_curr = 'USD'
- AND pp.prod_id IS NULL\G
*** 1. row ***
id: 1
select_type: SIMPLE
table: prod_price
type: ALL
possible_keys: prod_id
key: NULL
key_len: NULL
ref: NULL
rows: 75230
Extra: Using where
*** 2. row ***
id: 1
select_type: SIMPLE
table: prod
type: eq_ref
possible_keys: PRIMARY,pub_id
key: PRIMARY
key_len: 45
ref: giiexpr_db.prod_price.prod_id
rows: 1
Extra: Using where
*** 3. row ***
id: 1
select_type: SIMPLE
table: pub
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 45
ref: giiexpr_db.prod.pub_id
rows: 1
Extra: Using where
*** 4. row ***
id: 1
select_type: SIMPLE
table: pp
type: ref
possible_keys: prod_id
key: prod_id
key_len: 46
ref: giiexpr_db.prod.prod_id
rows: 2
Extra: Using where
==
mysql explain
- SELECT prod.prod_num,
- prod_price.prod_price_del_format,
- USD,
- prod_price.prod_price_disp_price,
- YEN,
- prod_price.prod_price_end_price * @exchange AS yen_end_price
-
-
- FROM
- pub JOIN
- prod JOIN
- prod_price
-
- WHERE
- pub.pub_code IN ('ENER', 'FIT', 'GOVT', 'HEAL', 'ID', 'LIFE',
'MANU
')
- AND pub.pub_id = prod.pub_id
- AND prod.prod_id = prod_price.prod_id
- AND prod.prod_discont = 0
- AND prod_price.prod_id NOT IN
-(SELECT prod_price.prod_id
- FROM prod_price
- WHERE prod_price.prod_price_end_curr = 'YEN')\G
*** 1. row ***
id: 1
select_type: PRIMARY
table: prod_price
type: ALL
possible_keys: prod_id
key: NULL
key_len: NULL
ref: NULL
rows: 75230
Extra: Using where
*** 2. row ***
id: 1
select_type: PRIMARY
table: prod
type: eq_ref
possible_keys: PRIMARY,pub_id
key: PRIMARY
key_len: 45
ref: giiexpr_db.prod_price.prod_id
rows: 1
Extra: Using where
*** 3. row ***
id: 1
select_type: PRIMARY
table: pub
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 45
ref: giiexpr_db.prod.pub_id
rows: 1
Extra: Using where
*** 4. row ***
id: 2
select_type: DEPENDENT SUBQUERY
table: prod_price
type: index_subquery
possible_keys: prod_id
key: prod_id
key_len: 46
ref: func
rows: 4
Extra: Using index; Using where
Thoughts? Explanations?
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: SELECT missing records
My apologies, you were correct: I left out a line from my query, so it would have given bogus results except for the fortunate fact that every product having at least one price has a USD price. The EXPLAIN output didn't change. 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: SELECT missing records
On 7/12/07, Jerry Schwartz [EMAIL PROTECTED] wrote: Since the rows is identical except for the last bit, where mine is 4 and yours is 2, does that mean yours is roughly more efficient by a 2:1 ratio? For the most part, MySQL will do better with LEFT JOIN than an IN subquery. You can read all about the subquery optimization issues on Baron Schwartz's blog, xaprb.com. Here's one to get you started: http://www.xaprb.com/blog/2006/04/30/how-to-optimize-subqueries-and-joins-in-mysql/ - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT missing records
On 7/12/07, Jerry Schwartz [EMAIL PROTECTED] wrote: I think that will give me one record for every price that is not Yen, so if a product has a price in USD and a price in GBP it will show up twice. That would happen if you removed the 'USD' condition from the first JOIN. Like I said, I'm not certain what results you're trying to get, but the LEFT JOIN technique is probably your best bet. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT missing records
At 07:26 PM 7/12/2007, Perrin Harkins wrote: On 7/12/07, Jerry Schwartz [EMAIL PROTECTED] wrote: Since the rows is identical except for the last bit, where mine is 4 and yours is 2, does that mean yours is roughly more efficient by a 2:1 ratio? For the most part, MySQL will do better with LEFT JOIN than an IN subquery. You can read all about the subquery optimization issues on Baron Schwartz's blog, xaprb.com. Here's one to get you started: http://www.xaprb.com/blog/2006/04/30/how-to-optimize-subqueries-and-joins-in-mysql/ BTW, joins will work faster if you load one or more tables in a Memory table before you do the join. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT missing records
On 7/12/07, mos [EMAIL PROTECTED] wrote: BTW, joins will work faster if you load one or more tables in a Memory table before you do the join. Well, if your tables are so small that you can load them entirely into memory, it probably doesn't matter how you code the query. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
