subquery

Last Post 30 Nov 2010 09:27 AM by gabling. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
paps
New Member
New Member

--
06 Oct 2010 08:40 AM

Hi Guys,

I am trying to select rows from my Lab1 table based on the rows from another but with distinct command.

select * from Lab1 where Lab1.orderno = (select distinct(Lab2.orderno) from Lab2 where lab2.dt <= '10/4/2010' and Lab2.dt >= '10/4/2010')

The only problem is it wont work. LOL

What is wrong with the above select statement

Tnx

Paps

rm
New Member
New Member

--
06 Oct 2010 02:46 PM
Got any error or wrong result?
gunneyk
New Member
New Member

--
08 Oct 2010 12:59 PM
Try using an EXISTS instead of =
nanda_krc
New Member
New Member

--
03 Nov 2010 08:59 PM
select * from Lab1 where Lab1.orderno In (select distinct(Lab2.orderno) from Lab2 where lab2.dt <= '10/4/2010' and Lab2.dt >= '10/4/2010')

gabling
New Member
New Member

--
30 Nov 2010 09:27 AM
I've had performance issues using the IN command (esp not in) and try to use joins instead.  See below:

SELECT *
FROM Lab1 o
JOIN Lab2 t ON t.Orderno = o.OrderNo
WHERE t.dt <= '10/4/2010' AND t.dt >= '10/4/2010'

The Lab2 table filter though where you specify the date...  Wouldn't the expression be valid for any value of dt?  The query looks for anything less than or equal to 10/4/2010 and anything greater than or equal to 10/4/2010.  I would imagine in this case you would have the same result set even if you did not specify dt criteria in the where clause.   



You are not authorized to post a reply.

Acceptable Use Policy