JOINing the same table..

Question

Hi there,

Here is my query:

SELECT * FROM PRODUCTS

WHERE

 (COL1= (SELECT COL1 FROM PRODUCTS WHERE PID = @PID)

AND (COL2 = (SELECT COL2 FROM PRODUCTS WHERE PID = @PID))

What I am trying to accomplish is to get the list of all products (including @PID) whose col1 and col2 matches that of @PID.

Problem with this query:

- When @PID has a unique combination of col1 and col2, returned result is blank rather than one (@PID)

- I know I can do better with JOIN instead of subqueries..

Could you please help?

Thanks

---

SK

Answer 1

I don't see any reason why what you have shouldn't work if the row with @PID has a unique set of COL1 and COL2 values.  For example, the following works

Create Table #Products(PID int primary key, COL1 varchar(20), COL2 varchar(20));
Insert #Products(PID, COL1, COL2) Values (1, 'Tom', 'Cooper'), (2, 'John', 'Doe');
Declare @PID int;

Set @PID = 1;
SELECT * FROM #PRODUCTS
WHERE
 (COL1= (SELECT COL1 FROM #PRODUCTS WHERE PID = @PID)
AND (COL2 = (SELECT COL2 FROM #PRODUCTS WHERE PID = @PID)));

Drop Table #Products;

So I suspect you have a data problem or something else going on that is causing you to get 0 rows in your output.  If you can't find anything, perhaps you could post a short demo (something like the above) that demonstrates your problem.  That might help us help you find it.

You are right that it would be more efficient to do a join.  The code you have will go to the table three times.  You can cut out one of those passes by doing

;WITH cte AS
(SELECT COL1, COL2 FROM PRODUCTS WHERE PID = @PID)
SELECT p.* FROM PRODUCTS p
INNER JOIN cte c ON p.COL1 = c.COL1 AND p.COL2 = c.COL2;

Tom

Answer 2

>> What I am trying to accomplish is to get the list of all products (including @in_product_id) whose col1 and col2 matches that of @in_product_idPID.<<

Based on your vague narrative and lack of DDL, and sample data here is my guess: 

SELECT * 
  FROM Products
 WHERE role1_product_id = @in_product_id 
    AND role2_product_id = @in_product_id; 

>> When @in_pruduct_id has a unique combination of col1 and col2, returned result is blank rather than one (@in_pruduct_id) <<

What does that mean?  There is no combination when you have one value. 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL