Having trouble getting distinct results probably because of an inner join in the query. Thank you

Question

Getting duplicate values for equipmentid event though I have the distinct keyword there. I think the issue is when I join with the equipment_extra table on the equipment id, as that table does have multiple entries with the same equipmentid. Either way I need results where the equipmentid is unique.

SELECT distinct(e.equipmentid), e.dcid, e.type, e.capacity, e.manufacturer, 
        e.model, e.sysid, e.location,   e.serial_no, e.contracted,
        am.project_manager, am.contract_sales, am.scheduler, ex.contract_id ,ex.billable
        FROM equipment_ext AS e INNER JOIN datacenters AS d ON d.dcid=e.dcid 
        INNER JOIN account_managers AS am ON am.companyid = d.companyid
        INNER JOIN equipment_extra AS ex ON ex.equipmentid=e.equipmentid
        WHERE companyid = $companyid AND ex.contract_status='1' GROUP BY e.equipmentid, ex.contract_id ORDER BY e.equipmentid

[Edit] the question and provide a [example], i.e. the `CREATE` statements of the tables or other objects involved (paste the **text**, don't use images, don't link to external sites), `INSERT` statements for sample data (dito) and the desired result with that sample data in tabular text format. Tag the DBMS you're using. — sticky bit, Jan 05 '22 at 18:54
Side note: Do not use string interpolation or concatenation to get values into SQL queries. That's error prone and might make your program vulnerable to SQL injection attacks. Use parameterized queries. See ["How to include a PHP variable inside a MySQL statement"](https://stackoverflow.com/questions/7537377/how-to-include-a-php-variable-inside-a-mysql-statement) and ["How can I prevent SQL injection in PHP?"](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). — sticky bit, Jan 05 '22 at 18:54
`DISTINCT` is not a function, it's a _set quantifier_. It's here a part of `SELECT DISTINCT`, and used to return _distinct rows_. Skip those extra parentheses and simply write `SELECT DISTINCT e.equipmentid, e.dcid, ...` - to make code clearer! — jarlh, Jan 05 '22 at 19:11
And you typically `GROUP BY` the same columns as you `SELECT` - except those who are arguments to set functions. (Which is none in your query... why group by then?) Your GROUP BY is invalid and expected to raise an error - and will do so when you upgrade to a newer MySQL version (unless in compatibility mode.) — jarlh, Jan 05 '22 at 19:14
Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. — Community, Jan 13 '22 at 07:09

Tamora · Accepted Answer · 2022-01-05T22:28:09.547

The DISINCT keyword applies to all the columns in your SELECT statement, not just the first column. The parentheses that you have added around e.equipmentid do not change this. Using DISTINCT when you are already using a GROUP BY clause does not work the way you seem to be expecting.

You are using the GROUP BY clause with e.equipmentid and ex.contract_id. This tells the query to return one row for each unique pair of e.equipmentid and ex.contract_id values.

If you want a single row result for each e.equipmentid, but the same equipmentid may be associated with multiple ex.contract_id values, then you need to specify which ex.contract_id to display, such as the MIN or MAX value for ex.contract_id.

For example, you could do something like this with your query, though you would lose the information about multiple ex.contract_id values:

SELECT e.equipmentid, e.dcid, e.type, e.capacity, e.manufacturer, 
    e.model, e.sysid, e.location, e.serial_no, e.contracted,
    am.project_manager, am.contract_sales, am.scheduler, 
    MAX(ex.contract_id) AS contract_id, MAX(ex.billable) AS billable
    FROM equipment_ext AS e 
    INNER JOIN datacenters AS d ON d.dcid = e.dcid 
    INNER JOIN account_managers AS am ON am.companyid = d.companyid
    INNER JOIN equipment_extra AS ex ON ex.equipmentid = e.equipmentid
    WHERE companyid = $companyid AND ex.contract_status='1'
    GROUP BY e.equipmentid, e.dcid, e.type, e.capacity, e.manufacturer, 
    e.model, e.sysid, e.location, e.serial_no, e.contracted,
    am.project_manager, am.contract_sales, am.scheduler
    ORDER BY e.equipmentid

Edited to add more information. If you want to SELECT the row with the maximum value for ex.contract_id for each e.equipmentid, that requires a more complicated WHERE clause. An example:

SELECT e.equipmentid, e.dcid, e.type, e.capacity, e.manufacturer, 
    e.model, e.sysid, e.location, e.serial_no, e.contracted,
    am.project_manager, am.contract_sales, am.scheduler, 
    ex.contract_id, ex.billable
    FROM equipment_ext AS e 
    INNER JOIN datacenters AS d ON d.dcid = e.dcid 
    INNER JOIN account_managers AS am ON am.companyid = d.companyid
    INNER JOIN equipment_extra AS ex ON ex.equipmentid = e.equipmentid
    WHERE companyid = $companyid AND ex.contract_status='1'
    AND ex.contract_id = 
       (SELECT MAX(ex2.contract_id) FROM equipment_extra AS ex2
        WHERE e.equipmentid = ex2.equipmentid)
    ORDER BY e.equipmentid

Note that this second query eliminates the need for using either DISTINCT or GROUP BY.

Thank you, it seems like I can take your idea about using 'MAX(ex.contract_id) AS contract_id' as the equipmentid that I am interested will always be associated with newest contract aka the entry with the largest contractid. As for 'MAX(ex.billable) AS billable', this is not necessary correct? As it is already choosing the appropriate row from the equipment_extra table with 'MAX(ex.contract_id) AS contract_id'. Thank you for your help, this is legacy code and I was overwhelmed. — user17843896, Jan 05 '22 at 20:26
The 'MAX(ex.contract_id) AS contract_id' does not work quite that way. It does not select the entire row with the MAX value of contract_id. That is possible to do though! I have edited my answer with that idea in mind, adding a second example query. — Tamora, Jan 05 '22 at 22:29
@user17843896 If this answers your question then please "accept" the answer. — Tamora, Jan 18 '22 at 20:00

Having trouble getting distinct results probably because of an inner join in the query. Thank you

1 Answers1