Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Select data from the same column and table

Author  Topic 

Need2CSharp
Starting Member

9 Posts

Posted - 2007-10-04 : 11:17:56
Hi All,

I need help with a query that uses an "and" condition for records stored in a table with the same column name.

This is the query that returns all the results. I need a specific subset of these results:

select session_product.session_order_id, session_addon.product_addon_group_id, session_addon.product_id
from session_product inner join session_addon on session_product.session_product_id = session_addon.session_product_id

What I need:

The session_product.session_order_id where...

For example, session_addon.product_addon_group_id = '78' AND session_addon.product_addon_group_id = '110' AND session_addon.product_addon_group_id = '133'

Is it possible to select data from the same column of the same table using the "and" condition? If so, can someone please provide the correct method?

Thanks!

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-10-04 : 11:21:16
No...AND means AND so you would get nothing back because 78 does not = 110 which does not = 133. They can't all be equal which is what AND is saying. How can you select a product_addon_group_id that is 78, 110, and 133 all at the same time (for the same record)? Can't be done. Use OR instead of AND.
Go to Top of Page

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-10-04 : 11:24:05
You could also say where product_addon_group_id in (78, 110, 133) instead of using OR.
Go to Top of Page

Need2CSharp
Starting Member

9 Posts

Posted - 2007-10-04 : 11:39:23
Hi,

Thanks for the quick reply. I realize that the "and" means "and"...I was wondering if it can be done in a subquery, or something like it. I'm trying to avoid having to programmatically filter out the results of a query that uses "or" and returns a large result set. I would rather the resources be used on the mssql server, rather than on the web server. Does that make sense?
Go to Top of Page

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-10-04 : 11:44:14
The best thing for performance then would be to create a #temptable or @tablevariable and put your values into it before your SELECT. Then instead of using OR or IN, you can use the #temptable or @tablevariable to link to in your SELECT statement. It's much much faster and better.
Go to Top of Page

Need2CSharp
Starting Member

9 Posts

Posted - 2007-10-04 : 12:07:30
Wow, you just went way over my head! :) I'm definitely not that versed in SQL...

Can you explain in a little more detail, or point me to a resource?

Thanks!
Go to Top of Page

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-10-04 : 15:43:46
I don't know any links to point you to but I'll try to give you the general idea. You are checking that single column product_addon_group_id against a series of values. Create a #temptable(pagid int) and insert all your values into it. Say there are 50 values you want to check for the product_addon_group_id (78, 110, 133, .... 50th value). Well your #temptable will have 50 rows in it. Now in your select just join #temptable to session_addon on product_addon_group_id = pagid. It only pulls back the rows that match. Runs faster and more efficient. Also, if it's a huge list in #temptable, you can always create an index on pagid to make it run faster.
Go to Top of Page

Need2CSharp
Starting Member

9 Posts

Posted - 2007-10-05 : 10:43:34
Van,

Thanks for the clarification; I think I understand now. You've been a great help!

- Tyler
Go to Top of Page

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-10-05 : 11:24:42
No problem. Hope you were able to get it to work.
Go to Top of Page

Need2CSharp
Starting Member

9 Posts

Posted - 2007-10-05 : 12:26:04
For those of you who have read this thread and are looking for a similar solution, I found an excellent article here:

http://www.sommarskog.se/arrays-in-sql-2005.html#CLR

I'm partial to the CLR functions solution, because I write everything in C#. However, the author has done a great job at offering other methods for getting the same results. It's very interesting stuff, whether you're a SQL guru or not.

Fortunately for me, the article deals with my exact problem.

Thanks to Van who pointed me in the right direction, and ultimately lead me to the aforementioned article.

Enjoy!
Go to Top of Page

Need2CSharp
Starting Member

9 Posts

Posted - 2007-10-08 : 11:42:24
Van,

I hope you can clarify the results I'm getting.

I created a user defined table function as per the article I mentioned in the previous post. It's purpose is to take a string of comma delimited Ids and return them as a table. Next, I created a stored procedure that calls the function and joins with other tables. Everything works properly (i.e., no errors are thrown); however, the results are not what I expected. Here's the stored procedure:

SELECT distinct session_product.session_order_id
FROM session_product
INNER JOIN session_addon ON session_product.session_product_id = session_addon.session_product_id
INNER JOIN DatabaseName..Split(@IdString, DEFAULT) AddonIds ON session_addon.product_addon_group_id = AddonIds.str
AND session_addon.product_id <> '-1'
ORDER BY session_product.session_order_id

At first, I thought everything was perfect and I was on the right track. When I ran the "or query", however, I noticed that I got the same results as the store procedure above (which I wanted to return "and results"). Here is the "or query":

SELECT distinct session_product.session_order_id
FROM session_product
INNER JOIN session_addon ON session_product.session_product_id = session_addon.session_product_id
WHERE (session_addon.product_addon_group_id in (76,78)) and session_addon.product_id <> '-1'
ORDER BY session_product.session_order_id

If I run the above, or call the store procedure (exec DatabaseName..Split '76,78'), I get the exact same results. This case is fine because the Ids do exist. If I query for ids 76,78,900, where 900 does NOT exist, I would expect the "or query" to return the same results and the "and query" to return zero results. Unfortunately, it does not...it returns the exact same thing as the "or query."

So, I attempted to do what you suggested by inserting values into a table and joining them. Do you have any insight as to why I'm not getting the correct results? Please help!

Thanks,
Tyler
Go to Top of Page

Need2CSharp
Starting Member

9 Posts

Posted - 2007-10-08 : 11:45:18
Sorry, the call to the stored procedure should be "EXEC GetAddonIds '76,78'", not "DatabaseName..Split '76,78'"
Go to Top of Page
   

- Advertisement -