Sorting results of mysql query over multiple columns

You should restructure the tables. Create a new table called access or something with the fields accessId, residentId and guest.

• accessId is the primary key, it is AUTO_INCREMENT INTEGER.
• resident is the foreign key to the residentlist table.
• guest is whatever you now search using your LIKE condition.

So for your 10 guests per resident, you will have 10 records in that access table.

Once you have that, you can use the query:

SELECT * FROM residentlist LEFT JOIN access ON access.residentId = residentlist.resident WHERE guest LIKE '<search query>';

Your current select statement will return all guests for a resident if a single one matches your search query. So if Bob allows Jack and Jane and you search for Jane, it'll return the entire row of Jack and Jane.

Yes, this should be redesigned to be a separate table that is simply Resident, Guest. The 10 maximum should be enforced on the application side rather than the database. See Shi's answer for an excellent explanation on how to structure it moving forward.

If you need to keep your current structure, you can obtain the desired results through the UNION operator.

SELECT guest1 FROM residentlist WHERE guest1 LIKE 'searchquery' 
UNION SELECT guest2 FROM residentlist WHERE guest2 LIKE 'searchquery' 
...
UNION SELECT guest10 FROM residentlist WHERE guest10 LIKE 'searchquery' 

I feel like the database could be better organized. As a general rule, if you have a field name that is blah1, blah2, ... blahX, that is usually a red flag.

Anyways, sounds like you have two objects: a guest and a resident, and then a relationship between them.

So, you can have a resident table that has information about the resident and a unique identifier for the resident like residentId. So you have:

• ResidentId
• Name
• Address ....

Then you have a guest table that specifies information about the guests:

• GuestId
• Name
• whatever else...

Then you have a third table that actually relates the guest and resident (guestToResidentTable). This will have two fields:

• ResidentId
• GuestId

• you might also want a unique id for each of these relationships - making three fields in this table

  Both id fields are foreign keys from your other tables.

This way every guest will be related to each resident and like Derek said, you enforce the 10 guest limit in your application (not the database).

Then you can search the guest table for all guests like your search query. If you then want information about the guest and what resident he is related to, you just join on the other tables.