Hi Rasta, I don't know how you'll use the actual data, but for this specific data the following query may help.
select T3.JobNo, T2.BlNumber, T3.Invoice from (select jobno, BLNUMBER, convert(int,SUBSTRING(BLNUMBER,3,len(BLNUMBER))) JoinVal from Table2) T2 Right outer join (SELECT JOBNO, INVOICE, convert(int,SUBSTRING(INVOICE, 4, LEN(INVOICE))) JoinVal FROM TABLE3) T3 on T2.JoinVal = T3.JoinVal Waqas... On Fri, 6 Aug 2004 17:39:16 +0800, Jominix <[EMAIL PROTECTED]> wrote: > Hello Group, > > I have 3 Tables and want to combine them to achive the result below. > > TABLE1.JOBNO | TABLE2.BLNUMBER | TABLE3.INVOICE > ------------------------------------------------------------------------------------------------------------------------ > 1 BL01 > INV01 > 1 BL02 > INV02 > 1 > INV03 > 1 > INV04 > 1 > INV05 > > ---------------------------------------------------------------------------------------------------------------------- > > BELOW ARE THE TABLE FIELDS : > > TABLE 1 | TABLE 2 | TABLE3 > -------------------------------------------------------------- > JOBNO | JOBNO | JOBNO > | BLNO | INVOICE > > Where table1 contains 1 record, > JOBNO > 1 > > And table2 contains 3 records > JOBNO BLNUMBER > 1 BL01 > 1 BL02 > > and table3 contains 5 records. > JOBNO INVOICE > 1 INV01 > 1 INV02 > 1 INV03 > 1 INV04 > 1 INV05 > > I'm using SQL Server 7. Any idea on how to query this? or any other options to > achieve the result above? Thanks. > > Rasta ------------------------ Yahoo! Groups Sponsor --------------------~--> Make a clean sweep of pop-up ads. Yahoo! Companion Toolbar. Now with Pop-Up Blocker. Get it for free! http://us.click.yahoo.com/L5YrjA/eSIIAA/yQLSAA/k7folB/TM --------------------------------------------------------------------~-> '// ======================================================= Rules : http://ReliableAnswers.com/List/Rules.asp Home : http://groups.yahoo.com/group/vbHelp/ ======================================================= Post : [EMAIL PROTECTED] Join : [EMAIL PROTECTED] Leave : [EMAIL PROTECTED] '// ======================================================= Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/vbhelp/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
