CODEWITHSUNDEEP
phpmysql
Jordan
Jordan

Reputation: 61

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)

Upvotes: 0

Views: 59

Answers (1)

Shadow
Shadow

Reputation: 34232

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 subquery 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.

Upvotes: 1

Related Questions