LEFT OUTER JOIN?.... 1

[burnside]

hi,

am using mysql and want a recordset to be displayed from a query that links three tables.
because a user can add to their selection at a later date i would like all records from a tbl_fitopt to be displayed even when there are no foreign keys in the other tables.

sql is below but at the moment it only lists records from tbl_fitopt that have keys in tbl_fitoptlink - nb i need all records from tbl_fitopt to display so a user can add/subtract from there choices.

strsql = "SELECT tbl_fitopt.fitoptid, tbl_fitopt.fitoptcost, tbl_fitopt.fitopt, tbl_stock.stockid, tbl_fitoptlink.fitoptlinkid "_

& "FROM (tbl_fitopt INNER JOIN tbl_fitoptlink ON tbl_fitopt.fitoptid = tbl_fitoptlink.fitoptid)
INNER JOIN tbl_stock ON tbl_fitoptlink.stockid = tbl_stock.stockid "_

& "WHERE tbl_stock.stockid = " & tkey & ";

 

[TonyGroves]

You could just replace the INNER JOINs with LEFT JOINs, which will include all records from tbl_fitopt, but then you're filtering the result set by tbl_stock.stockid, which I don't quite understand. Maybe the WHERE condition should include "OR tbl_stockid IS NULL"?

 

[burnside]

sorry - my tables look like

tbl_fitopt
----------------------------
fitoptid fitopt fitoptcost
1 alloys 200
2 mudguards 30
3 leather 100...

tbl_fitoptlink
---------------------------------
fitoptlinkid fitoptid stockid
1 1 2
2 2 2
3 1 3
4 3 3...

tbl_stock
-----------------
stockid stock
1 car1
2 car2
3 car3...

 

[burnside]

tony, thanks for your reply

have updated my sql but still the same

strsql = "SELECT tbl_fitopt.fitoptid, tbl_fitopt.fitoptcost, tbl_fitopt.fitopt,  tbl_fitoptlink.dealid, tbl_fitoptlink.fitoptlinkid 

FROM (tbl_fitopt LEFT JOIN tbl_fitoptlink ON tbl_fitopt.fitoptid = tbl_fitoptlink.fitoptid) 

WHERE tbl_fitoptlink.dealid = " & tkey & ";"

i want an asp page to list options for a car including ones that may have been chosen before.
one car and many options so a link table relates the two together
the WHERE clause gives value for stockid

Page example(using tables above)

Options for Car2

alloys 200 [/] 'tick is shown
mudguards 30 [/] 'tick is shown
leather 100 [ ]... 'no tick but can be added

 

[TonyGroves]

You could try something like:
[tt]
SELECT
tbl_fitopt.fitoptid, tbl_fitopt.fitoptcost,
tbl_fitopt.fitopt, s.stockid, s.fitoptlinkid
FROM
tbl_fitopt
LEFT JOIN
(
SELECT *
FROM
tbl_fitoptlink
JOIN tbl_stock
ON tbl_fitoptlink.stockid = tbl_stock.stockid
WHERE tbl_stock.stockid="tkey"
) s
ON tbl_fitopt.fitoptid = s.fitoptid
[/tt]
That should give all the options chosen for a particular car, as well as all the others available.

 

[burnside]

thanks for your reply tony-

realised didnt need tbl_stock so sql looks like
but is still only displaying options that have
links in tbl_fitoptlink

strsql = "SELECT tbl_fitopt.fitoptid, tbl_fitopt.fitoptcost, tbl_fitopt.fitopt,  tbl_fitoptlink.dealid, tbl_fitoptlink.fitoptlinkid 

FROM (tbl_fitopt LEFT JOIN tbl_fitoptlink ON tbl_fitopt.fitoptid = tbl_fitoptlink.fitoptid) 

WHERE tbl_fitoptlink.dealid = " & tkey & ";"

 

[TonyGroves]

If you don't need tbl_stock, just remove it from the join, without changing the logic of the rest of the query:
[tt]
SELECT
tbl_fitopt.fitoptid, tbl_fitopt.fitoptcost,
tbl_fitopt.fitopt, s.stockid, s.fitoptlinkid
FROM
tbl_fitopt
LEFT JOIN
(
SELECT *
FROM tbl_fitoptlink
WHERE stockid="tkey"
) s
ON tbl_fitopt.fitoptid = s.fitoptid
[/tt]

 

[burnside]

tony - sorry have been away
appreciate your help

am getting an sql error from this statement

strsql = "SELECT tbl_fitopt.fitoptid, tbl_fitopt.fitoptcost, tbl_fitopt.fitopt, tbl_fitoptlink.stockid, s.fitoptlinkid "_
& "FROM tbl_fitopt LEFT JOIN(SELECT * FROM tbl_fitoptlink WHERE tbl_fitoptlink.stockid=" & tkey & ") "_
& "ON tbl_fitopt.fitoptid = tbl_fitoptlink.fitoptid"

have removed 's' as it didnt like it - is this necessary?
should there have been an 'AS s' in your statement?

the error is

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[MySQL][ODBC 3.51 Driver][mysqld-4.0.21-debug]You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * FROM tbl_fitoptlink WHERE tbl_fitoptlink.stockid=11) ON
/dfasp2/stockaccdeal.asp, line 303

r.write strsql shows-

SELECT tbl_fitopt.fitoptid, tbl_fitopt.fitoptcost, tbl_fitopt.fitopt, tbl_fitoptlink.dealid, tbl_fitoptlink.fitoptlinkid FROM tbl_fitopt LEFT JOIN (SELECT * FROM tbl_fitoptlink WHERE tbl_fitoptlink.stockid=11) ON tbl_fitopt.fitoptid = tbl_fitoptlink.fitoptid

many thanks MG

 

[TonyGroves]

The 's' (or any other name) is necessary; all subqueries have to be aliased. But that doesn't explain the error. What does explain it is the "[mysqld-4.0.21-debug]" bit. Subqueries can only be used with MySQL 4.1 onwards.

What you could do instead is to split the query into two. First, create a temporary table using the subquery, then replace the subquery with a join on the temporary table.

 

[burnside]

upgraded to 4.1
now get this error with sql

Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/dfasp2/stockaccdeal.asp, line 300, column 173
strsql = "SELECT tbl_fitopt.fitoptid, tbl_fitopt.fitoptcost,tbl_fitopt.fitopt, s.dealid, s.fitoptlinkid FROM tbl_fitopt LEFT JOIN(SELECT * FROM tbl_fitoptlink WHERE dealid="tkey") s ON tbl_fitopt.fitoptid = s.fitoptid"
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------^

 

[burnside]

sorry forget to remove double quotes from var!
cheers tony - works great
if ya get time do you know nething about thread436-1101293