Databases
StackOverflow2013
Postsdbo
POHow to have a left join involving a sub query?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to have a left join involving a sub query?
    primarykey
    data
    text
    <p>Please help with a problem I'm facing to properly join a few tables <strong>in a single query</strong>.</p> <p>Simplified schema is as follows:</p> <pre><code>TRANSACTION TABLE: ----------------- TRANSACTION_DATE AMOUNT CURRENCY ---------------- ---------- -------- 22/09/13 100 EUR 22/09/13 200 FRA 22/09/13 200 GBP CURRENCY_CONVERSION TABLE: ------------------------- FROM_CURRENCY TO_CURRENCY CONVERSION_DATE CONVERSION_RATE ------------- ----------- --------------- --------------- GBP USD 23/09/13 1.61 EUR USD 22/09/13 1.35 </code></pre> <p>Presently, the query that works is as follows (using Sybase/SQL Server join syntax)</p> <pre><code>SELECT t.transaction_date, t.amount, t.currency, t.amount * cc.conversion_rate as amount_usd FROM transaction t, currency_conversion cc, WHERE t.transaction_date *= cc.conversion_date AND t.currency *= cc.from_currency AND cc.to_currency = 'USD' </code></pre> <p>We face issue in above query when currency_conversion table may not have conversion rate for certain dates. In this case of missing value, business wants to resort to last latest conversion rate available in DB. For example, if EUR to USD rate is not available for today, use yesterday's rate. I try to do this as follows:</p> <pre><code>SELECT t.transaction_date, t.amount, t.currency, t.amount * cc.conversion_rate as amount_usd FROM transaction t, currency_conversion cc, WHERE t.transaction_date *= (SELECT max(c1.conversion_date) FROM currency_conversion c1 WHERE c1.from_currency = t.from_currency AND c1.to_currency = 'USD') AND t.currency *= cc.from_currency AND cc.to_currency = 'USD' </code></pre> <p>This query does not works. The left join on t.transaction_date is illegal. It would work if I let go of the join but then I miss some transactions in the final result.</p> <p>Please advise.</p> <p>Please note that:</p> <ul> <li>its important to achive this in a single query (two step solution with two queries won't help) <ul> <li>DB used is Sybase 12 though I think this is a generic SQL stuff</li> <li>this is a simplified schema, in the actual query I use there are two more such conversions to USD required for different amounts</li> <li>if possible, please suggest a solution around the existing query with minimal changes</li> </ul></li> </ul> <p>Thanks for your time.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USTwelfth
    1. UShaps10
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to have a left join involving a sub query?
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    2. VO
      singulars
      1. POHow to have a left join involving a sub query?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COchanged tagging - no need to put oracle and mssql2008 tags on a sybase/sql question. You will want to have the max logic on the table level in the from statement not in the where clause. Can you assume the exchange rate you want is always the most recent
      singulars
      1. POHow to have a left join involving a sub query?
      1. USTwelfth
    2. COSInce SYbase and SQl Server are basesd on the same engine, I would be very wary of using implict syntax for left joins. It gave incorrect results at times in SQl Server (BAck in SQL Server 200 when it was still allowed) sometime interpreting it as a cross join. Infact, It is a SQL antipattern to use implict joins at all and you really should stop using this proactice for all queries.
      singulars
      1. POHow to have a left join involving a sub query?
      1. USHLGEM
    3. COPArt of your problem is bad table design. If your currency conversion table had begin and end dates, it would be easier to query.
      singulars
      1. POHow to have a left join involving a sub query?
      1. USHLGEM
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload