Thanks Waqas, actually TABLE2(BL) AND TABLE3(Invoice) are not related to each other, TABLE1(JOB) is the Parent table with a primary key of JOBNO and the other two tables are linked to the Parent Table.
Table2 and Table3 can have a multiple number of records related to the parent table or none at all. I need the data for my client report requirements. I'm using crystal report 9, I can achieve the result below by doing a loop and store the data in a temporary table, but I don't know if this is achievable using pure SQL query, or maybe in Crystal report? Rasta Jominix ----- Original Message ----- From: "Waqas Hussain" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Sunday, August 08, 2004 12:29 PM Subject: Re: [vbhelp] SQL Complex Query > 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 > > > > > '// ======================================================= > 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 > > > > > > ------------------------ Yahoo! Groups Sponsor --------------------~--> Yahoo! Domains - Claim yours for only $14.70 http://us.click.yahoo.com/Z1wmxD/DREIAA/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/
