Question 243791 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql select row after query using data from query

Which the best method to form following query as a single query in MySql.
I have 2 following tables

Table bid

user_id row
1 silly
2 silly
1 silly
1 silly

Table users

id username
1 ElonMusk
2 VadimRog

After query to the bid

SELECT user_id, COUNT(*) total_bids FROM bid A GROUP BY user_id ORDER BY total_bids DESC;

I have a result such as

user_id total_bids
1 3
2 1

So now I need to get username from table users WHERE A.user_id = users.id;

I have been trying many inners, outer join such as

SELECT user_id, COUNT(*) FROM bid A GROUP BY A.user_id ORDER BY COUNT(*) DESC JOIN users B ON A.user_id = B.id;

But I am on mysql error any time

What’s best practice to query this in a single query and how it’s looks also with foreign query?

UPD 1.

So I am looking for result like as

Result

user_id total_bids username
1 3 ElonMusk
2 1 VadimRog
Tags:

3

Answers


  1. 0 
    SELECT a.user_id, 
       COUNT(a.user_id) as total_bids, 
       b.username 
FROM bid A 
GROUP BY A.user_id 
JOIN users B ON A.user_id = B.id 
ORDER BY total_bids DESC
    Login or Signup to reply.
  2. 0

    Considering each id is unique per each username in users table then a simple count would do the trick

    SELECT u.id,count(b.user_id) as total_bids,u.username
from users u 
inner join bid b on u.id=b.user_id
group by u.id,u.username;

    See example here

    Login or Signup to reply.
  3. 0 
    SELECT users.id, COUNT(1) total_bids, users.username
FROM bid
    INNER JOIN users ON users.id = bid.user_id
GROUP BY users.id

    This works:

        id  total_bids  username
------  ----------  ----------
     1           3  ElonMusk
     2           1  VadimRog

    Join both the tables using bid.user_id & users.id
    Then group by on users.id, and get a count of bid records per user.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search