CODEWITHSUNDEEP
phpmysql
EnexoOnoma
EnexoOnoma

Reputation: 8836

Trying to join 3 mysql tables but I do not get the expected result. What is mistake?

The below mysql query

SELECT *
FROM alerts_list l, alerts_data d, alerts_push_data p
WHERE p.push_data_hash = d.alerts_data_hash
AND p.push_data_alert_id = l.alerts_id
AND d.alerts_data_id = l.alerts_id
AND d.alerts_data_hash =  'JiaYRSVNZxgE'

shows the results of JiaYRSVNZxgE by joining three tables.

Here are the tables that I use and the columns that I want to connect between them:

table alerts_list

column: alerts_id

enter image description here

table alerts_push_data

column: push_data_alert_id

column: push_data_hash

enter image description here

table alerts_data

column: alerts_data_id

column: alerts_data_hash

enter image description here

What I want to achieve is:

connect push_data_alert_id with alerts_id

connect alerts_data_id with alerts_id

but show only the results where alerts_data_hash and push_data_hash is "abcdef"

Unfortunately my query results to no results found, but there are results in reality. What I am doing wrong?

Upvotes: 1

Views: 49

Answers (1)

Poiz
Poiz

Reputation: 7617

You could use MySQL JOINS to perform that operation quite easily like so:

         <?php
            // USING NORMAL JOIN:
            // WE NOW ADD AN EXTRA LAYER (THE VARIABLE $fldVal) 
            // IN THE CASE THAT YOUR VALUE ('JiaYRSVNZxgE') IS DYNAMIC...
            $fldVal = 'JiaYRSVNZxgE';
            $sql    = "SELECT DISTINCT *
                            FROM alerts_list l 
                            JOIN alerts_data d ON d.alerts_data_id=l.alerts_id
                            JOIN alerts_push_data p ON p.push_data_alert_id=l.alerts_id
                            WHERE d.alerts_data_hash='" . $fldVal . "'";



            // USING LEFT JOIN:
            // WE NOW ADD AN EXTRA LAYER (THE VARIABLE $fldVal) 
            // IN THE CASE THAT YOUR VALUE ('JiaYRSVNZxgE') IS DYNAMIC...
            $fldVal = 'JiaYRSVNZxgE';
            $sql    = "SELECT DISTINCT *
                            FROM alerts_list l 
                            LEFT JOIN alerts_data d ON d.alerts_data_id=l.alerts_id
                            LEFT JOIN alerts_push_data p ON p.push_data_alert_id=l.alerts_id
                            WHERE d.alerts_data_hash='" . $fldVal . "'";



            // USING INNER JOIN:
            // WE NOW ADD AN EXTRA LAYER (THE VARIABLE $fldVal) 
            // IN THE CASE THAT YOUR VALUE ('JiaYRSVNZxgE') IS DYNAMIC...
            $fldVal = 'JiaYRSVNZxgE';
            $sql    = "SELECT DISTINCT *
                            FROM alerts_list l 
                            INNER JOIN alerts_data d ON d.alerts_data_id=l.alerts_id
                            INNER JOIN alerts_push_data p ON p.push_data_alert_id=l.alerts_id
                            WHERE d.alerts_data_hash='" . $fldVal . "'";

::AND YET A NEW UPDATE WITH GROUP BY CLAUSE::

        <?php
            // WE NOW ADD AN EXTRA LAYER (THE VARIABLE $fldVal) 
            // IN THE CASE THAT YOUR VALUE ('JiaYRSVNZxgE') IS DYNAMIC...
            $fldVal = 'JiaYRSVNZxgE';



            $sql2    = "SELECT DISTINCT *
                            FROM alerts_list AS A_LIST
                            LEFT JOIN alerts_push_data A_PUSH ON A_PUSH.push_data_alert_id=A_LIST.alerts_id
                            LEFT JOIN alerts_data A_DATA ON A_DATA.alerts_data_hash=A_PUSH.push_data_hash
                            WHERE A_DATA.alerts_data_hash='" . $fldVal . "'
                            GROUP BY A_LIST.alerts_id";

TEST-CASE QUERY

                    SELECT DISTINCT *
                            FROM alerts_list AS A_LIST
                            LEFT JOIN alerts_push_data A_PUSH ON A_PUSH.push_data_alert_id=A_LIST.alerts_id
                            LEFT JOIN alerts_data A_DATA ON A_DATA.alerts_data_hash=A_PUSH.push_data_hash
                            WHERE A_DATA.alerts_data_hash='iSg2loGJDaWs'
                            GROUP BY A_LIST.alerts_id

RESULT

And these are to be expected because I simulated only 2 Rows in all the other Tables except the alerts_list which has 10 Rows.

Result of dumping the Query Above var_dump using the Query Above

Table: alerts_list

Table: alerts_list

Table: alerts_data Table: alerts_data

Table: alerts_push_data Table: alerts_push_data

Upvotes: 2

Related Questions