innerjoin foreign keys... 1

[burnside]

this is mysql statement-

strsql = "SELECT td.dealid, td.dealdate, td.userid, td.stockid, td.fincoid, td.finco, td.cusid, td.pxid, td.dealnotes, td.sourceid, td.purch, td.disc1, td.disc1id, td.disc2, td.disc2id, td.cussave, td.gmpoints, td.ocost, td.ocostid, td.delcost, td.numcost, td.firstreg, td.fuelcost, td.rflcost, td.rflid, td.hpscost, td.depcost, td.fdacost, td.fdaid, td.dealdeldate, td.dealdeltime, td.ampmid, td.warrid, td.warrcost, td.dealdeldate, td.dealdeltime, " 
'user select
strsql = strsql & "tuse.userid, tuse.usernamefor, tuse.usernamesur, tuse.usersiteid, "
'location select
strsql = strsql &"tloc.locid, tloc.locname, "
'cust select
strsql = strsql &"tc.cusid, tc.cusfname, tc.cuslname, tc.custitleid, tc.cusadd1, tc.cusadd2, tc.cusadd3, tc.custown, tc.cuscounty, tc.cuspostc, tc.cusinitial, "_
'title sel
strsql = strsql &"tbl_title.titleid, tbl_title.title, "
'stock sel
strsql = strsql &"ts.stockid, ts.stockmodid, ts.stockmakeid, ts.stockreg, ts.stockcolid, ts.stocktranid, ts.stockbodyid, ts.stockengsize, ts.stocksiteid, ts.stocktypeid, ts.stockfueltype, ts.stockprc, ts.stockpp, ts.vatstatid, "_
'make/mod sel
strsql = strsql &"tm.make, tmod.model, "
'body tran eng colour fuel
strsql = strsql &"tbod.bodydesc, ttran.trandesc, teng.engsize, tcol.colour, tfuel.fueldesc, "
'deal bits
strsql = strsql &"tdisc.discid, tdisc.disc, twarr.warrid, twarr.warranty, tocost.ocostid, tocost.ocostdesc, tfinco.finid, tfinco.financeco, tfda.fdaid, tfda.fda, tampm.ampmid, tampm.ampm, "
'state/saletype
strsql = strsql &"tbar.option, tstate.statedesc, tvat.vatstat, trfl.rfl, temis.emis "_

& "FROM tbl_deal AS td "_
& "INNER JOIN tbl_users AS tuse ON td.userid = tuse.userid "_
& "INNER JOIN tbl_location AS tloc ON tuse.usersiteid = tloc.locid "_
& "INNER JOIN tbl_customers AS tc ON td.cusid = tc.cusid "_
& "INNER JOIN tbl_title ON tc.custitleid = tbl_title.titleid "_
& "INNER JOIN tbl_stock AS ts ON td.stockid = ts.stockid "_
& "INNER JOIN tbl_make AS tm ON ts.stockmakeid = tm.makeid "_
& "INNER JOIN tbl_model AS tmod ON ts.stockmodid = tmod.modid "_
& "INNER JOIN tbl_body AS tbod ON ts.stockbodyid = tbod.bodyid "_
& "INNER JOIN tbl_tran AS ttran ON ts.stocktranid = ttran.tranid "_
& "INNER JOIN tbl_engsize AS teng ON ts.stockengsize = teng.engsizeid "_
& "INNER JOIN tbl_colour AS tcol ON ts.stockcolid = tcol.colid "_
& "INNER JOIN tbl_fueltype AS tfuel ON ts.stockfueltype = tfuel.fuelid "_
& "INNER JOIN tbl_disc AS tdisc ON td.disc1id = tdisc.discid "_
& "INNER JOIN tbl_warranty AS twarr ON td.warrid = twarr.warrid "_
& "INNER JOIN tbl_ocost AS tocost ON td.ocostid = tocost.ocostid "_
& "INNER JOIN tbl_financeco AS tfinco ON td.fincoid = tfinco.finid "_
& "INNER JOIN tbl_fda AS tfda ON td.fdaid = tfda.fdaid "_
& "INNER JOIN tbl_ampm AS tampm ON td.ampmid = tampm.ampmid "_
& "INNER JOIN tbl_salebar AS tbar ON td.saletypeid = tbar.barid "_
& "INNER JOIN tbl_state AS tstate ON ts.stockstateid = tstate.stateid "_
& "INNER JOIN tbl_rfl AS trfl ON td.rflid = trfl.rflid "_
& "INNER JOIN tbl_emis AS temis ON ts.emisid = temis.emisid "_
& "INNER JOIN tbl_vatstat AS tvat ON ts.vatstatid = tvat.vatstatid "_
& "WHERE dealid= " & tkey

when i started this i was using multiple recordsets and could decide not to run a lookup if the foreign key was 0.
but i changed my design after getting more involved and started using innerjoin

my problem is now if a foreign key is 0 the rs is eof.
I could have record 1 as a 'none' record
but is this the best way to do it?

my default value for all foreign keys is 0 rather than leaving it null - is this a good idea?

 

[TonyGroves]

Do you mean you want to return a record even where the foreign key does not match any record in the joined second table? In that case you could use an outer join. For example:
[tt]
SELECT *
FROM tbl1 LEFT JOIN tbl2 ON tbl1.t2id=tbl2.id
[/tt]
will always return at least one record for each tbl1 record. In the cases where there is no match with tbl2, the tbl2 fields returned will be null.

 

[burnside]

many thanks