SQL-Join or two queries?

[stirrah]

Hello!

 

Im doing something in PHP. Right now im doing a query which gets me everything I need. Problem is that userId and projectStatus should be getting the names from another table.

 

Tables:

 

Project:

projectId

projectName

projectDescrictopn

 

Projectstatus

statusId

statusName

 

Users

userId

userName

   $test = mysql_query("SELECT * FROM `project` WHERE `projectHuvudId` = $id");
             ?> <table class="display">
             .................................................................................................................................................
              <th>id</th>
              <th>namn</th>
              <th>beskrivning</th>
              <?php
                  while($row = mysql_fetch_assoc($test)) 
                  { ?>

                 
		
		    <tr>
		    <td><?php echo $row['projectId'] ?></td>
		    <td><?php echo $row['projektName'] ?></td>
		    <td><?php echo $row['projectDescription'] ?></td>
                    <td><?php echo $row['userId'] ?></td>
                    <td><?php echo $row['projectStatus'] ?></td>
		    </tr>
		
    <?php
                  }
                 ?> </table><?php

		    
		}
      ?> 

I want to get the names which are related to the id. Is it better to just make 1 query with joins trying to get all information I need, or is it better to make another query to get that information?

[B_CooperA]

Does these three tables have any kind of bond between each others? Are u using foreign keys or something? How the SQL should know what's the status name of each project or who has created the project if there are not any kind of bond?

Edited October 8, 2013 by B_CooperA

[stirrah]

Does these three tables have any kind of bond between each others? Are u using foreign keys or something? How the SQL should know what's the status name of each project or who has created the project if there are not any kind of bond?

 

Hi,

Yes, they have a bond with foreign keys. And thats working. So my question is really if I should make a query where EVERYTHING is collected, and then use PHP to sort it out. Or if I should make "smaller" queries.

[DavidAM]

It is better to use a JOIN.

 

It is also better to select ONLY the columns you need (don't use SELECT *).

 

SELECT projectId, projektName, projectDescription, statusName, userName
FROM Projects JOIN ProjectStatus on Projects.statusId = ProjectStatus.statusId
JOIN Users ON Users.userId = Projects.userId

I'm just guessing at some of the column names, since you did not show all of the columns in the Projects table.

[B_CooperA]

I'm just guessing at some of the column names, since you did not show all of the columns in the Projects table.

 

 

That's way I was asking if there are any foreign keys you can JOIN together. So if it's not working with the query DavidAM provided, then you need to give us the all remaining columns in the Projects table