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

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 <jschwartz@the-infoshop.com> 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/mysql?unsub=anandkl@gmail.com
>
>

On 7/12/07, Jerry Schwartz <jschwartz@the-infoshop.com> 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')
WHERE yen_price.prod_id IS NULL

- Perrin

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:anandkl@gmail.com]
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 <jschwartz@the-infoshop.com> 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/mysql?unsub=anandkl@gmail.com

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

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

On 7/12/07, Jerry Schwartz <jschwartz@the-infoshop.com> 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

On 7/12/07, Jerry Schwartz <jschwartz@the-infoshop.com> 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...oins-in-mysql/

- Perrin

At 07:26 PM 7/12/2007, Perrin Harkins wrote:
>On 7/12/07, Jerry Schwartz <jschwartz@the-infoshop.com> 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...oins-in-mysql/

BTW, joins will work faster if you load one or more tables in a Memory
table before you do the join.

Mike

On 7/12/07, mos <mos99@fastmail.fm> 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