CODEWITHSUNDEEP
mysqlinner-join
kyle
kyle

Reputation: 568

mysql inner join same table and same column multiple times from multiple tables

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.

Here is the concept of the database: There is an Entity, Address, Contact, Client, Group and Facility table involved in my query.

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.

The Entity table houses an address record id and a contact record id.

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.

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):

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";

Thanks in advance for the help!

Upvotes: 2

Views: 2012

Answers (1)

skv
skv

Reputation: 1803

Yes, the better way to do this for maintenance purposes is to create a view of only the inner joins and querying the view. Remember in terms of performance there would be little by way of improvement but maintenance of the code would become much easier.

Given your purpose the inner joins are not entirely avoidable unless you decide to change the structure of the tables

Upvotes: 1

Related Questions