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

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
================================================== =========

thanks alot friend, it worked !!!

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

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

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
================================================== =========