Databases
StackOverflow2013
Postsdbo
POSelect top one from left outer join
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSelect top one from left outer join
    primarykey
    data
    text
    <p>Guys, I have a query where basically select the latest browser that our user used.</p> <p>here is our (simplified) table structure</p> <pre><code>HITS_TABLE ---------- USERID BROWSER HITSDATE USER_TABLE ---------- USERID USERNAME </code></pre> <p>and here is how I query the latest browser that our user used</p> <pre><code>SELECT U.*, H.BROWSER FROM USER_TABLE U CROSS APPLY (SELECT TOP 1 BROWSER FROM HITS_TABLE WHERE HITS_TABLE.USERID = U.USERID ORDER BY HITS_TABLE.HITSDATE DESC )as H </code></pre> <p>The HITS_TABLE is just added several days ago. </p> <p>So, that query is just resulting users that visited our website after we added the HITS_TABLE, and eliminate the others.</p> <p>Here is the sample case</p> <pre><code>USER_TABLE ------------------- USERID USERNAME ------------------- 1 'Spolski' 2 'Atwoord 3 'Dixon' HITS_TABLE ------------------------------ USERID HITSDATE BROWSER ------------------------------ 2 15/8/2009 'Firefox 3.5' 1 16/8/2009 'IE 6' 2 16/8/2009 'Chrome' </code></pre> <p>Here is the sample result</p> <pre><code>------------------------------ USERID USERNAME BROWSER ------------------------------ 1 'Spolsky' 'IE 6' 2 'Atwoord' 'Chrome' </code></pre> <p>But, I want to add other users with 'unknown' browser. Here is my desired result</p> <pre><code>------------------------------ USERID USERNAME BROWSER ------------------------------ 1 'Spolsky' 'IE 6' 2 'Atwoord' 'Chrome' 3 'Dixon' 'Unknown' </code></pre> <p>I believe it could be achieved by LEFT OUTER JOIN. But I always had this: (I DO NOT want this result)</p> <pre><code>------------------------------ USERID USERNAME BROWSER ------------------------------ 1 'Spolsky' 'IE 6' 2 'Atwoord' 'Chrome' 2 'Atwoord' 'Firefox 3.5' 3 'Dixon' 'Unknown' </code></pre> <p>I hope my question is clear.</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. USAnwar Chandra
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    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. POSelect top one from left outer join
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSelect top one from left outer join
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POSelect top one from left outer join
      1. USScott Szretter
      1. VTFavorite
    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