Best of 2 Tables? Most Efficient.

[ChenXiu]

Two tables list different fruits.
I don't need to know which tables have which fruit.
I just need to know the best price.

mysql> select * from table1;
+-------+-------+
| fruit | price |
+-------+-------+
| grape |  5.00 |
| melon |  1.00 |
+-------+-------+
2 rows in set (0.00 sec)

mysql> select * from table2;
+-------+-------+
| fruit | price |
+-------+-------+
| lemon |  4.00 |
| grape |  3.00 |
+-------+-------+
2 rows in set (0.00 sec)

What would be the most efficient (fastest speed, least code) way of querying per fruit?
e.g.
"...where fruit = 'grape' "  // $3.00
"...where fruit = 'strawberry' " // no result
"...where fruit = 'lemon' " // $4.00

Thank you.

[Barand]

The most efficient way would be a single table. Whay have you got two tables holding the same data structure?

[ChenXiu]

14 minutes ago, Barand said:

The most efficient way would be a single table. Whay have you got two tables holding the same data structure?

Thank you. Yes, I agree. On the shared server I'm on, due to space constraints, I can't have a 3rd large table. And I have to maintain the orig 2 tables (I'm not allowed to delete one to make room).

So, currently, I run 2 queries. And then I use PHP to compare the result (e.g. if only one or the other table has the desired item, and then if both have the same item, which has the best price, etc.)

I'm sure there must be a much more efficient way (like a "one liner") in mySQL that I could run.

Thank you.

Edited July 4, 2021 by ChenXiu

[Barand]

SELECT fruit 
     , MIN(price)
FROM (
        SELECT fruit, price FROM fruit1
        UNION ALL
        SELECT fruit, price FROM fruit2
     ) both_tables
GROUP BY fruit;


+-------+------------+
| fruit | MIN(price) |
+-------+------------+
| grape |       3.00 |
| lemon |       4.00 |
| melon |       1.00 |
+-------+------------+

 

Edited July 4, 2021 by Barand

[ChenXiu]

That's amazing! There's no way I would have ever come up with that! (I've been spending the day trying to read up on how to do "join," and by golly THAT'S a big rabbithole.......).
QUESTION:
Because I just query one fruit at a time (e.g.  "....where fruit = 'grape' ) where do I put "where fruit = 'grape'" without getting the error message "fruit column is ambigous"

EDIT: this appears to work:
SELECT fruit , MIN(price) FROM ( SELECT fruit, price FROM fruit1 UNION ALL SELECT fruit, price FROM fruit2 ) both_tables where fruit="grape" GROUP BY fruit;

Is that OKAY?

Thank you!

Edited July 4, 2021 by ChenXiu

[Barand]

SELECT fruit 
     , MIN(price) as price
FROM (
        SELECT fruit, price FROM fruit1
        UNION ALL
        SELECT fruit, price FROM fruit2
     ) x
WHERE fruit = 'grape';

+-------+------------+
| fruit |  price     |
+-------+------------+
| grape |       3.00 |
+-------+------------+

 

Edited July 4, 2021 by Barand

[Barand]

13 minutes ago, ChenXiu said:

Because I just query one fruit at a time

I hope that doesn't mean that you are looping through the results of another query (eg shopping cart) and querying the prices one by one.

[ChenXiu]

2 hours ago, Barand said:

I hope that doesn't mean that you are looping through the results of another query (eg shopping cart) and querying the prices one by one.

No sir. With the new query code you suggested, I will only be doing single queries.
Previously I had been doing very inefficient queries -- if one table returned "null" I would run a second query to see if the second table had the product. I knew it would be too inefficient to query both tables and compare prices one by one, so I actually never did that. Now I will be able to do that.

I was reading up on the "both_tables" you used in your suggested query.

I wonder if that is necessary when the "where" clause involves a primary key?

In other words, should "both_tables" always be used?

[kicken]

both_tables is just the name of the virtual-table created by the two inner select queries.  A name is necessary there, but it doesn't have to be both_tables, that's just a descriptive name.  You can use a different name if you wanted.