MS SQL RSS MS SQL RSS

Simple query I cannot figure out

Reply
1 2

•
•
Join Date: Jul 2008
Posts: 18
Reputation: LAMDB is an unknown quantity at this point 
Solved Threads: 0
LAMDB LAMDB is offline Offline
Newbie Poster

Simple query I cannot figure out

 
0
  #1
Jul 6th, 2009
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) 
  1. SELECT 
  2. Users.Id, Users.FN, etc
  3. FROM
  4. Users LEFT JOIN Users_AOI ON User.ID = Users_AOI.UserId
  5. WHERE
  6. Users_AOI.AOIID = x AND
  7. Users_AOI.AOIID = y AND
  8. 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.
Reply With Quote Quick reply to this message  
•
•
Join Date: Sep 2008
Posts: 118
Reputation: cgyrob is on a distinguished road 
Solved Threads: 18
cgyrob's Avatar
cgyrob cgyrob is offline Offline
Junior Poster

Re: Simple query I cannot figure out

 
0
  #2
Jul 6th, 2009
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) 
  1. SELECT Users.Id, Users.FN, etc
  2. FROM Users
  3. JOIN Users_AOI  on Users.userid = Users_AOI.userid
  4. JOIN AOI  on AOI.aoiid = Users_AOI.aoiid
  5. WHERE AOI.interest IN ('x','y',z')
  6.  
Reply With Quote Quick reply to this message  
•
•
Join Date: Jul 2008
Posts: 18
Reputation: LAMDB is an unknown quantity at this point 
Solved Threads: 0
LAMDB LAMDB is offline Offline
Newbie Poster

Re: Simple query I cannot figure out

 
0
  #3
Jul 6th, 2009
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.
Reply With Quote Quick reply to this message  
•
•
Join Date: Jul 2008
Posts: 18
Reputation: LAMDB is an unknown quantity at this point 
Solved Threads: 0
LAMDB LAMDB is offline Offline
Newbie Poster

Re: Simple query I cannot figure out

 
0
  #4
Jul 6th, 2009
I gave it a try and I get OR results.

How was this query done prior SQL 2005 INTERSECT?

Thanks.
Reply With Quote Quick reply to this message  
•
•
Join Date: Sep 2008
Posts: 118
Reputation: cgyrob is on a distinguished road 
Solved Threads: 18
cgyrob's Avatar
cgyrob cgyrob is offline Offline
Junior Poster

Re: Simple query I cannot figure out

 
0
  #5
Jul 6th, 2009
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) 
  1. SELECT data
  2. FROM users, Users_AOI, AOI
  3. WHERE users.userid = Users_AOI.userid
  4. 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) 
  1. SELECT data
  2. FROM users, Users_AOI, AOI
  3. WHERE users.userid = Users_AOI.userid
  4. AND AOI.ID = Users_AOI.aoiid 
  5. 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.
Reply With Quote Quick reply to this message  
•
•
Join Date: Jul 2008
Posts: 18
Reputation: LAMDB is an unknown quantity at this point 
Solved Threads: 0
LAMDB LAMDB is offline Offline
Newbie Poster

Re: Simple query I cannot figure out

 
0
  #6
Jul 6th, 2009
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.
Reply With Quote Quick reply to this message  
•
•
Join Date: Sep 2008
Posts: 118
Reputation: cgyrob is on a distinguished road 
Solved Threads: 18
cgyrob's Avatar
cgyrob cgyrob is offline Offline
Junior Poster

Re: Simple query I cannot figure out

 
0
  #7
Jul 6th, 2009
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?
Reply With Quote Quick reply to this message  
•
•
Join Date: Jul 2008
Posts: 18
Reputation: LAMDB is an unknown quantity at this point 
Solved Threads: 0
LAMDB LAMDB is offline Offline
Newbie Poster

Re: Simple query I cannot figure out

 
0
  #8
Jul 6th, 2009
•
•
•
•
Originally Posted by cgyrob View Post
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.
Reply With Quote Quick reply to this message  
•
•
Join Date: Sep 2008
Posts: 118
Reputation: cgyrob is on a distinguished road 
Solved Threads: 18
cgyrob's Avatar
cgyrob cgyrob is offline Offline
Junior Poster

Re: Simple query I cannot figure out

 
0
  #9
Jul 6th, 2009
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) 
  1. SELECT DATA
  2. FROM users, Users_AOI, AOI
  3. WHERE users.userid = Users_AOI.userid
  4. AND AOI.ID = Users_AOI.aoiid 
  5. AND Users_AOI.aoiid = option1
  6. AND Users_AOI.aoiid = option2
  7. 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.
Reply With Quote Quick reply to this message  
•
•
Join Date: Jul 2008
Posts: 18
Reputation: LAMDB is an unknown quantity at this point 
Solved Threads: 0
LAMDB LAMDB is offline Offline
Newbie Poster

Re: Simple query I cannot figure out

 
0
  #10
Jul 6th, 2009
•
•
•
•
Originally Posted by cgyrob View Post
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) 
  1. SELECT DATA
  2. FROM users, Users_AOI, AOI
  3. WHERE users.userid = Users_AOI.userid
  4. AND AOI.ID = Users_AOI.aoiid 
  5. AND Users_AOI.aoiid = option1
  6. AND Users_AOI.aoiid = option2
  7. 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.
Reply With Quote Quick reply to this message  
Reply
1 2

Quick Reply to Thread
This thread is more than three months old.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Similar Threads
Other Threads in the MS SQL Forum
Thread Tools Search this Thread



connectingtodatabaseinuse count cursor database datepart deadlock delete_trigger highperformancecomputing hpc hpcserver2008 ibm iis loop microsoft ms multithreading news permission query server sql sqlserver sqlserver2005 supercomputing uniqueid update view
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC