Guys,

I have records from table 1. the objective is to get the
date from Table 2. when i try to use select statement to obtain the records
it pull all itemnumber with the same item number in TABLE 2. is there any idea to to this using the result below. thanks.

TABLE 1	
ItemNumber	Ponumber
P300-1110-C731-U	8933867
P300-1110-C731-U	8933867
P300-1110-C731-U	8402605


TABLE 2		
PONumber	ItemNumber	Date
9095105	P300-1110-C731-U	8/31/2011
9095105	P300-1110-C731-U	8/31/2011
9095105	P300-1110-C731-U	8/31/2011
9095105	P300-1110-C731-U	8/31/2011
8402605	P300-1110-C731-U	3/31/2011
8402605	P300-1110-C731-U	3/31/2011
8402605	P300-1110-C731-U	3/31/2011
8402605	P300-1110-C731-U	3/31/2011
8402605	P300-1110-C731-U	3/31/2011
8402605	P300-1110-C731-U	3/31/2011
8933867	P300-1110-C731-U	7/30/2011
8933867	P300-1110-C731-U	7/30/2011
8933867	P300-1110-C731-U	7/30/2011
8933867	P300-1110-C731-U	7/30/2011


RESULT:		
ItemNumber	Ponumber	Date
P300-1110-C731	8933867	7/30/2011
P300-1110-C731	8933867	7/30/2011
P300-1110-C731	8402605	3/31/2011

Regards,

JOV

You need to join both the tables based on ItemNumber or Ponumber or both based on your database structure.

Thanks for your reply.

I used sub query.

(Select max(date) from table2 as t2 where t1.itemnumber =t2.itemnumber
and t1.ponumber = t2.ponumber) as pomonth

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.