CODEWITHSUNDEEP
phpmysqlsqldatabasemysqli
Usman Ali
Usman Ali

Reputation: 58

Select rows from a table2 based on a value from table1

I am trying to retrieve data from a table where I want to get all the rows from a table where a a column could be multiple values i.e.

Table 1 

Id     User   SiteID
--------------------
 1  -  Ali   -  6
--------------------
 2  -  Ali   -  7
--------------------
 3  -  Ali   -  8
--------------------
 4  -  Jack  -  20
--------------------

So from the table one Ali has sites 6,7,8 and jack has site 20 only. Now I have another table

Table 2

    Id     Event         Location     SiteID
    -------------------------------------------
     1  -  Fire       -  Room 22      -  101
    -------------------------------------------
     2  -  Fault      -  Reception    -  6
    -------------------------------------------
     3  -  Isolation  -  Student Hall -  7
    -------------------------------------------
     4  -  Bomb Alert -  Floor 3      -  8
    -------------------------------------------

I want to select the data from the table 2 where the SiteID's are the sites that Ali has i.e Ali has SiteID's 6,7,8.

So initially using php I can fetch the records from Table 1 using the following:

 $query="SELECT * FROM Table1 WHERE User='Jack'";
                $result = mysqli_query($con,$query) or die(mysql_error());
                while($row11=mysqli_fetch_array($result,MYSQLI_ASSOC)){ 
                    $site=$row11["SiteID"];
                    echo $mySite;      
}

Now I want to select from Table 2 Where SiteID is the sites allocated to Ali i.e. Site 6,7,8.

So, I have tried the following but it does not accomplish the task:

$sql_query="SELECT * FROM Table2 WHERE SiteID='$site' ORDER BY Id DESC LIMIT 300";
                $result_set=mysqli_query($con,$sql_query);

                   while($row=mysqli_fetch_array($result_set,MYSQLI_ASSOC))
                    {

                        echo $row["Event"];
                        echo $row["Location"];
                   }

However this does not give me the desire results. I want all the events and location from table 2 where the site is the sites that Ali has from table one so the desired result should give me the following.

     Id     Event         Location     SiteID
    -------------------------------------------
     2  -  Fault      -  Reception    -  6
    -------------------------------------------
     3  -  Isolation  -  Student Hall -  7
    -------------------------------------------
     4  -  Bomb Alert -  Floor 3      -  8
    -------------------------------------------

Can anyone suggest how I can accomplish this. Thanks

Upvotes: 0

Views: 382

Answers (2)

Kasnady
Kasnady

Reputation: 2279

Change the t2.* to the desired column you want to show only. E.g: t2.SiteID, t2.xxxx

SELECT t1.*, t2.*
FROM table1 t1
 LEFT JOIN table2 t2 ON t1.SiteID = t2.SiteID
WHERE user = ‘Jack’

Upvotes: 1

Salman Arshad
Salman Arshad

Reputation: 272006

You can use one query instead of two:

SELECT Table2.*
FROM Table1
INNER JOIN Table2 ON Table1.SiteID = Table2.SiteID
WHERE Table1.User = 'Ali'
ORDER BY Id DESC
LIMIT 300

Upvotes: 3

Related Questions