SQL Join

[elite311]

After reading for hours and still not understanding how to write this I'm looking here for help.

 

I am fairly new to the world of MySQL but I have written lots of query's just always just for basic things, now I'm trying to do something a little more advanced and I'm not understanding it at all even after reading about joins and what types to use.

 

I have 2 tables, assets & locations. I want to join these 2 tables so that I can display how many assets are at each location, seemed pretty easy to me at first but boy was I wrong....

 

Tables are:

 

Name: assets

id (auto increment)

asset

currentjob

 

Name: location

id (auto increment)

jobnumber

jobname

jobphone

jobfax

 

$locationresult = $db->fetch_all_array("SELECT * FROM location LEFT JOIN assets ON location.jobnumber = assets.currentjob SUM(assets.currentjob) WHERE assets.currentjob = location.jobnumber AS jobassets ORDER BY location.jobnumber ASC");

 

Then I am displaying it like this:

 

            <table width="100%" cellspacing="5">
              <tr>
              	<th align="left">Job #</th>
                <th align="left"># of Assets</th>
                <th align="left">Job Name</th>
                <th align="left">Job Phone #</th>
                <th align="left">Job Fax #</th>
              </tr>
           	   <?php foreach($locationresult as $location) { ?>
                <tr>
                <td align="left" valign="top"><a href="locationinfo.php?id=<?php echo $location['jobnumber'];?>"><?php echo $location['jobnumber'];?></a></td>
                <td align="left" valign="top"><?php echo $location['jobassets'];?></td>
                <td align="left" valign="top"><?php echo $location['jobname'];?></td>
                <td align="left" valign="top"><?php echo $location['jobphone'];?></td>
               <td align="left" valign="top"><?php echo $location['jobfax'];?></td> 
               </tr>
          	  <?php } ?>
            </table>        

 

I am getting no output so clearly I am not writing my query correctly, I'm hoping someone can help me with what I am doing incorrectly.

 

[Christian F.]

You have a table for the assets and one for the locations, but no table for the jobs. Thus there is no relation between the two tables you've posted.

That's the main crux of your problem, and to fix it you'll need to design your database properly. Once you've done that, the query will indeed be easy to write.

[elite311]

I'm not understanding what you mean, the locations are the jobs.

 

Would the relation between the 2 tables not be: assets table shows the "currentjob" the asset is on and the location table show the "jobnumber" ?

 

I just want to show how many assets are on each job or "location" if you will.

[Jessica]

SELECT COUNT(assets.id), location.jobname 
FROM location
INNER JOIN assets 
ON assets.currentjob = location.id
GROUP BY location.id

[Christian F.]

If the locations are the jobs, then why do you have the following structure:

Name: location
id (auto increment)
jobnumber

 

Either jobnumber and id are both unique identifiers, in which case the job number should have been the primary key (and id not existed); Each job can have several locations, in which case jobs should be their own table with a many-to-many relation with the locations; Or the job takes place at a location and is not the location itself, which makes sense and means that the location should be a foreign key in the jobs table (and jobs should thusly be in a table of their own).

 

After all, even if you work at Wall-Mart, your job isn't "Wall-Mart" but "sales clerk", "manager" or something like that. Wall-Mart is just the location in which you perform the job.

[elite311]

Thank you very much jesirose for the code makes more sense to me now, that code works great to count how many assets are at each location. However it caused me another problem, the other displayed data that was showing like "jobname" , "jobphone", "jobfax" are not displaying now. Is that because the it's not querying those records now? can I do 2 queries in one statement to be able to get all the data? I read something about a UNION to be able to add more results to the query would this be the best way to go about this?

 

ChristianF

 

Each job we have is only 1 location ever so I didn't see why I would need to have a location table and a job table but instead combine all the data about the job into 1 table and call it location. I see what your saying and yes the job number is the unique identifier so I should get rid of the ID column as I have been using the WHERE command in other query's to the jobnumber.

[Jessica]

Just add them to the fields you select.

[DavidAM]

If you choose not to remove the location.id, because of other application requirements that we are not privy to, be sure to create an index on the location.jobnumber column so your query performance will not fade as the tables increase in size.

 

In any case, you should define an index on assets.currentjob for query performance as well.