Brett Powell Brett Powell - 3 months ago 8
MySQL Question

Query values from two tables with same ID

I currently have a database with two tables tc_services and tc_game_services, where these contain different information about the same service. They both are identified by the service_id field, where I need to pull the billing_id from one and game_id from the other.

Now I found some examples in other questions, however when ran, they just provide me a list of these two values for every service, rather than the specific one I am trying to query from.

What do I need to correct in this query?

SELECT
tcservices.billing_id,
tcgameservices.game_id
FROM tc_services AS tcservices
INNER JOIN tc_game_services AS tcgameservices ON tcservices.service_id = '1812';

Answer

This is very basic SQL. You didn't specify a joining condition on those tables. As you've mentioned, your common column is service_id. Connect both tables using this condition, and then in WHERE clause put the logic on retrieval of record for a specific service_id.

SELECT
  tcservices.billing_id,
  tcgameservices.game_id
FROM tc_services AS tcservices
INNER JOIN tc_game_services AS tcgameservices ON tcservices.service_id = tcgameservices.service_id
WHERE tcservices.service_id = '1812';

Explaining the behaviour of your approach (the line below): you pulled every record from tc_services matching a criteria service_id = '1812' and then cross-joined it with every record from tc_gameservices. This is how you got your result.

INNER JOIN tc_game_services AS tcgameservices ON tcservices.service_id = '1812'

I would suggest cutting the length of table aliases to save some typing and make the code look neater. This might particularly come in handy when dealing with larger and complicated queries.

As a side note, if your service_id is of Integer datatype then you don't need single quotes around the value.