Databases
StackOverflow2013
Postsdbo
POmysql inner join same table and same column multiple times from multiple tables
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POmysql inner join same table and same column multiple times from multiple tables
    primarykey
    data
    text
    <p>I'm having an issue with a mysql query for a search screen at work. I've got the query working using the code I'll post below, but I'm suspicious there is a better way to do it. Mysql are pretty newbie really, I just figure it out as I go along, or try to.</p> <p>Here is the concept of the database: There is an Entity, Address, Contact, Client, Group and Facility table involved in my query.</p> <p>Each Client, Group and Facility is an "Entity" for lack of a better word. Each Entity has it's own Entity ID in the Entity table.</p> <p>The Entity table houses an address record id and a contact record id.</p> <p>On the facility search screen, if a user searches a phone number I want to search through the client and group records as well as the facility records. And then return any matching facility information as I normally would. </p> <p>Here's what I've got so far(I'm doing nothing for address outside of facility records just yet and I've hardcoded some things for the sake of explaining myself):</p> <pre><code>SELECT facility.FacilityID, facility.Name, contact.PhoneNumber, addy.State addy.ZipCode, facility.ProductionFacilityID, facility.ProductionEntityID FROM Facility facility INNER JOIN ClientGroup cg ON facility.GroupID = cg.GroupID INNER JOIN Client c ON cg.ClientID = c.ClientID INNER JOIN Entity e ON facility.FacilityID = e.EntityID INNER JOIN Entity eg ON cg.GroupID = eg.EntityID INNER JOIN Entity ec ON c.ClientID = ec.EntityID INNER JOIN Contact contact ON e.BillingContactID = contact.ContactID INNER JOIN Contact contactg ON eg.BillingContactID = contactg.ContactID INNER JOIN Contact cc ON ec.BillingContactID = cc.ContactID INNER JOIN Address addy ON addy.AddressID = e.PhysicalAddressID WHERE (facility.FacilityID like '%$searchfor%' OR contactg.PhoneNumber like '%$searchfor%' OR cc.PhoneNumber like '%$searchfor%') AND facility.IsRowActive=1 ORDER BY $searchtype"; </code></pre> <p>Thanks in advance for the help!</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USkyle
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POmysql inner join same table and same column multiple times from multiple tables
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhat is your question? Is this query returning the results that you need?
      singulars
      1. POmysql inner join same table and same column multiple times from multiple tables
      1. USGordon Linoff
    2. COIs there a better or more correct way for me to write this? Surely there must be?
      singulars
      1. POmysql inner join same table and same column multiple times from multiple tables
      1. USkyle
 

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