Simple query I cannot figure out

[LAMDB]

Hi all,

There is a (I assume) simple query i cannot figure out (in a best practice way).

Let's say I have three tables:

• Users

• AOI (Areas of Interest)

• Users_AOI (Simply containing userId and AOIId for all interests for each user)

So far so good.

Now I want to select all users that have interest of 'x' and 'y' and 'z'. Simple right? How do I do it?

My original attempt was

SELECT 
Users.Id, Users.FN, etc
FROM
Users left join Users_AOI on User.ID = Users_AOI.UserId
WHERE
Users_AOI.AOIID = x and
Users_AOI.AOIID = y and
Users_AOI.AOIID = z

But ofcourse it returns NOTHING because there is no AOIID that equals x, y, and z at the same time.

What would be the "Best Practice Way" of handling these type of queries?

Any help will be highly appreciated.

Thanks.

[cgyrob]

I would do something like the following. This will give you all the records for the users that have any of the 3 AOI and with removing the left join you will not retrieve all the null returns. You can also make the join to the AOI table if you want further information.

Select Users.Id, Users.FN, etc
FROM Users
Join Users_AOI  on Users.userid = Users_AOI.userid
Join AOI  on AOI.aoiid = Users_AOI.aoiid
Where AOI.interest in ('x','y',z')

[LAMDB]

Will IN('x', 'y', 'z') be treated as 'AND' or 'OR'.

I was exploring the SQL INTERSECT statement, but I am afraid the query might take too long if lots of Areas Of Interest are pre-selected.

Thanks.

[LAMDB]

I gave it a try and I get OR results.

How was this query done prior SQL 2005 INTERSECT?

Thanks.

[cgyrob]

the 'IN' statement is an 'OR' statement.

Basically it reads if value = 'x' or value = 'y' or value = 'z'

Intersect gives you the intersection of two seperate select statements.

This will give all the records associated to a user for each area of interest in the Users_AOI table

Select data
From users, Users_AOI, AOI
Where users.userid = Users_AOI.userid
and AOI.ID = Users_AOI.aoiid

If you want to limit the number of AOI to display the IN statement will be the most proficient. You can use a Select statement for the clause but you will require something within the Users_AOI table to use as a delimiter. Either the Id which you would require to list each id you wish to display or add another field to limit by.

Users_AOI
userid
AOIid
AOItype

Select data
From users, Users_AOI, AOI
Where users.userid = Users_AOI.userid
and AOI.ID = Users_AOI.aoiid 
and Users.userid in (select userid from Users_AOI where users_AOI.type = value)

[LAMDB]

Maybe I did not explain myself correctly.

I have an advance search page (web page) showing (among other fields) all possible Areas Of Interest.

Users will check multiple AOI (say: x,y,z) checkboxes and submit the form.

Selecting multiple checkboxes means: "Get all users that have AOI x and y and z". Then I should display all users that have previously selected the three options as areas of interest.

I am trying to avoid selecting more data than what I need and traverse sequencially in the code (c#) discarting what I do not need.

Thanks again for all your help.

[cgyrob]

Just to clarify,

when the user posts the form you want to retrieve all the users that have previously selected the exact same AOI (x and y and z) or any of the selected AOI(x or y or z)

Also the list of AOI is it a dynamic list that can be added to by the user or static list with limited choices?

[LAMDB]

Just to clarify,

when the user posts the form you want to retrieve all the users that have previously selected the exact same AOI (x and y and z) or any of the selected AOI(x or y or z)

Also the list of AOI is it a dynamic list that can be added to by the user or static list with limited choices?

At registration, users select multiple Areas Of Interest and these get saved on the DB. The administrator will then want to select all users that have certain interests (lets say, music, art, etc).

So, lets put a concrete example. John and Paul register.
John selects music and painting as areas of interest and Paul selects only music.

Later, when the admin runs a query such as "Give me all users that like music AND painting" John MUST appear in the list and Paul must NOT.

The list of AOI is dynamically created, but only the admin can add/remove/update Areas.

Thanks.

[cgyrob]

If you are trying to create an exclusive list then 'AND' is the way to go. The key will be to build the statement dynamically in the code so it is only querying for the selected fields.

Build something similar to this

SELECT DATA
FROM users, Users_AOI, AOI
WHERE users.userid = Users_AOI.userid
AND AOI.ID = Users_AOI.aoiid 
AND Users_AOI.aoiid = option1
AND Users_AOI.aoiid = option2
etc.

There is no way around having to dynamically build the statement as this is a dynamic request so a static SQL statement can't be created to encompass all possible resultsets.

[LAMDB]

If you are trying to create an exclusive list then 'AND' is the way to go. The key will be to build the statement dynamically in the code so it is only querying for the selected fields.

Build something similar to this

SELECT DATA
FROM users, Users_AOI, AOI
WHERE users.userid = Users_AOI.userid
AND AOI.ID = Users_AOI.aoiid 
AND Users_AOI.aoiid = option1
AND Users_AOI.aoiid = option2
etc.

There is no way around having to dynamically build the statement as this is a dynamic request so a static SQL statement can't be created to encompass all possible resultsets.

If you take a look at my first post, I used basically the same code you proposed but it does not work. Take a look at my original post.

[cgyrob]

What you need to do is build the query for only the selected options within the code block. If you build a static select query with all the options then it will return nothing because no user will select all the options.

In the code you will have to pull all the options the user selected and build the query to include only those options. Also don't use the left join from the original query as that will give null results from the Users_AOI table which you don't want.

[cgyrob]

If you have questions on how to dynamically build queries is C#, make a post in the C# forum.

[LAMDB]

If you have questions on how to dynamically build queries is C#, make a post in the C# forum.

I am building the query dynamically in C# ( c# is not the issue here) with only the options selected by the user.

However, like I said in my previous example, if a user pre-selected "music" and "paiting" you cannot ask:

... 
where 
AOI = music AND
AOI = Painting

Even if the user preselected these two Areas Of Interest there are in two separate records, and there is no record that is music AND painting at the same time, therefore, the query (even when dynamically created) will return nothing.

Thanks.

[cgyrob]

I see what you mean, I am in the middle of something but i will work on a query for you.

[LAMDB]

I see what you mean, I am in the middle of something but i will work on a query for you.

Thanks so much. I appreciate it.

I will post what I have done so far later today.

Thanks again.

[cgyrob]

As you mentioned in an earlier post Intersect is the way to go if you are using SQL Server 2005 or later.

select a.name,a.userid 
from users a, aoi b, users_aoi c
Where a.userid = c.userid 
and b.aoiid = c.aoiid
and b.aoiid = option1
Intersect
select a.name, a.userid
from users a, aoi b, users_aoi c
Where a.userid = c.userid 
and b.aoiid = c.aoiid
and b.aoiid = option2

Ofcourse if you are still using SQL Server 2000 you do not have access to Intersect. I have put together a query that I beleive will do the trick for you, give it a try and let me know.

SELECT distinct a.userid,a.name
FROM Users a,efx_aoi b ,users_aoi c
Where a.userid = c.userid
and b.aoiid = c.aoiid
and EXISTS
(
SELECT 1
FROM users_aoi c
WHERE a.userid = c.userid
and c.aoiid = 3
and exists(SELECT 1
FROM users_aoi c
WHERE a.userid = c.userid
and c.aoiid = 4)
)

You will have to build the exists portion dynamically to include the options selected.

hope this helps