extracting values on certain date matching condition

Question

hey friends.
i want to show (extract) product names , which are near to be expired in WInforms in SQL server 2008 of visual studio 2010
I found dis for one table , but i want to fetch data from two tables in single datagridview.

I have two tables PRO and OPN_STK

in PRO
PId        P_name        P_expdate
1          crocin           1-jan-14
2          disprin           2-mar-15
3          vicks             2-jan-14

in OPN_STK
P_oe        P_name        P_oexpdate
1          combiflame    1-jan-14
2             d-cold           2-mar-15
3             Glucose           2-jan-14

i want if today is 18-jul-14 then those product name should be fetched whose exp_date are exactly 6 months before than the date of today(current date of system)

like dis

P_oe        P_name        P_oexpdate

1         combiflame      1-jan-14
2          vicks             2-jan-14
3         crocin             1-jan-14

i tried this code

SELECT P_name from OPN_STK where (P_oexpdate < DateADD(month , -6 , Getdate()))

and this query is giving me desired result for 1 table but when i put join to fetch data from both table its only giving me result from one table only

i wrote
SELECT P_name from OPN_STK,PRO where P_id=P_oe and (P_oexpdate < DateADD(month , -6 , Getdate()))

Help me plz i really need it to be fixed . thanks in advance .

Ramesh Maruthi · Answer

Hello Varsha Instead of writing where condition you can use JOINS and the performance of query will be much better than where query if u use joins Please try this query :) SELECT PRO.P_nameOPN_STK.Pname from PRO INNER JOIN OPN_STK ON PRO.PId = OPN_STK.P_oe AND (P_oexpdate SELECT PRO.P_nameOPN_STK.Pname from PRO FULL OUTER JOIN OPN_STK ON PRO.PId = OPN_STK.P_oe AND (P_oexpdate INNER JOIN: Returns all rows when there is at least one match in BOTH tables FULL JOIN: Return all rows when there is a match in ONE of the tables

varsha dodiya · Answer

@ Ramesh ... i tried ur query above first one is showing nothing but only coulumn name ... and then query of full outer join is showing all the P_name from both table. like dis P_name expr1 combiflame null vicks null null paracitamol null disprin null crocin

varsha dodiya · Answer

hi sujana  what is Orders in query u have given ? its showing error on this word

srujana thallam · Answer

Hi Varsha I think before u write query u can join two table by using full outer joinbecause you want to all the details which are present in two table.like SELECT PRO. P_name  OPN_STK.P_name FROM PRO FULL OUTER JOIN Orders ON PRO.Pid=OPN_STK.P_oe and (P_oexpdate Try like thisthis may solve your problem.