CODEWITHSUNDEEP
mysqlsqljoin
Ozi Oz
Ozi Oz

Reputation: 289

issue with joins

I have the following query, in which I used JOINs. It says:

unknown column m.bv ..

Could you please take a look and tell me what I'm doing wrong?

$query4 =  'SELECT u.*, SUM(c.ts) AS total_sum1, SUM(m.bv) AS total_sum 
FROM users u 
LEFT JOIN 
 (SELECT user_id ,SUM(points) AS ts FROM coupon GROUP BY user_id) c 
 ON u.user_id=c.user_id 
LEFT JOIN 
 (SELECT user_id ,SUM(points) AS bv FROM matching GROUP BY user_id) r 
ON u.user_id=m.user_id 
where u.user_id="'.$_SESSION['user_name'].'"
GROUP BY u.user_id';

Upvotes: 0

Views: 48

Answers (3)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115520

You have aliased the derived table with r and you reference that table (twice) with m. Correct one or the other.

Since you group by user_id in the two subqueries and user_id is (I assume) the primary key of table user, you don't really need the final GROUP BY.

I would write it like this, if it was meant for all (many) users:

SELECT u.*, COALESCE(c.ts, 0) AS total_sum1, COALESCE(m.bv, 0) AS total_sum 
FROM users u 
LEFT JOIN 
 (SELECT user_id, SUM(points) AS ts FROM coupon GROUP BY user_id) c 
   ON u.user_id = c.user_id 
LEFT JOIN 
 (SELECT user_id, SUM(points) AS bv FROM matching GROUP BY user_id) m  
   ON u.user_id = m.user_id

and like this in your (one user) case:

SELECT u.*, COALESCE(c.ts, 0) AS total_sum1, COALESCE(m.bv, 0) AS total_sum 
FROM users u 
  LEFT JOIN 
     (SELECT SUM(points) AS ts FROM coupon
      WHERE user_id = "'.$_SESSION['user_name'].'") c 
    ON TRUE 
  LEFT JOIN 
     (SELECT SUM(points) AS bv FROM matching
      WHERE user_id = "'.$_SESSION['user_name'].'") m
   ON TRUE 
WHERE u.user_id = "'.$_SESSION['user_name'].'"

The last query can also be simplified to:

SELECT u.*, 
     COALESCE( (SELECT SUM(points) FROM coupon 
                WHERE user_id = u.user_id)
             , 0) AS total_sum1,
     COALESCE( (SELECT SUM(points) FROM matching 
                WHERE user_id = u.user_id)
             , 0) AS total_sum 
FROM users u 
WHERE u.user_id = "'.$_SESSION['user_name'].'"

Upvotes: 0

jorj
jorj

Reputation: 153

Replace m., with r. Look at second Join

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79909

You are selecting SUM(points) AS bv from the table with the alias r, there is no tables with the alias m. So that it has to be r.bv instead like so:

SELECT 
  u.*, 
  SUM(c.ts) AS total_sum1, 
  SUM(r.bv) AS total_sum 
FROM users u 
LEFT JOIN 
(
    SELECT 
      user_id,
      SUM(points) AS ts 
    FROM coupon 
    GROUP BY user_id
) c ON u.user_id=c.user_id 
LEFT JOIN 
(
    SELECT 
      user_id,
      SUM(points) AS bv 
     FROM matching 
     GROUP BY user_id
) r ON u.user_id = m.user_id 
where u.user_id="'.$_SESSION['user_name'].'"
GROUP BY u.user_id

Upvotes: 1

Related Questions