Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
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
 
Old October 12th, 2007, 02:00 AM
Authorized User
 
Join Date: Aug 2005
Posts: 64
Thanks: 0
Thanked 0 Times in 0 Posts
Default problem with getting data from 2 tables

hello
i'm stucked in a program of vb6 while fetching data from MS Access
i've 2 tables "ebook1" and "ebook2" having same structure but different data likewise:

ebook1
======

id exp date note amount
------------------------------------------------------
1 7 05/10/07 suzuki car 180000
2 7 06/02/07 paid to mr.A 25000
3 7 06/02/07 1st payment 17000
4 7 01/03/07 2nd payment 315000

ebook2
======

id exp date note amount
------------------------------------------------------
1 7 06/02/07 for cultus 100000
2 7 06/09/07 for alto 50000
3 7 06/30/07 for baleno 215000

note that both tables have a field "exp" having common data

now what i want is 2 create a query that may return data in the following manner

date note amount date note amount
--------------------------------------------------------------
05/10/07 suzuki car 180000 06/02/07 for cultus 100000
06/02/07 paid to mr.A 25000 06/09/07 for alto 50000
06/02/07 1st payment 17000 06/30/07 for balena 215000
01/03/07 2nd payment 315000

note that three columns on the left side contain data from table "ebook1"
and three columns on the right side contain data from table "ebook2"
the criteria for this will be the value of the field "exp", i.e if i pass value "7" for this field then it may return the above mentioned data
doesnt matter if a change in the table structure is required, i just want the solution

plz help me regarding this matter, it'z really very important for me
i'll w8 for a good reply

 
Old October 12th, 2007, 07:00 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

hi there.. try something like this:

SELECT a.date,a.note,a.amount,b.date,b.note,b.amount
FROM ebook1 as a LEFT JOIN ebook2 as b on a.id = b.id
WHERE a.exp = 7



HTH

Gonzalo

================================================== =========
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
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========
 
Old October 12th, 2007, 01:05 PM
Authorized User
 
Join Date: Aug 2005
Posts: 64
Thanks: 0
Thanked 0 Times in 0 Posts
Default

thanks alot friend, it worked !!!

 
Old October 12th, 2007, 01:17 PM
Authorized User
 
Join Date: Aug 2005
Posts: 64
Thanks: 0
Thanked 0 Times in 0 Posts
Default

oopppsssss

it works only when no. of records of table ebook2 are less than or equal to ebook1, as i increased no. of records in ebook2, it didn't display the desired results, plz check it

i'll w8 4 a good reply

 
Old October 12th, 2007, 02:29 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Depending on what you are trying to accomplish, instead of waiting, try a RIGHT OUTER JOIN if you want all the rows in ebook2 and those that match from ebook1. As you've seen, the LEFT OUTER JOIN returns all the rows from ebook1 and those that match from ebook2. If you want both, try a FULL OUTER JOIN ...

Just out of curiosity, what is the nature of the relationship between these two tables that you would want to present them the way you've asked? Specifically, what does the 'id' column in each table mean and how does the id in one table relate to the id in the other?

In other words, you said that this was desired output:

05/10/07 suzuki car 180000 06/02/07 for cultus 100000

why isn't this:

05/10/07 suzuki car 180000 06/09/07 for alto 50000

??



Jeff Mason
je.mason@comcast.net
 
Old October 12th, 2007, 05:51 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

you better follow Jeff ideas.. your first problem was clearly a left join, but if you want a set of mixed data you should use a full outer join..

This blog could help you a lot
http://www.codinghorror.com/blog/archives/000976.html

HTH

Gonzalo

================================================== =========
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
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========





Similar Threads
Thread Thread Starter Forum Replies Last Post
Getting data from link tables psnow1985 SQL Server ASP 0 March 27th, 2008 03:19 PM
Insert Data into two tables Lofa PHP Databases 4 July 7th, 2007 10:36 PM
data from multiple tables keyvanjan Classic ASP Basics 1 January 24th, 2006 06:32 PM
Getting data from two tables pajer SQL Server 2000 8 September 30th, 2004 10:45 AM
Getting data from tables MattLeek Access VBA 2 October 8th, 2003 11:22 AM





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