Laravel / MySQL query raw

Question

Not sure on how to query this, but let's say I've got two tables as such

Table 1

| id         | userid      | points       |
|:-----------|------------:|:------------:|
| 1          |      1      |     30     
| 2          |      3      |     40    
| 3          |      1      |     30     
| 4          |      3      |     40      
| 5          |      1      |     30    
| 6          |      3      |     40

Table 2

| id         | userid      | productid    |
|:-----------|------------:|:------------:|
| 1          |      1      |     4     
| 2          |      3      |     4    
| 3          |      1      |     3     
| 4          |      3      |     3      
| 5          |      1      |     3    
| 6          |      3      |     3

I need to get all rows with s from table 1 where points are above 30 and where table2 has a productid of 4

At the moment I have a raw query like this:

SELECT userid, SUM(points) as points FROM table1 GROUP BY userid HAVING SUM(points) >= 30 ORDER BY SUM(points) DESC, userid

Through DB::select

How can I make sure that all of the results only have a product id of 4 via table2 connected via the userid? Is this where join is applicable and then I see leftjoin and others so I'm not too sure how to go about this, any suggestions appreciated.

EDIT:

I just got this working:

SELECT userid, SUM(points) as points FROM table1 LEFTJOIN table2 on table1.userid = table2.userid WHERE table2.productid = '4' GROUP BY userid HAVING SUM(points) >= 30 ORDER BY SUM(points) DESC, userid

It is giving me back to correct results, but not 100%sure on join/leftjoin, any feedback if that is OK?

Yes but it is not looking for table 2 where productid is 4, as the question. I've found a solution and am about to edit it and answer below comment, not sure if it is the correct way though so any feedback would be appreciated in a moment — Jamie000012
– Jamie000012, Commented Sep 7, 2017 at 11:22

ScaisEdge · Accepted Answer · 2017-09-07 11:16:47Z

0

If you use inner join you get only the related row that match between productid =4 and sum only this

SELECT userid, SUM(points) as points 
FROM table1 
inner join table2 on table1.id = table2.userid and productid=4
GROUP BY userid 
HAVING SUM(points) >= 30 
RDER BY SUM(points) DESC, userid

or if you are looking for the user that have on of the product = 4 then you can use

SELECT userid, SUM(points) as points 
FROM table1 
inner join (
  select distinct userid 
  from table2 where productid =4

) t on table1.id = t.userid 
GROUP BY userid 
HAVING SUM(points) >= 30 
RDER BY SUM(points) DESC, userid

answered Sep 7, 2017 at 11:16

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Jamie000012 Over a year ago

Thanks, I actually got something working now which is this: SELECT userid, SUM(points) as points FROM table1 LEFTJOIN table2 on table1.userid = table2.userid WHERE table2.productid = '4' GROUP BY userid HAVING SUM(points) >= 30 ORDER BY SUM(points) DESC, userid would this be correct or is leftjoin not right here? It does give back the correct rows in my current instance it seems

ScaisEdge Over a year ago

explain better .. my suggestion are right? are wrong? work ? don'twork? is what you are looking for?

Jamie000012 Over a year ago

Apologies commented too quickly, I also edited original post with a solution that seems to be working, but you're suggesting innerjoin as opposed to the leftjoin I got it working with. Any feedback?

ScaisEdge Over a year ago

the use of left join is for rows that don't match in select table using left joi you are calculateing the sum of all the point not only of those with producto = 4 or for user with product = 4 .. If you need only point for row whit product = 4 you should use INNER JOIN as i suggested in my answer ...hope my comment is clear

Jamie000012 Over a year ago

Thanks scaisedge, I think that makes sense. I have selected your answer as the solution. Both inner join and left join seems to give me the same result with my small table, but if I understand correctly with INNER JOIN it will only sum points for rows in table2 that have productid is 4

Collectives™ on Stack Overflow

Laravel / MySQL query raw

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related