Can I search for a value in 1 of 5 tables with one query?

schwim · July 13, 2019, 12:13am

Hi there everyone!

I want to find the value of price where vehicle = ‘car1’ in one of the 5 tables it can reside in. Currently, the only way I know to do this is to do a separate query for each table:

SELECT price FROM table1 WHERE vehicle = 'car1' LIMIT 1

and if it’s not found, I do the same for table2, table3, table4 and table5.

This all seems very inefficient. Is there a way to write a single MySQL query that will find the value of price for car1 in any of those 5 tables or do I need to stick with the separate queries? The vehicle will only exist in one of the 5 tables.

Thanks for your time!

John_Betong · July 13, 2019, 12:37am

Did you try the following:


SELECT
  price
FROM
  table1,
  table2,
  table3,
  table4,
  table5
  WHERE
  vehicle = 'car1'
  LIMIT 1

If there is only one possible result the LIMIT is not required

benanamen · July 13, 2019, 1:25am

in one of the 5 tables it can reside in

Yeah, this is your real problem. You need to learn “Database Normalization”. You should not be duplicating data. I really hope you are not naming your tables like you show.

The answer is not a query, it is a database re-design.

schwim · July 13, 2019, 1:26am

It was a simplified example for ease of explanation. John solved my issue perfectly.

benanamen · July 13, 2019, 1:27am

Actually, he didn’t. Your database is wrong if you are duplicating data or data columns.

schwim · July 13, 2019, 1:28am

Actually, he did. Thanks for the help though.

benanamen · July 13, 2019, 1:34am

If you would like to learn how the DB should be done, post or PM me an SQL dump and I will do it up for you. If your just satisfied that “it works” then there is nothing more anyone can say.

schwim · July 13, 2019, 2:39am

I seem to be getting an ambiguous error. The error isn’t ambiguous, it seems that I’m being ambiguous. Here’s my initial effort:

SELECT * FROM owned_aircrafts, owned_boats, owned_vehicles WHERE owner = '$playerIdentifier'

Which told me that owner was ambiguous. So I tried the following:

SELECT * FROM owned_aircrafts AS oa, owned_boats AS ob, owned_vehicles AS ov WHERE oa.owner = '$playerIdentifier' OR ob.owner = '$playerIdentifier' OR ov.owner = '$playerIdentifier'

Which seems to have caused a division by 0 loop that eats all my memory before failing.

How do I let the statement know that it should check for a match in owner in any of the tables?

benanamen · July 13, 2019, 2:49am

Your last post confirms you have a database design problem. I am more than happy to help you fix it as I have a little time available right now but I can only help you do it correctly. I will ask once more, provide an SQL dump and I will show you how this needs to be done. If you continue on the path you are on, all the code you write will just be a hack and you will continue to have problems.

igor_g · July 13, 2019, 3:32am

Exactly for actual DB structure you need UNION: https://dev.mysql.com/doc/refman/5.7/en/union.html

But I’m agree with @benanamen, you should think about your DB optimizing.

John_Betong · July 13, 2019, 6:35am

Whatever happened about John solved my issue perfectly. Just joking

I seriously think you should take up @benanamen’s very generous offer to redesign the database.

I dread to think what an outsourced professional would charge

schwim · July 13, 2019, 11:55am

Hi there guys and thanks for all the help!

It seems I shouldn’t have simplified my needs as it created a lot of unnecessary derailments.

I can not change the database structure as I’m writing a web panel for a game that runs a particular framework. This framework is installed across thousands of gameservers, is designed by other people and is completely outside of my control. As much as I would like to change that part of this equation, I can’t. I need to conform to the database structure that these people run.

The players will have vehicles of different types and those vehicles are stored in different tables, depending on type(aircraft, boat or ground). I need to get every one from those tables to store so I can then populate a web page with their list of vehicles.

So I plead with my resident gurus, could someone tell me how I might go about getting rid of the ambiguous owner error when trying to retrieve the data from these three tables relating to this owner?

SELECT * FROM owned_aircrafts, owned_boats, owned_vehicles WHERE owner = '$playerIdentifier'

Thanks for your time!

m_hutley · July 13, 2019, 12:00pm

Stop trying to stuff it all into one query.
Query each table independently, and UNION the results. Is what Igor was trying to tell you.

The trouble comes in if the tables don’t have the same structure.

schwim · July 13, 2019, 12:01pm

So just do what I said in the beginning? I guess I could have saved myself a lot of time

m_hutley · July 13, 2019, 12:03pm

Sort of.

(SELECT price FROM table1 WHERE owner = 'Carl' LIMIT 1)
UNION (SELECT price FROM table2 WHERE owner = 'Carl' LIMIT 1)
UNION (SELECT price FROM table3 WHERE owner = 'Carl' LIMIT 1)
UNION (SELECT price FROM table4 WHERE owner = 'Carl' LIMIT 1)
UNION (SELECT price FROM table5 WHERE owner = 'Carl' LIMIT 1)
LIMIT 1

or something to that effect.

benanamen · July 14, 2019, 12:31am

Is this game/framework/code available for download?

schwim · July 14, 2019, 2:51am

The basic framework can be found Here: https://github.com/kanersps/essentialmode

Then the gamemode content can be found here: https://github.com/ESX-Org

On top of that, there’s another 50 or so most popular resources found both on the public github page as well as other from other authors. Most of these resources bring in their own SQL to be added to the server.

Basically, server owners run relatively the same base of 75 or so resources and the web panel is designed to work with those most popular resources.

system · October 13, 2019, 1:49pm

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.