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_idfrom session_product inner join session_addon on session_product.session_product_id = session_addon.session_product_idWhat 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. |
|
|
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. |
|
|
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? |
|
|
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. |
|
|
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! |
|
|
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. |
|
|
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 |
|
|
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. |
|
|
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#CLRI'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! |
|
|
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_idAt 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_idFROM session_product INNER JOIN session_addon ON session_product.session_product_id = session_addon.session_product_idWHERE (session_addon.product_addon_group_id in (76,78)) and session_addon.product_id <> '-1'ORDER BY session_product.session_order_idIf 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 |
|
|
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'" |
|
|
|