Databases
StackOverflow2013
Postsdbo
POSQL Server 2005 insert from select with conditionals and joins
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Server 2005 insert from select with conditionals and joins
    primarykey
    data
    text
    <p>Ok, so given the cluster fudge of a database below (SQL Server 2005), I need to do the following:</p> <ul> <li>Get the users.id for the temp.empid that are not duplicate (easy ids = 3, 4 with a simple join)</li> <li>Get the users.id for the temp.empid that are duplicate but exist in the oldLog (easy ids = 1, 7 easy enough with 2 joins)</li> <li>Get the users.id for the temp.empid that is duplicate and missing in the oldLog (wtf should be id = 8) <strong>The Mother Load => This user is not on the oldLog and is a duplicate, so I have to check both dbo.firstCriteria and dbo.secondCriteria.</strong> _<em>if amount is 200 I check dbo.firstCriteria for having completed count >= 3.</em>_ <strong>if amount is 100 I check dbo.secondCriteria for a completed.</strong></li> <li>insert into newLog</li> </ul> <p><strong>dbo.users</strong></p> <pre><code>id | empid ============= 1 | 1234 2 | 2345 3 | 3456 4 | 4567 (Missing log table) 5 | 5678 (Missing temp table) 6 | 1234 (Duplicate empid) 7 | 2345 (Duplicate empid) 8 | 6789 (The Mother Load Missing from oldLog and duplicate empid) 9 | 6789 10 | 1111 (The Mother Load Missing from oldLog and duplicate empid) 11 | 1111 </code></pre> <p><strong>dbo.temp</strong></p> <pre><code>empid | amount ======================== 1234 (id 1) | 200 2345 (id 7) | 200 3456 (id 3) | 100 4567 (id 4) | 100 6789 (id 8) | 200 1111 (id 11) | 100 </code></pre> <p><strong>dbo.oldLog</strong></p> <pre><code>id == 1 3 7 </code></pre> <p><strong>dbo.firstCriteria</strong></p> <pre><code>id | task | status =========================== 1 | task1 | completed 1 | task2 | completed 1 | task3 | completed 2 | task1 | completed 3 | task1 | completed 8 | task1 | completed 8 | task2 | completed 8 | task3 | completed </code></pre> <p><strong>dbo.secondCriteria</strong></p> <pre><code>id | status ============== 1 | completed 7 | completed 3 | completed 11 | completed </code></pre> <p><strong>dbo.newLog</strong></p> <p>BLANK</p> <p>My results should be as follows:</p> <pre><code>id | empid ============= 1 | 1234 7 | 2345 3 | 3456 4 | 4567 8 | 6789 11 | 1111 </code></pre> <p>And this is what I was attempting and got stuck:</p> <pre><code>SELECT users.id FROM TEMP JOIN users ON users.empid = TEMP.empid WHERE users.empid NOT IN (SELECT users.empid FROM users GROUP BY users.empid HAVING COUNT(users.empid) &gt; 1) UNION ALL SELECT users.id FROM TEMP JOIN users ON users.empid = TEMP.empid WHERE users.empid IN (SELECT users.empid FROM users GROUP BY users.empid HAVING COUNT(users.empid) &gt; 1) AND users.id IN (SELECT oldlog.id FROM oldlog) UNION ALL --???? </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USmarc_s
    1. USjesusOmar
    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
    1. VO
      singulars
      1. POSQL Server 2005 insert from select with conditionals and joins
      1. This table or related slice is empty.
      1. VTUpMod
    1. COCould you explain in a sentence or two exactly what the problematic query needs to include, please?
      singulars
      1. POSQL Server 2005 insert from select with conditionals and joins
      1. USWill A
 

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