Iam Srkfan Iam Srkfan - 2 months ago 6
MySQL Question

Join three Tables without a common ID #MySQL

I have 3 tables cam_details, cam_category and upload_data as following..

cam_details:

+---------+-----------+-----------
| cam_id | category_id| cam_name |
+========+============+===========
| 1 | 1 | CCTV |
+--------+------------+-----------
| 2 | 1 | CCtv2 |
+--------+------------+===========
| 3 | 2 | cctv3 |
+--------+------------+===========
| 4 | 4 | cctv4 |
+--------+------------+===========


cam_category:

+-------------+---------------+
| category_id | category_name |
+=============+===============+
| 1 | Analog |
+-------------+---------------+
| 2 | Digital |
+-------------+---------------+
| 3 | Network |
+-------------+---------------+
| 4 | Simple |
+-------------+---------------+


upload_data:

+---------+-----------+
| cam_id | FILE_NAME |
+========+============+
| 1 | abc.jpg |
+--------+------------+
| 1 | abc2.jpg |
+--------+------------+
| 1 | abc3.jpg |
+--------+------------+
| 2 | xyz.jpg |
+--------+------------+


now i want to fetch the details of the cam in a array and display the results. i am able to fetch cam pic and cam_details but not the cam_category name.
code below...

<?php
// Check connection
if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$result = mysqli_query($conn,"SELECT cam_details.*, upload_data.FILE_NAME FROM `cam_details`
JOIN upload_data on cam_details.cam_id = upload_data.cam_id LEFT JOIN cam_category c
on cam_details.category_id = c.category_id
GROUP BY upload_data.cam_id ORDER BY cam_id DESC");
while($row = mysqli_fetch_array($result))
{?>

Answer

instead of left join do only join and use selec command at first for the cateroy table...

<?php
// Check connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$result = mysqli_query($conn,"SELECT cam_details.*, upload_data.FILE_NAME, cam_category.*  FROM `cam_details` 
 JOIN  upload_data on cam_details.cam_id = upload_data.cam_id JOIN cam_category 
     on cam_details.category_id = cam_category.category_id 
    GROUP BY upload_data.cam_id ORDER BY cam_id DESC");
while($row = mysqli_fetch_array($result)) 
{?>