I am trying to join 2 tables and then display all the results.  What
is happening is that I am only getting back the results that have a
relationship.  I need to display every record of each table together.
Anyone know how to do this.  My sql statement is below:

sql = "Select tblproduct.storeid, tblproduct.code"
sql = sql & ", tblproduct.thumb, tblproduct.caption, tblproduct.id"
sql = sql & ", tblproduct.mpn, tblproduct.upc, tblMisc.code"
sql = sql & ", tblMisc.id from tblMisc INNER JOIN tblProduct ON"
sql = sql & " tblMisc.id = tblProduct.id where tblProduct.id = " & id

Thanks in advance..

Wayne

Remove the JOIN clause

sql = "Select tblproduct.storeid, tblproduct.code"
sql = sql & ", tblproduct.thumb, tblproduct.caption, tblproduct.id"
sql = sql & ", tblproduct.mpn, tblproduct.upc, tblMisc.code"
sql = sql & ", tblMisc.id from tblMisc, tblProduct where tblProduct.id = " & id

This will return a cross product of every item in each table with the match of tblProduct.id = x.
I doubt this is what you want. You obviously are looking for some kind of relationship between the tables.
If the first, didn't work, then maybe you want this...

sql = "Select tblproduct.storeid, tblproduct.code"
sql = sql & ", tblproduct.thumb, tblproduct.caption, tblproduct.id"
sql = sql & ", tblproduct.mpn, tblproduct.upc, tblMisc.code"
sql = sql & ", tblMisc.id from tblMisc OUTER JOIN tblProduct ON"
sql = sql & " tblMisc.id = tblProduct.id where tblProduct.id = " & id

Hard to tell, as your query is less than precise.
--
Randy Charles Morin
http://www.kbcafe.com

Feel free to contact me by private email or messenger

If u're trying to get every single record of both tables
then you could use UNION but the 2 select statements of
each table must be of equal value. The downside of a UNION
is you don't get to get which line of record belongs to
which table unless you have an indicator between the 2
tables indicating which record belongs to which table.

But then the other way is to use a LEFT OUTER JOIN rather
than an INNER JOIN coz from the SELECT statement, it seems
like u're trying to get the Misc table's code and the info
of the product.

All the best in trying
Cheyenne

tblProduct.id = " & id

Use the FULL OUTER JOIN.

This posting is provided ?AS IS?, with no warranties, and confers no
rights.

Use the FULL OUTER JOIN.

Regards,

This posting is provided ?AS IS?, with no warranties, and confers no
rights.