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

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!

Did you try the following:

  vehicle = 'car1'

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

1 Like

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.


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

1 Like

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

Actually, he did. Thanks for the help though.

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.

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?

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.

1 Like

Exactly for actual DB structure you need UNION:

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

1 Like

Whatever happened about John solved my issue perfectly. :slight_smile: Just joking :slight_smile:

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 :frowning:

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!

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.

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

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)

or something to that effect.

Is this game/framework/code available for download?

The basic framework can be found Here:

Then the gamemode content can be found here:

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.

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