CODEWITHSUNDEEP
mysqlsqlnull
ShopifyBuilder
ShopifyBuilder

Reputation: 7

MySQL Unable to select a specific field value?

While generating some external reports for Kayako Fusion using MySQL Queries, I have encountered a strange and annoying problem. One of the fields in a table contains a 'TEXT' field which can contain one of two values: 41 or 40. If the end user doesn't select one of the values, the default is NULL. (I am unable to change that).

So, when attempting to do a simple:

WHERE swcustomfieldvalues.fieldvalue = '41'

It then also returns records with a NULL (or maybe '') value.

The same applies to:

WHERE swcustomfieldvalues.fieldvalue = '40'

We also get the NULL records (Which is kind of accurate because we treat NULL as 40.

For simple reference the value of 40 is for non-chargeable work and the value of 41 is for chargeable work.

What am I missing, or is there a workaround?

Screen Shot

Here is the nasty original SQL statement:

SELECT
swtickets.ticketid AS `Ticket ID`,
swtickettimetracks.tickettimetrackid AS `Track ID`,
swtickets.ticketmaskid AS `TicketMASK`,
(
    SELECT
        swcustomfieldvalues.fieldvalue
    FROM
        swcustomfieldvalues,
        swcustomfields
    WHERE
        swcustomfieldvalues.customfieldid = swcustomfields.customfieldid
    AND swtickets.ticketid = swcustomfieldvalues.typeid
    AND swcustomfields.title = 'Member Company'
    ORDER BY
        swcustomfieldvalues.customfieldvalueid DESC
    LIMIT 1
) AS MemberCompany,
(
    SELECT
        swcustomfieldvalues.fieldvalue
    FROM
        swcustomfieldvalues,
        swcustomfields
    WHERE
        swcustomfieldvalues.customfieldid = swcustomfields.customfieldid
    AND swtickets.ticketid = swcustomfieldvalues.typeid
    AND swcustomfields.title = 'Member Name'
    ORDER BY
        swcustomfieldvalues.customfieldvalueid DESC
    LIMIT 1
) AS MemberName,
(
    SELECT
        swcustomfieldvalues.fieldvalue
    FROM
        swcustomfieldvalues,
        swcustomfields
    WHERE
        swcustomfieldvalues.customfieldid = swcustomfields.customfieldid
    AND swtickets.ticketid = swcustomfieldvalues.typeid
    AND swcustomfields.title = 'Chargeable'
    AND swcustomfieldvalues.fieldvalue = '41'
    ORDER BY
        swcustomfieldvalues.customfieldvalueid ASC
    LIMIT 1
) AS `Chg`,
swtickets.`subject` AS `Subject`,
swtickets.departmenttitle AS Category,
FROM_UNIXTIME(
    swtickettimetracks.workdateline,
    '%Y-%m-%d'
) AS `workDateline`,
FROM_UNIXTIME(
    swtickettimetracks.dateline,
    '%Y-%m-%d'
) AS `dateline`,
swtickettimetracks.timespent AS `Time Spent`,
swtickets.timeworked / 60 AS `Time Worked`
FROM
    swtickets
RIGHT OUTER JOIN swusers ON swtickets.userid = swusers.userid
INNER JOIN swuserorganizations ON swuserorganizations.userorganizationid = swusers.userorganizationid
INNER JOIN swtickettimetracks ON swtickettimetracks.ticketid = swtickets.ticketid
WHERE
    swuserorganizations.organizationname = 'Clarence Professional Offices'
AND (
    swtickets.ticketstatustitle = 'Closed'
    OR swtickets.ticketstatustitle = 'Completed'
)
AND swtickets.ticketid = '2895'
GROUP BY
    `Ticket ID`,
    `Track ID`

Please be kind to me, I'm a novice ;)

Breaking out the individual Sub Selects (which are unavoidable) I can prove that this individual one for obtaining the Custom Field 'Chargeable' with a Value of '41' against a specific Ticket '2895' actually Works! See:

SELECT
        swcustomfieldvalues.fieldvalue,
        swtickets.ticketid

    FROM
        swcustomfieldvalues,
        swcustomfields,
        swtickets
    WHERE
        swcustomfieldvalues.customfieldid = swcustomfields.customfieldid
    AND swtickets.ticketid = swcustomfieldvalues.typeid
    AND swcustomfields.title = 'Chargeable'
    AND swcustomfieldvalues.fieldvalue = '40'
    AND swtickets.ticketid = '2895'
    ORDER BY
        swcustomfieldvalues.customfieldvalueid ASC

It Returns a TRUE, as I know that this Ticket is NON Chargeable (40). If I change the Value to '41' (Chargeable) it returns False (No Records).

The Whole SQL Statement (Original) should return: ALL Tickets that are Chargeable within a Date Range, showing me

Ticket ID/Ticket Mask, Track ID, Member Company, Member Name, Chargeable? (40|41), Subject, Category, WorkDateline, Dateline, Time Spent, and Time Worked.

Then in PHP I do some more manipulation to do a 'Break On' for Sub Totals etc. But I digress.

Upvotes: 0

Views: 499

Answers (1)

Sebas
Sebas

Reputation: 21532

In your screenshot the problem shows up clearly: you are returning the result of your select directly into the main SELECT statement, using a nested query.

This nested query does not return nulls when you filter fieldvalue = '40', it just doesn't return anything (i.e.: no row is returned), which leads to showing NULL in the main SELECT statement.

If you want to actually filter out the records not matching the criteria you mention (fieldvalue) then you should include this nested subquery in the JOIN section to actually reduce the resulting recordsets.

I can't give you the query since you didn't paste it here, but if you need I'll be glad to help.

Upvotes: 1

Related Questions