CODEWITHSUNDEEP
mysqlsqljoininner-join
user470760
user470760

Reputation:

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';

Upvotes: 0

Views: 2773

Answers (4)

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17147

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.

Upvotes: 5

xeph
xeph

Reputation: 60

I think you should but the ID you're searching for in the WHERE clause and specify the column you want to join in your INNER JOIN. 

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';

Upvotes: 0

Alex K.
Alex K.

Reputation: 175776

Use ON to specify the relationhip between the two, so equality on service_id then filter the results with WHERE:

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

Upvotes: 0

Imanez
Imanez

Reputation: 514

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';

Upvotes: 0

Related Questions