CODEWITHSUNDEEP
phpmysql
user3701157
user3701157

Reputation: 97

Database Group By error

I've been working with Mysql for a while, but this is the first time I've encountered this problem.

The thing is that I have a select query... 

SELECT
    transactions.inventoryid,
    inventoryName,
    inventoryBarcode,
    inventoryControlNumber,
    users.nombre,
    users.apellido,
    transactionid,
    transactionNumber,
    originalQTY,
    updateQTY,
    finalQTY,
    transactionDate,
    transactionState,
    transactions.observaciones
FROM
    transactions
LEFT JOIN 
    inventory ON inventory.inventoryid = transactions.inventoryid
LEFT JOIN 
    users ON transactions.userid = users.userid
GROUP BY
    transactions.transactionNumber
ORDER BY
    transactions.inventoryid

But the GROUP BY is eliminating 2 values from the QUERY.

In this case, when I output:

foreach($inventory->inventory as $values){
    $transactionid[] = $values['inventoryid'];
}

It returns: 

2,3,5

If I eliminate the GROUP BY Statement it returns

2,3,4,5,6

Which is the output I need for this particular case.

The question is:

Is there a reason for this to happen? If I'm grouping by a transaction and that was supposed to affect the query, wouldn't it then return only 1 value?

Maybe I'm over thinking this one or been working too long on the code that I don't see the obvious flaw in my logic. But if someone can lend me a hand I would appreciate it.

Upvotes: 1

Views: 56

Answers (1)

Erik Theoboldt
Erik Theoboldt

Reputation: 2568

In standard SQL you can only SELECT colums which are

  • contained in GROUP BY clause
  • (or) aggregate "colums", like MAX() or COUNT().

You need to consult the MySQL description of the interpretation they use for columns which are not contained by GROUP BY (and which are no aggregated column) MySQL Handling of GROUP BY to find out what happens here.

Do you need more information?

Upvotes: 1

Related Questions