Databases
StackOverflow2013
Postsdbo
POSQL Server JOIN ON value ELSE NULL
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Server JOIN ON value ELSE NULL
    primarykey
    data
    text
    <p>I’m struggling with a query where I need to join data. I have a table with all kinds of status messages. These have a failureBitNr like for example “a1_1”. Then I have a second table that translates “a1_1” to a human readable text (lng_English). By default, the second table has a translation for “a1_1”. But this can also be overwritten by another specific translation, for example when the failureBitNr is used for another status than standard.</p> <p>So I need to join the status table with the translation table on failureBitNr. That’s not that hard.</p> <p>But how do I join it on the translation that overwrites the basic translation?</p> <p>Table 2 looks like this:</p> <pre><code>id fileProjectNr MachineNr failureBitNr lng_English 905 2203456 2 a6_1 This message overwrites the standard 205 NULL NULL a6_1 Standard message for a6_1 204 NULL NULL a1_1 Standard message for A1_1 203 NULL NULL a1_2 Standard message for A1_2 </code></pre> <p>Remark that the message that overwrites the existing one holds a fileProjectNr that is not NULL. All standard messages have a fileProjectNr NULL</p> <p>So joining only on FailureBitNr will return both rows (905 &amp; 205). But I need to join on failureBitNr and do something with not being NULL of fileProjectNr.</p> <p>So I did this:</p> <pre><code>DECLARE @ProjectNr int = 123456 SELECT t1.*, t2.* FROM Table1 AS t1 LEFT JOIN Table2 AS t2 ON (t1.failureBitNr = t2.failureBitNr) AND (t2.fileProjectNr LIKE CASE WHEN t2.fileProjectNr = @ProjectNr THEN @ProjectNr ELSE NULL END ) WHERE {where statement} </code></pre> <p>This will return ID 905, however if there is also a join on failureBitNr a1_1 and a1_2, these will all be returned as “NULL” in stead of “Standard message for ax_x”.</p> <p>Anyone an idea of how to fix this?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USTimo002
    plurals
    1. This table or related slice is empty.
    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
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

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