# SQLite Query from unknown table based on known column value single query

I'm working on a database to back up my online TCG. It's currently SQLite, but I'm going to transition it to a MySQL database later.

Currently, I have three distinct types of cards with their own unique fields. For example. However, all cards have a single uniqueid that I use to refer to them.

TABLE cards {
CARD_ID as INT
CARD_TYPE as INT
TYPE_ID as INT
}

TABLE creature_cards {
CARD_ID as INT
TYPE_ID as INT
NAME as TEXT
FLAVOR as TEXT
ATTACK as INT
DEFENSE as INT
ELEMENT as INT
FLAGS as INT
MANA_COST as TEXT
}

TABLE spell_cards {
CARD_ID as INT
TYPE_ID as INT
NAME as TEXT
FLAVOR as TEXT
SPELL_TYPE as INT
ELEMENT as INT
MANA_COST as TEXT
}

TABLE mana_cards {
CARD_ID as INT
TYPE_ID as INT
NAME as TEXT
FLAVOR as TEXT
ELEMENT as INT
}


I'm trying to figure out how to build a query that will allow me to get all fields from a specific table without knowing the table I need to pull from ahead of time.

Basically, I want to be able to supply CARD_ID, query the cards table, and use the CARD_TYPE and TYPE_ID to return the row from creature_cards where CARD_TYPE is 1, spell_cards where CARD_TYPE is 2, and mana_cards where CARD_TYPE is 3.

I'm currently doing this through two separate queries and doing the logic on the backend, but I'm wondering if there's a way to get the data all in one query, because my queries can be quite time consuming.

Would it just be better to jam all card fields into one table, rather than keeping them in three separate tables?

Would it just be better to jam all card fields into one table, rather than keeping them in three separate tables?

That depends on 2 things: How many you have and how different they are. Basically if you don't aim to have more than several thousand items (that's just a random number order I throw, it's system and query dependent), you are probably better of with just 1 table, it will save you a lot of time.

That's brings me to the second point. It depends on how different the cards are. If I look at your schema, you have 5 common fields between cards, and the rest is a bit different. That's in my opinion not enough to justify having different tables. The logic is the same behind each cards (they are used in the same way, only with different effect?).

However, your main concern over this seems to be performance. For performance, it depends on the frequency mainly of your calls. If you have in a database all of your possible cards, then you don't need to query the database more than once. You just load all of them in memory and go through the memory whenever you need to, it's way faster than accessing disk.

Finally, to answer your question, you can use JOINS (examples). And if you are interested, see the performance of joins here.