MS SQL

MS SQL RSS

Simple query I cannot figure out

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: Sep 2008

Posts: 118

Reputation: cgyrob is on a distinguished road

Solved Threads: 18

cgyrob

Offline

Junior Poster

Re: Simple query I cannot figure out

#11

Jul 6th, 2009

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.

•

Join Date: Sep 2008

Posts: 118

Reputation: cgyrob is on a distinguished road

Solved Threads: 18

cgyrob

Offline

Junior Poster

Re: Simple query I cannot figure out

#12

Jul 6th, 2009

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

Last edited by cgyrob; Jul 6th, 2009 at 9:44 pm.

•

Join Date: Jul 2008

Posts: 18

Reputation: LAMDB is an unknown quantity at this point

Solved Threads: 0

LAMDB

Offline

Newbie Poster

Re: Simple query I cannot figure out

#13

Jul 7th, 2009

•

Originally Posted by cgyrob

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:

Help with Code Tags

MS SQL Syntax (Toggle Plain Text)

... 
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.

•

Join Date: Sep 2008

Posts: 118

Reputation: cgyrob is on a distinguished road

Solved Threads: 18

cgyrob

Offline

Junior Poster

Re: Simple query I cannot figure out

#14

Jul 7th, 2009

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

•

Join Date: Jul 2008

Posts: 18

Reputation: LAMDB is an unknown quantity at this point

Solved Threads: 0

LAMDB

Offline

Newbie Poster

Re: Simple query I cannot figure out

#15

Jul 7th, 2009

•

Originally Posted by cgyrob

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.

•

Join Date: Sep 2008

Posts: 118

Reputation: cgyrob is on a distinguished road

Solved Threads: 18

cgyrob

Offline

Junior Poster

Re: Simple query I cannot figure out

#16

Jul 7th, 2009

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

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

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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)
)
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)
)