Databases
StackOverflow2013
Postsdbo
POLinking a table to a second table and pulling data from the first one to the second one multiple times
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POLinking a table to a second table and pulling data from the first one to the second one multiple times
    primarykey
    data
    text
    <p>I have 2 tables 1 for location and 1 for staff. In the location table I have 3 fields: contact1, contact2 and partner. I need each of these to select a name, email and phone number from the staff table and display it. I can only seem to get it to pull one contact at a time. Here is what I have</p> <pre><code>SELECT officelocations_tbl.*, staff_tbl.*, city_tbl.*, state_tbl.* FROM officelocations_tbl JOIN city_tbl ON officelocations_tbl.cityID = city_tbl.cityID JOIN state_tbl ON officelocations_tbl.stateID = state_tbl.stateID JOIN staff_tbl ON staff_tbl.staffID = officelocations_tbl.contact1 </code></pre> <p>That only displays the office information and the one contact I want it to do something like this</p> <pre><code>SELECT officelocations_tbl.*, staff_tbl.*, city_tbl.*, state_tbl.* FROM officelocations_tbl JOIN city_tbl ON officelocations_tbl.cityID = city_tbl.cityID JOIN state_tbl ON officelocations_tbl.stateID = state_tbl.stateID JOIN staff_tbl ON staff_tbl.staffID = officelocations_tbl.contact1 JOIN staff_tbl ON staff_tbl.staffID = officelocations_tbl.contact2 JOIN staff_tbl ON staff_tbl.staffID = officelocations_tbl.partner </code></pre> <p>doing this however gives me an error</p> <p>Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in .....</p> <p>Is there another way to list them by using the staffID and linking it to the three different fields in another table?</p> <p>I tried the solution on here <a href="https://stackoverflow.com/questions/11175901/how-to-select-data-from-multiple-tables-using-joins-subquery-properly-php-mysq">How to select data from multiple tables using joins/subquery properly? (PHP-MySQL)</a> but it wouldn't recognize the second select statement in the from section. I also tried the concat and it said it wasn't a valid sql query, it was the same for the inner join. I'm using php/mysql database. The message I posted above is what I kept getting when using any of the examples on that page. The only thing that changed was the line the error was being thrown on.</p> <p>I was thinking of just creating 4 separate sql statements. I know there is a way to do this but the ones I've tried don't seem to work.</p> <p>Thank you for any help.</p> <p>Edited after assistance rendered below</p> <p>ok So I got it to display but with one minor issue when I tell it to display the contact information for sf1 it is only showing 2 entries when there should be 13. I have a total of 29 locations that I want to be able to display not all locations have contact1 or contact2 but all have a partner. Here is the code I have edited to reflect your suggestions:</p> <pre><code> $sql_locations = "SELECT officelocations_tbl.*, sf1.firstName AS c1Firstname, sf1.lastName AS c1lastName, sf1.middleInitial AS c1middleInitial, sf1.suffix AS c1suffix, sf1.accredations AS c1accredations, sf2.firstName AS c2Firstname, sf2.lastName AS c2lastName, sf2.middleInitial AS c2middleInitial, sf2.suffix AS c2suffix, sf2.accredations AS c2accredations, sf3.firstName AS c3Firstname, sf3.lastName AS c3lastName, sf3.middleInitial AS c3middleInitial, sf3.suffix AS c3suffix, sf3.accredations AS c3accredations, city_tbl.*, state_tbl.* FROM officelocations_tbl JOIN city_tbl ON (officelocations_tbl.cityID = city_tbl.cityID) JOIN state_tbl ON (officelocations_tbl.stateID = state_tbl.stateID) JOIN staff_tbl sf1 ON (sf1.staffID = officelocations_tbl.contact1) JOIN staff_tbl sf2 ON (sf2.staffID = officelocations_tbl.contact2) JOIN staff_tbl sf3 ON (sf3.staffID = officelocations_tbl.partner)"; $result_loc = mysql_query($sql_locations); while ($db_field = mysql_fetch_assoc($result_loc)) { if ($db_field['c2Firstname'] == ""){ print $db_field['officeName'] . "&lt;BR&gt;"; print $db_field['address1'] . "&lt;BR&gt;"; print $db_field['cityName'] . ", " . $db_field['state_abreviation'] . " " . $db_field['zipCode']."&lt;BR&gt;"; print $db_field['c1Firstname'] . " " . $db_field['c1lastName'] . " ". $db_field['c1middleInitial'] . " ". $db_field['c1suffix']. " ". $db_field['c1accredations'] . "&lt;BR&gt;&lt;BR&gt;&lt;BR&gt;&lt;BR&gt;"; print $db_field['c3Firstname'] . " " . $db_field['c3lastName'] . " ". $db_field['c3middleInitial'] . " ". $db_field['c3suffix']. " ". $db_field['c3accredations'] . "&lt;BR&gt;"; }else if ($db_field['c2Firstname'] != ""){ print $db_field['officeName'] . "&lt;BR&gt;"; print $db_field['address1'] . "&lt;BR&gt;"; print $db_field['cityName'] . ", " . $db_field['state_abreviation'] . " " . $db_field['zipCode']."&lt;BR&gt;"; print $db_field['c1Firstname'] . " " . $db_field['c1lastName'] . " ". $db_field['c1middleInitial'] . " ". $db_field['c1suffix']. " ". $db_field['c1accredations'] . "&lt;BR&gt;"; print $db_field['c2Firstname'] . " " . $db_field['c2lastName'] . " ". $db_field['c2middleInitial'] . " ". $db_field['c2suffix']. " ". $db_field['c2accredations'] . "&lt;BR&gt;"; print $db_field['c3Firstname'] . " " . $db_field['c3lastName'] . " ". $db_field['c3middleInitial'] . " ". $db_field['c3suffix']. " ". $db_field['c3accredations'] . "&lt;BR&gt;&lt;BR&gt;&lt;BR&gt;&lt;BR&gt;"; } </code></pre> <p>I did try to have the if statement say </p> <pre><code> if ($db_field['c1Firstname'] != "" &amp;&amp; $db_field['c2Firstname'] == "") </code></pre> <p>but it didn't seem to work either.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USpuddleJumper
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload