Jordan Jordan - 2 months ago 6
MySQL Question

MYSQL and PHP where any related row contains string

I am having a small issue with MYSQL relations.

There is for every 1 value in table 1, there can be a multitude of values (0+) in table 2.

I am able to get all the data correctly, however, the issue comes when some values in table 2 differ, specifically the "taken up" field.

$sql = "
SELECT
accounts.name AS business,
accounts.industry AS style,
accounts_cstm.renewaldate_c AS ren_date,
accounts_cstm.nolongercontact_c AS NLC,
accounts_cstm.contactname_c AS person,
campaigns.name AS campaign,
users.first_name AS exec_fn,
users.last_name AS exec_sn,
email_addr_bean_rel.bean_id AS bean_id,
email_addresses.email_address AS email,
qs_quotationinformation.takenup AS takeup,
email_addr_bean_rel.email_address_id AS email_id
FROM
accounts
LEFT JOIN
campaigns ON accounts.campaign_id = campaigns.id
LEFT JOIN
users ON accounts.assigned_user_id = users.id
INNER JOIN
accounts_cstm ON accounts.id = accounts_cstm.id_c
LEFT JOIN
email_addr_bean_rel ON accounts.id = email_addr_bean_rel.bean_id
LEFT JOIN
email_addresses ON email_addr_bean_rel.email_address_id = email_addresses.id
LEFT JOIN
qs_quotamation_accounts_c ON accounts.id = qs_quotamation_accounts_c.qs_quot108funts_ida
LEFT JOIN
qs_quotationinformation ON qs_quotamation_accounts_c.qs_quotdb81tion_idb = qs_quotationinformation.id
WHERE
accounts.deleted = 0";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
if($row["NLC"] == 1 || $row["takeup"] == 1){$NLC = "No";}else{$NLC = "Yes";}
echo '<tr><td>'.$row['business'].'</td><td>'.$row["style"].'</td><td>'.$row["ren_date"].'</td><td>'.$NLC.'</td><td>'.$row["person"].'</td><td>'.$row["campaign"].'</td><td>'.$row["exec_fn"].' '.$row["exec_sn"].'</td><td>'.$row["email"].'</td><td>'.$row["takeup"].'</tr>';
}
} else {
echo "0 results";
}


In this case Table 1 is "accounts" and Table 2 is "qs_quotationinformation".

There are some accounts in Table 1 which have multiple records in Table 2, and some of the "takenup" records in Table 2 (relating to the same account) can be 1 and some be 0.

So what I need to do is have it so that if any of the records in Table 2 = 1, then all of the variables of $NLC need to = "No".

I don't know if this is possible, or if there is a better way to get this information. The html table is missing data that gets pulled, but that's because the table is just a visual representation of the most important data to the user.

EDIT Tables (excluding personal data):

Table 1:

+----+---------+---------+
| id | name | deleted |
+----+---------+---------+
| 1 | example | 0 |
+----+---------+---------+


Table 2:

+----+---------+
| id | takenup |
+----+---------+
| 1 | 0 |
+----+---------+
| 2 | 1 |
+----+---------+
| 3 | 0 |
+----+---------+


All of the rows in Table 2 relate to the row in Table 1. But because there is a row with takenup = 1 $NLC needs to return "No" and not "Yes" (which it currently does because the last related row is 0)

Answer

So, if you understand you correctly, if you have an account, that has a corresponding qs_quotationinformation.takenup value of 1, then the query should return "No" for accounts_cstm.nolongercontact_c AS NLC for all records with the same account id, regardless of the value of qs_quotationinformation.takenup field in the other records.

In this case you need to get the list of accounts that have qs_quotationinformation.takenup=1 and you can use a subqury to return this information, which can be left joined to the main query. accounts_cstm.nolongercontact_c AS NLC would be changed to a case expression to return the "No" value based on the subquery.

    SELECT 
        accounts.name AS business,
        accounts.industry AS style,
        accounts_cstm.renewaldate_c AS ren_date,
        case
            when no_nlc.qs_quot108funts_ida is null then accounts_cstm.nolongercontact_c
            else 'No'
        end AS NLC,
        accounts_cstm.contactname_c AS person,
        campaigns.name AS campaign,
        users.first_name AS exec_fn,
        users.last_name AS exec_sn,
        email_addr_bean_rel.bean_id AS bean_id,
        email_addresses.email_address AS email,
        qs_quotationinformation.takenup AS takeup,
        email_addr_bean_rel.email_address_id AS email_id
    FROM
        accounts
            LEFT JOIN
        campaigns ON accounts.campaign_id = campaigns.id
            LEFT JOIN
        users ON accounts.assigned_user_id = users.id
            INNER JOIN
        accounts_cstm ON accounts.id = accounts_cstm.id_c
            LEFT JOIN
        email_addr_bean_rel ON accounts.id = email_addr_bean_rel.bean_id
            LEFT JOIN
        email_addresses ON email_addr_bean_rel.email_address_id = email_addresses.id
            LEFT JOIN
        qs_quotamation_accounts_c ON accounts.id = qs_quotamation_accounts_c.qs_quot108funts_ida
            LEFT JOIN
        qs_quotationinformation ON qs_quotamation_accounts_c.qs_quotdb81tion_idb = qs_quotationinformation.id
            LEFT JOIN (
                       SELECT qs_quot108funts_ida
                       FROM qs_quotamation_accounts_c 
                       INNER JOIN qs_quotationinformation
                           ON qs_quotamation_accounts_c.qs_quotdb81tion_idb = qs_quotationinformation.id
                       WHERE qs_quotationinformation.takenup=1
                      ) no_nlc ON accounts.id = no_nlc.qs_quot108funts_ida
    WHERE
        accounts.deleted = 0

The case expression assumes that accounts_cstm.nolongercontact_c field is of a string type (char, varchar, etc). If this is not the case, then you need to cast the value of accounts_cstm.nolongercontact_c field to char using the cast() function.