Making MySQL return only one row per entry

Question

I have two tables in my MySQL setup. One with some Materials and one with the respective Weights of them. Every time some Material is updated, I update the Weight, adding another row in the Weights table, to later keep track of the Weight changes (I also included some timestamps and stuff, but I won't be adding those in the example below, for simplicity.)

        Table "Weights"                  Table "Materials"
###############################   ##############################
# ID #  MaterialID  #  Weight #   # Code # Type # Size # Color #
###############################   ##############################
#  1 #      46      #  456.4  #   #  46  #   B  #  13  # Black #   
#  2 #      46      #  453.2  #   #  47  #   D  #  11  # Green #   
#  3 #      47      #  231.1  #   ##############################
#  4 #      47      #  222.0  #
###############################

Now if I execute a query like this below, I can get the Material and it's corresponding Weight:

SELECT Materials.Code,
       Materials.Type,
       Materials.Size,
       Materials.Color,
       Weights.Weight
FROM 
       Materials
INNER JOIN Weights ON Weights.MaterialID = Materials.Code
WHERE Materials.Code = 46
ORDER BY Weights.Code DESC
LIMIT 1

Result (It shows me the last weight to the corresponding Meterial):

#######################################
# Code # Type # Size # Color # Weight #
#  46  #   B  #  13  # Black #  453.2 #
#######################################

So far, so good, but now...

If I omit the LIMIT and the WHERE clause, I get multiple rows (That is quite obvious, but here I'm stuck now):

#######################################
# Code # Type # Size # Color # Weight #
#  47  #   D  #  11  # Green #  231.1 #
#  47  #   D  #  11  # Green #  222.0 #
#  46  #   B  #  13  # Black #  453.2 #
#  46  #   B  #  13  # Black #  456.4 #
#######################################

My plan was to get only the last inserted weight with the corresponding Material. For one Material, one weight row, like so:

#######################################
# Code # Type # Size # Color # Weight #
#  46  #   B  #  13  # Black #  453.2 #
#  47  #   D  #  11  # Green #  222.0 #
#######################################

I already tried putting a MIN() between the Weights.Weight, expecting that it only would pick one, but then it would only return one row, which is the 46, and ignoring all others.

I'm clueless, already searched all over the Internet, found some answers, but I don't understood nothing on how I could adapt those to fit my needs.

Any help is greatly appreciated.

@saband I don't understand MySQL so well (The basics yes, but JOIN and stuff not so), so if you could point me in the right direction with LEFT JOIN, it would be enormously helpful. — Fusseldieb, Oct 23 '17 at 18:08
Check this past post, https://stackoverflow.com/questions/3491329/group-by-with-maxdate is basically the same concept. — Nic3500, Oct 23 '17 at 18:14
I dont want to be the bearer of bad news but understanding `INNER JOIN` and `OUTER JOIN` is a large part of "the basics" for SQL development. — Tom O., Oct 23 '17 at 18:15
@saband I've tried that too, but it still gives me multiple rows. The only diference is that now there are new rows and some values are "(null)" where the other tables has no respective values for it. — Fusseldieb, Oct 23 '17 at 18:36

isaace · Accepted Answer · 2017-10-23T18:54:56.127

1

This should work. I added a filter on the ID to select only the ID's that match the max ID for each material.

SELECT  w.ID , m.Code,
   m.Type,
   m.Size,
   m.Color,
   w.Weight
FROM 
   Materials m
INNER JOIN Weights w ON w.MaterialID = m.Code 
AND 
w.ID IN(SELECT MAX(ID) FROM Weights i WHERE i.MaterialID = m.Code )

edited Oct 23 '17 at 18:54

answered Oct 23 '17 at 18:47

isaace

3,336
1
9
22

Do or do not, there is no "try". A ***good answer*** will always have an explanation of what was done and why it was done in such a manner, not only for the OP but for future visitors to SO. – Jay Blanchard Oct 23 '17 at 18:52
Holy cow, this complex thing actually works. Thanks :) – Fusseldieb Oct 23 '17 at 18:58

score 0 · Answer 2 · answered Oct 23 '17 at 18:08

0

You can use :

mysqli_insert_id($connection);

To get the last inserted ID of a query. You can then use this to access your other material or weight or whichever way you would like to produce your code.

E.g.

$latest_id = mysqli_insert_id($connection);

$query ="SELECT whatever FROM wherever WHERE id = '{$latest_id}' ";

Etc

answered Oct 23 '17 at 18:08

cmprogram

1,854
2
13
25

Yeah, I could easily do that in two separate queries. One pushing from the Material table and the other from the Weights table, but performance would suffer a bit, and I want to avoid that, IF possible. – Fusseldieb Oct 23 '17 at 18:10
It's a fair point. But if it's between half of a millisecond, and not working, I'd choose the former. – cmprogram Oct 23 '17 at 18:27
Good point also, but there is not one row, but aprox. 300. Half a millisecond would have an impact of 150ms. Not much, but still... – Fusseldieb Oct 23 '17 at 18:44

Making MySQL return only one row per entry

2 Answers2