SQL query with multiple 'WHERE IN' values?

_sh

Guys,

Its been a while since I last visited here, and the site's SO much faster now, great to see because it used to be a great cause of frustration as the forum pages crawled up!

Anyway...I need to write a SQL query that I just cannot get my head around, I'm sure there's a really simple solution but after hours of scratching my head, a simple way of forming it hasn't occurred to me.

So, I've built the following DB structure (simplified example of it):

1: tbl_users
UserID
UserName

2: tbl_interests
InterestID
InterestName

3: tbl_linkuserstointerests
LinkID
LinkUserID
LinkInterestID

I have a form within my website where users can search for users that match one or more interests, so after submitting the form I end up with a checkbox[] array that implodes to "5,3,4,8,2" - how do I take this to find all users that match at least one of the interests?

My gut feeling wants to do something like this...but I know this isn't going to work but will hopefully illustrate something similar to what I am trying to achieve.

SELECT UserID, UserName FROM tbl_users WHERE UserID IN (SELECT LinkUserID FROM tbl_linkuserstointerests WHERE LinkInterestID IN 5,3,4,8,2)

Can anyone help? 😃

p.s. its good to be back!

bradgrafelman

Well for one, I'd get rid of the 'LinkID' column from the tbl_linkuserstointerests table since it's redundantly redundant.

Otherwise, all you should need to do is fix your IN() syntax (you're missing a group of parenthesis).

_sh

bradgrafelman;10980882 wrote:
Well for one, I'd get rid of the 'LinkID' column from the tbl_linkuserstointerests table since it's redundantly redundant.

Otherwise, all you should need to do is fix your IN() syntax (you're missing a group of parenthesis).

So you're saying that my example is actually viable as a query? I've clearly been silly here lol!! Sometimes the obvious is staring you in the face! I'll go and give it a go.

I put in the LinkID as I'll use that unique column to reference the row whilst editing/deleting a relationship between a User and an Interest, otherwise how would you reference a specific row?

p.s. I remember you Brad from past conversations where you've been a great help 😃

bradgrafelman

@sh wrote:
So you're saying that my example is actually viable as a query?

The theory/logic behind it is indeed one way to go about doing what you're asking, yes. Since subqueries can often be re-written using JOINs instead, however, there are alternative queries.

@sh wrote:
how would you reference a specific row?

Does it make sense for a given user to have the same interest recorded twice? If not, then you should have a UNIQUE index on the user and interest ID columns.

Depending upon the needs/functionality of your application(s), that UNIQUE index could be promoted to being the PRIMARY KEY instead.

johanafm

Because you know both LinkUserID and LinkInterestID, and these could be used as primary key for the table instead of LinkID which means you can also always uniquely reference such a row by using LinkUserID and LinkIterestID. Which in turn means you can simply drop LinkID.

_sh

johanafm;10980887 wrote:
Because you know both LinkUserID and LinkInterestID, and these could be used as primary key for the table instead of LinkID which means you can also always uniquely reference such a row by using LinkUserID and LinkIterestID. Which in turn means you can simply drop LinkID.

I see your method but I always personally prefer to use a single identifiable unique column for any SQL procedures, in my opinion it is neater, I guess we all have our own preferences.

p.s. I corrected the parenthesise and my query does indeed work...next time I'll try before asking 😃 sorry guys, but thanks!

Roger_Ramjet

Just to pick up on the last point: it is good practice to have a unique ID column in all tables regardless of other indexes. So, have your autoincrement ID column and have a multi-column unique index on LinkUserID and LinkIterestID as well.

johanafm

Roger Ramjet;10980970 wrote:
it is good practice to have a unique ID column in all tables

Would you care to elaborate?

I agree that it can be good to have an ID field which serves only as a unique identifier.
- You may lack other information that can be guaranteed to uniquely identify a row. There might for example be 2 John Doe living on Street Avenue 1.
- You may have information that uniquely identifies a row, but which might also change in the future, while your specific id column will be forever persistent.
- You may have information that uniquely identifies a row such as an email address in a one account per email address system, but would rather use a smaller identifier (4-8 bytes vs 50-100 bytes) and index.

But, when the only data in a table is two such unique identifiers, why would you ever want to add another? I fail to see how you'd ever want to ask yourself quetions like what person and phone number relates to 5. You are however likely to ask which people can be reached on this phone number, or which phone numbers belong to a certain person.