Get value from one table and lookup in other Table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Get value from one table and lookup in other Table

Author

Topic

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-09-02 : 11:16:06

Hi

I have table (Table1) and (Table2) as below

Table1
-------
INV         PONUM                     CUSTOMER        
IN5046      A219-235.56778-7896        MANIT
IN5047      HJ89956                    Duncan
IN5048      A219-235.56778-7896        MANIT
IN5049      A219-235.56778-7896        MANIT
IN5050      WA56783                    CANVAS
IN5061      TY3567                     Wallstreet
IN5062      GH6788                     Wallstreet

Table2
--------
INV2          CUSTOMER2
IN5046       MANIT
IN5061       Wallstreet

MY question : How do I get INV2 from Table2 for customer 'MANIT' and compare the value with INV in Table1 for customer 'MANIT'. So when INV2 matches with INV in Table1 bring all the value from INV where customer is 'MANIT and Greater than INV2 in Table2.

Advance thanks

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-02 : 12:05:16

Vaishu, when you say "Greater than INV2 in Table2" what field are you referring to that should be greater than? Can you provide the full table structures?

------------------------
Future guru in the making.

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-09-02 : 12:56:02

Hi

INV2 in Table2 store the last INV from Table1 for custmer 'MANIT'. So I can use value from INV2 of table2 as a last record to match with INV in Table1 and then get all values Greater from Table1 (Not from table2, sorry my mistake)for customer 'MANIT'

(i.e) IF INV2.Table2 matches with INV.Table1 Then Select INV.Table1 > INV2.Table2 from Table1

(ex)INV2 is IN5046 , I have matching value INV is IN5046 in Table1. so the expected result from Table1 is below

IN5048
IN5049

quote:
Originally posted by Zoroaster

Vaishu, when you say "Greater than INV2 in Table2" what field are you referring to that should be greater than? Can you provide the full table structures?

------------------------
Future guru in the making.

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-02 : 19:24:37

This might work for you:

SELECT INV FROM TABLE1
JOIN TABLE2 ON
TABLE1.INV > TABLE2.INV2
AND
TABLE1.CUSTOMER = TABLE2.CUSTOMER2
AND TABLE1.CUSTOMER='MANIT'

------------------------
Future guru in the making.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources