When to use 1=1 logic 1

[Jago11]

Hi,

It's been quite some time since I was in amongst code, and am wondering how to use 1=1 logic in order to relax a join condition.

Can you advise if this syntax is correct?

) ON table1.id_number = table2.id_number

AND (table3.currency = table2.currency OR 1=1)​

which is intending to take a currency match if there is one.

 

[Andrzejek]

You may want to read about the differences between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN here.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Jago11]

I haven't explained it very well. This is what we're trying to do

 

[Chris Miller]

OR 1=1 would relax the join condition too much, it would mean anything joins, you'd do a cross join and that's not what you want.

So indeed the usual condition about same currency is enough, you just have to think about the join type. If you do an INNER join, not finding a currency means you cancel the whole record, which is what you want to avoid, so choose an OUTER join, right or left depends which tables come first in the from clause, but the OUTER join condition already "relaxes" this join.

OR 1=1 in a join condition is never a good idea.

Chriss

 

[Andrzejek]

Can you have this data in:

[pre]
FX_backup
Deal num CCY USD_rate df
1 EUR 1.04 0.99
1 USD 1 1
2 GRP 1.2 0.9[blue]
2 USD 1 1[/blue]
[/pre]


If you can, then how can you get the last record in your outcome?
How do you choose between GRP and USD?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Jago11]

Essentially, if there is no ccy match, take the df value relative to the latest deal num.

 

[Chris Miller]

No idea what "the df value relative to the latest deal num" would be, you only show one table with df values and that would have no matching record if the join condition isn't satisfied, so that alternative df value has to come from somewhere else.

What I can tell you is that the missing record of an OUTER join is represented with NULL values and you have - besides others - the COALESCE function to pick something else in that case.

So your field list could contain COALESCE(outerjoinedtable.df, someotherdf) to make up for the non-joined data. and someotherdf could come from some other join, or from the main table, or be a constant like 1.0, no idea what works best for you.

But all in all, you give too little information to guide you fully. The data in your picture has currency = "EUR" for all deals in mrisk, so there isn't actually a case where a currency is missing in FX_bacckup. Even assuming the last deal would be about JPY, which is missing in FX_backup, there's no alternative table depicted to get another df.

Since your code talks of table2 and table3 and there's much guesswork to do to find out what tables you actually want to join how. The table numbering also hints at more tables involved than the two you want to combine to the third in your image.

You can, by the way, make it easier for everyone to see the image, as you can actually show an image in your posts. Click on the "Image" tool icon in the toolbar of the post editor and be guided to pick an image that is embedded into your post. Then also you can view it with the preview button to see how your post looks before submitting it.

Try once more, please, to describe your problem and the source data available.

Two things are very sure by now: You want an outer join and a COALESCE() or NULLIF() to make up for a missing df value. (Edit: IFNULL is MySQL, sorry.)

Chriss

 

[Andrzejek]

Looks to me that you need something like:

[pre]
Select
...
) ON table1.id_number = table2.id_number
AND table3.currency = table2.currency
...[red]
UNION[/red]
Select
...
(connect tables to get the missing data)
[/pre]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson