CODEWITHSUNDEEP
mysql
user2573866
user2573866

Reputation: 11

MySQL query with 2 JOINs not returning expected result

I have the following 3 tables. This is just a small section of the data, I left out most rows and other columns that I'm not querying against. If it would be helpful to include the full table(s) let me know and I can figure out how to post them.

infocoms

id    items_id  itemtype         value      
1735  21        Software         0.0000     
1736  22        Software         0.0000     
1739  21        Peripheral       151.2500   
1741  23        Peripheral       150.5000   
1742  24        Peripheral       0.0000     
1743  25        Peripheral       0.0000

locations

id  name    
34  Anaheim
35  Kirkland  
36  Palm Springs  
37  Tacoma

peripherals

id  name                                                          locations_id  
11  Logitech USB Wheel Mouse                                      0             
12  Samsung Slate Dock                                            17            
21  USB Scan Gun with Stand                                       34            
23  USB Scan Gun with Stand                                       63            
24  USB Scan Gun with Stand                                       45            
26  USB Scan Gun with Stand                                       39

I am running the following query against these tables:

SELECT peripherals.name, infocoms.value, locations.name AS Branch
FROM peripherals
JOIN infocoms ON peripherals.id = infocoms.items_id
JOIN locations ON peripherals.locations_id = locations.id
WHERE (peripherals.name = 'USB Scan Gun with Stand'
AND peripherals.locations_id != '0')
GROUP BY peripherals.id ORDER BY locations.name ASC

I get the right number of rows returned however the value shows everything as 0.0000 instead of where there are actual amounts (151.25 and 150.50).

Any help or insight would be greatly appreciated. Thanks.

Upvotes: 1

Views: 101

Answers (3)

user2573866
user2573866

Reputation: 11

In the WHERE clause I added AND infocoms.itemtype = 'Peripheral' which returned the correct number of of rows along with the appropriate value(s).

Upvotes: 0

LexLythius
LexLythius

Reputation: 1944

@Eugene Scray is right about GROUP BY being the reason why you see only some values. To keep columns "ungrouped", you need to add those columns into the GROUPING clause. For example:

GROUP BY peripherals.id, infocom.value

Upvotes: 0

Eugene Scray
Eugene Scray

Reputation: 355

Comment (because I do not have the reputation) : "value" and "name" should be encased in back-ticks (``) because they are reserved words.

But looking at your code a little closer I find that you are grouping by location.name even though many of the values are duplicated when you do an JOIN ON peripherals.locations_id = locations.id.

What happens afterwards is that you GROUP the rest of the statements by location.name giving the first result of all of the locations that do not have a location name associated to the peripherals.locations_id

Try not using GROUPING BY and see what you get. In order to get the results that you want you need to either omit the JOIN ON peripherals.locations_id = locations.id or associate every peripials.locations_id to an appropriate locations.id

Upvotes: 1

Related Questions