Join on table1.var1 = -table2.var2 gives 'Join expression not supported' error

[PeDa]

Any suggestions how to work arround this error message in the following expression where the ON condition contains a minus sign (row source for a dropdown list)

SELECT pID, pName & ", " & pTitle & " " & pPreName & " (" & pBornYear & "-" & pDiedYear & ")", tbTimesRefs.rShip_n 
FROM tbTimesRefs LEFT OUTER JOIN tbPeople 
ON (tbTimesRefs.rShip_n = -tbPeople.pID) 
WHERE ....

 

[Andrzejek]

SELECT pID, pName & ", " & pTitle & " " & pPreName & " (" & pBornYear & "-" & pDiedYear & ")" [blue]As Something[/blue], tbTimesRefs.rShip_n 
FROM tbTimesRefs LEFT OUTER JOIN tbPeople 
ON (tbTimesRefs.rShip_n = [highlight #FCE94F]-[/highlight]tbPeople.pID) 
WHERE ....

What is this - (minus) in [tt][highlight #FCE94F]-[/highlight]tbPeople.pID[/tt] ?


---- Andy

There is a great need for a sarcasm font.

 

[PeDa]

Yes that is indeed minus! (there is a good reason for this); the rShip_n values are negative if the value represents a person and not a ship.

Example: the entry for 26 February 1866 ('The Gun-Carriage Competition') at the bottom of

https://www.pdavis.nl/ShowShip.php?id=1776

(a ship entry) is also included in

https://www.pdavis.nl/ShowBiog.php?id=1716

(a person entry). I use Access to fill the (MySQL) database; there is a one-to-many relationship between (on the one hand) the text fragments and (on the other hand) the ships and people.

 

[kray4660]

You may have to change it to (tbTimesRefs.rShip_n = -1 * tbPeople.pID) , of course it assumes that the fields in question are numeric.

 

[Andrzejek]

Based on this place, your syntax in MySQL should be:

...
FROM tbTimesRefs LEFT [highlight #FCE94F][[/highlight]OUTER[highlight #FCE94F]][/highlight] JOIN tbPeople 
ON ...

---- Andy

There is a great need for a sarcasm font.

 

[PeDa]

Unfortunately neither of these suggestions helped. The values are indeed numeric. The Join works fine if I make both sides of ON clause positive.

 

[SkipVought]

Still fishin'...

How 'bout...

 (tbTimesRefs.rShip_n = [highlight #FCE94F]([/highlight]-1 * tbPeople.pID[highlight #FCE94F])[/highlight])

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[strongm]

Can't replicate this at all ... works fine in a bare bones implementation. Hmmm ...

 

[PeDa]

I populate three dropdown lists (for 'people', 'ships' and 'specials') with the following code:

pubSqlString = "SELECT vShipID, vName &  "" ("" &  left(vLaunched,4) & ""-"" & vFate & "")"", tbTimesRefs.rShip_n, vName " & _
"FROM tbTimesRefs LEFT OUTER JOIN tbVictorianShips ON (tbTimesRefs.rShip_n = tbVictorianShips.vShipID) " & _
"WHERE (tbTimesRefs.rEvent_n = " & mEvent_n & ") And (tbTimesRefs.rID Is Not Null) AND (tbTimesRefs.rShip_n >0) " & _
"ORDER BY vName"
keuShip.RowSource = pubSqlString

pubSqlString = "SELECT pID, pName & "", "" & pTitle & "" "" & pPreName & "" ("" & pBornYear & ""-"" & pDiedYear & "")"", tbTimesRefs.rShip_n, pName " & _
"FROM tbTimesRefs LEFT OUTER JOIN tbPeople ON (tbTimesrefs.rShip_n= -tbPeople.pID) " & _
"WHERE (tbTimesRefs.rEvent_n = " & mEvent_n & ") And (tbTimesRefs.rID Is Not Null) And (tbTimesRefs.rShip_n<0) ORDER BY pName"
keuPerson.RowSource = pubSqlString

pubSqlString = "SELECT vID, vName, tbTimesRefs.rShip_n " & _
"FROM tbTimesRefs LEFT OUTER JOIN tbSpecial ON (tbTimesRefs.rShip_n = tbSpecial.vID) " & _
"WHERE (tbTimesRefs.rEvent_n = " & mEvent_n & ") And (tbTimesRefs.rID Is Not Null) And (vID Is Not Null) ORDER BY vName"
keuSpecial.RowSource = pubSqlString

This seems to work OK, but when I try to open de 'people' dropdown the error message appears (the other two work as expected).

The extra pair of () didn't help.

I think that the pragmatic solution is going to be: copy rShip_n from the relevant records of tbTimesRefs (it's only a couple at most) on the fly to a scratch table, multiply it by -1 to remove the negative sign, and join tbPeople to that.

(later: have just carried out this last plan, and it works fine; problem solved)

 

[combo]

Check wirh Debug.Print SQLs you pass, for the first string with non-initialised variables I get:

[tt]SELECT vShipID, vName & " (" & left(vLaunched,4) & "-" & vFate & ")", tbTimesRefs.rShip_n, vName FROM tbTimesRefs LEFT OUTER JOIN tbVictorianShips ON (tbTimesRefs.rShip_n = tbVictorianShips.vShipID) WHERE (tbTimesRefs.rEvent_n = ) And (tbTimesRefs.rID Is Not Null) AND (tbTimesRefs.rShip_n >0) ORDER BY vName[/tt]

which, esp. in the first part syntax, is probably not what you expect.

combo