Ad:

MS SQL Discussion Thread
Unsolved
Views: 1108

Page 1
2
Next

You are currently viewing page 1 of this multi-page discussion thread

Jul 6th, 2009

Simple query I cannot figure out

Expand Post »

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

MS SQL Syntax (Toggle Plain Text)
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
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.

Similar Threads

Simple Multiple Update Query from HTML textboxes in PHP
OMG simple update query not working. PLEASE HELP! in MySQL
php query headers in PHP
Newbie - looping and array [very simple] problem in PHP
showing the form after insertind data in PHP

LAMDB

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

18 posts
since Jul 2008

Permalink

Jul 6th, 2009

Re: Simple query I cannot figure out

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.

SQL Syntax (Toggle Plain Text)
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')
 
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')

cgyrob

Reputation Points: 91

Solved Threads: 18

Junior Poster

Offline

125 posts
since Sep 2008

Permalink

Jul 6th, 2009

Re: Simple query I cannot figure out

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

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

18 posts
since Jul 2008

Permalink

Jul 6th, 2009

Re: Simple query I cannot figure out

I gave it a try and I get OR results.

How was this query done prior SQL 2005 INTERSECT?

Thanks.

LAMDB

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

18 posts
since Jul 2008

Permalink

Jul 6th, 2009

Re: Simple query I cannot figure out

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

sql Syntax (Toggle Plain Text)
SELECT data
FROM users, Users_AOI, AOI
WHERE users.userid = Users_AOI.userid
AND AOI.ID = Users_AOI.aoiid
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

sql Syntax (Toggle Plain Text)
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)
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)

Last edited by cgyrob; Jul 6th, 2009 at 4:28 pm.

cgyrob

Reputation Points: 91

Solved Threads: 18

Junior Poster

Offline

125 posts
since Sep 2008

Permalink

Jul 6th, 2009

Re: Simple query I cannot figure out

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.

LAMDB

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

18 posts
since Jul 2008

Permalink

Jul 6th, 2009

Re: Simple query I cannot figure out

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?

cgyrob

Reputation Points: 91

Solved Threads: 18

Junior Poster

Offline

125 posts
since Sep 2008

Permalink

Jul 6th, 2009

Re: Simple query I cannot figure out

Click to Expand / Collapse Quote originally posted by 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?

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.

LAMDB

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

18 posts
since Jul 2008

Permalink

Jul 6th, 2009

Re: Simple query I cannot figure out

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

sql Syntax (Toggle Plain Text)
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.
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.

cgyrob

Reputation Points: 91

Solved Threads: 18

Junior Poster

Offline

125 posts
since Sep 2008

Permalink

Jul 6th, 2009

Re: Simple query I cannot figure out

Click to Expand / Collapse Quote originally posted by 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
sql Syntax (Toggle Plain Text)
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.
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.

LAMDB

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

18 posts
since Jul 2008

Page 1
2
Next

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: Sql Server 2008 Reporting services

Next Thread in MS SQL Forum Timeline: Error while transfering text data type data through linked server

DaniWeb Message

Cancel Changes

User Name
Password