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?
FROM tc_services AS tcservices
INNER JOIN tc_game_services AS tcgameservices ON tcservices.service_id = '1812';
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
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.