CODEWITHSUNDEEP
phpsql
Alexander Schulz
Alexander Schulz

Reputation: 1

SQL select rating from other table

i have 2 tables

  1. table "users" with column user_id
  2. table "extra" with column rating and column user_id

how can i select 2 tables and sorting it by rating?

my query

SELECT
   USER_ID,
   SUM(RATING) 
FROM
   USERS,
   EXTRA 
WHERE
   EXTRA.USER_ID = '{$row['USER_ID']}'

Column 'user_id' in field list is ambiguous

Upvotes: 0

Views: 91

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270411

Never use commas in the FROM clause. Always use proper explicit JOIN syntax.

Apart from that, the query is over complicated. You only need to reference once table:

SELECT e.USER_ID, SUM(e.RATING) 
FROM EXTRA e
WHERE e.USER_ID = '{$row['USER_ID']}';

Additional notes:

  • Provide table aliases for table names, typically the abbreviate of the table name. This makes queries easier to write and to read.
  • Qualify all column names in the query.
  • Do not munge query strings with parameter values. SQL provides parameter placeholders for exactly this purpose.

So the query should look more like:

SELECT e.USER_ID, SUM(e.RATING) 
FROM EXTRA e
WHERE e.USER_ID = ?

Upvotes: 1

Waldson Patricio
Waldson Patricio

Reputation: 1529

Both of your tables have user_id column. You must specify which one you want to select: 

SELECT users.user_id, SUM(rating) FROM users, extra WHERE extra.user_id ='{$row['user_id']}'

Upvotes: 0

Related Questions