Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old September 28th, 2007, 04:40 AM
Registered User
 
Join Date: Sep 2007
Location: , , Philippines.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to pit_bait
Default joining tables

i have 3 tables as below;

table a
-------
EmpNo EmpName
----- -------
380 khalis
400 rashid

table b
-------
EmpNo IncCode Amount
----- ------- ------
380 111 100
380 222 150
400 111 50

table c
-------
EmpNo DedCode Amount
----- ------- ------
380 001 25
400 001 50
400 002 35

how to join all the above table to get the result like below;

EmpNo EmpName IncCode Amount DedCode Amount
----- ------- ------- ------ ------- ------
380 khalis 111 100 001 25
380 khalis 222 150 <null> <null>
400 rashid 111 50 001 50
400 rashid <null> <null> 002 35

i hope you guys can help me to cater the above problem. your assistance are very highly appreciate.

Reply With Quote
  #2 (permalink)  
Old September 28th, 2007, 06:51 AM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

this should work

SELECT
a.EmpNo,
a.EmpName,
b.IncCode,
b.Amount,
c.DedCode,
c.Amount
From [table a] a
LEFT OUTER JOIN [table b] b ON a.EmpNo = b.EmpNo
LEFT OUTER JOIN [table c] c ON a.EmpNo = c.EmpNo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for:
Professional Search Engine Optimization with ASP.NET
Professional IIS 7 and ASP.NET Integrated Programming
Wrox Blox: Introduction to Google Gears
Wrox Blox: Create Amazing Custom User Interfaces with WPF and .NET 3.0
================================================== =========
Reply With Quote
  #3 (permalink)  
Old September 28th, 2007, 07:29 AM
Authorized User
 
Join Date: Dec 2006
Location: Mumbai, Maharastra, India.
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I think u have to add one more field to [table b] and[table c] to indicate the record no in particular employee in that table.

Now format of table b should be -

table b
-------
id EmpNo IncCode Amount
----- ------- ------
1 380 111 100
2 380 222 150
1 400 111 50

and format of table c will be -

table c
-------
id EmpNo DedCode Amount
----- ------- ------
1 380 001 25
1 400 001 50
2 400 002 35

And Run this Query for your required Result -

SELECT M.EMPNO,A.EMPNAME,B.INCCODE,B.AMOUNT,C.DEDCODE,C.A MOUNT
FROM
(
SELECT ID,EMPNO FROM TABLE_B
UNION
SELECT ID,EMPNO FROM TABLE_C
) M
LEFT OUTER JOIN TABLE_A A ON M.EMPNO=A.EMPNO
LEFT OUTER JOIN TABLE_B B ON M.ID=B.ID AND M.EMPNO=B.EMPNO
LEFT OUTER JOIN TABLE_C C ON M.ID=C.ID AND M.EMPNO=C.EMPNO
ORDER BY 1




Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help joining tables and restricting records bren582 SQL Server 2005 3 January 18th, 2008 02:45 PM
Joining Three Tables Alexpizzoferro Classic ASP Databases 5 September 1st, 2006 04:08 PM
problem joining tables using vfpoledb in c# ACE2084 ADO.NET 2 April 1st, 2006 03:10 AM
joining 3 tables koskalgr MySQL 5 March 24th, 2006 09:52 AM
Joining two tables rajustha SQL Server 2000 1 December 7th, 2003 07:38 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.