MYSQL and PHP where any related row contains string

Question

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)

Just a tip, try to keep your joins as low as possible, 7 is a lot. — Grumpy
– Grumpy, Commented Sep 29, 2016 at 8:51
@Grumpy unfortunately the database I have to work with has 1 account's record split up over 10 tables due to the program which created/uses it — Jordan
– Jordan, Commented Sep 29, 2016 at 9:14
@Jordan 7 joins are actually not that much in a real-life query, so do not worry about that :) — Shadow
– Shadow, Commented Sep 29, 2016 at 9:25

marc_s · Accepted Answer · 2016-10-03 19:23:08Z

1

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.

edited Oct 3, 2016 at 19:23

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Sep 29, 2016 at 9:24

Shadow

34.5k10 gold badges67 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jordan Over a year ago

No, I probably should have used a different variable to the AS for nolongercontact_c What it should do is if there is any account which has at least one occurrence of accounts_cstm.nolongercontact_c or qs_quotationinformation.takenup where it has a value of 1, then the variable $NLC needs to be set to "No". Otherwise, it needs to set to "Yes"

Shadow Over a year ago

Then just change the case expression above to return 'Yes' instead of accounts_cstm.nolongercontact_c.

Collectives™ on Stack Overflow

MYSQL and PHP where any related row contains string

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related