Jump to content

Best of 2 Tables? Most Efficient.


ChenXiu
 Share

Go to solution Solved by Barand,

Recommended Posts

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.

Link to comment
Share on other sites

Posted (edited)
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 by ChenXiu
Link to comment
Share on other sites

Posted (edited)
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 by Barand
Link to comment
Share on other sites

Posted (edited)

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 by ChenXiu
Link to comment
Share on other sites

  • Solution
Posted (edited)
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 by Barand
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

 

  • Like 1
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.