Databases
StackOverflow2013
Postsdbo
POHow do i query this complex query in T-SQL / Linq?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow do i query this complex query in T-SQL / Linq?
    primarykey
    data
    text
    <p>Currently I have 2 table</p> <pre><code>[Category] -&gt; PK| CAT_ID -&gt; CAT_PARENT (link to itself | if it's a top parent category then it's 0) [Posts] -&gt; PK | POST_ID -&gt; FK | CAT_ID | CREATE_DATE </code></pre> <p>How do I select top 15 ROWS of Posts in every CAT_PARENT which have multiple child category. So the total Posts in CAT_PARENT and all it's child are only 15. we have multiple CAT_PARENT here. so we can return multiple group of post which contain 15 post for every CAT_PARENT and it's child category</p> <p>The problem here is to have it in one round trip of query to the SQL server since one query can take up to 200 posts so it could be the best if you can write it in 1 T-sQL query / linq query.</p> <p>I prefer if you can write it in LINQ. But it's also OK to write it in T-SQL. </p> <p>Thank you very much :)</p> <hr> <p>Thanks to Alexander's solution down here, i modified some part and it work well with 186 query and some lazy loaded column for less than 2s (remotely) to my SQL server</p> <pre><code>ALTER procedure [dbo].[get_topParentPost] ( @quantity int ) as WITH t AS ( SELECT ROW_NUMBER() OVER (PARTITION BY top_level.CAT_ID ORDER BY p.CREATE_DATE DESC) AS row_num, top_level.CAT_ID AS top_level_cat_id, child_category.CAT_ID AS category_id, p.POST_ID, p.CREATE_DATE, p.VALIDATE, p.CAT_ID, p.DESCRIPTION, p.DRAF_OF, p.END_DATE, p.MOD_DATE, p.ON_HOMEPAGE, p.PUBLISH_DATE, p.[STATE], p.THUMB_ID, p.TITLE, p.[TYPE], p.[VIEW] FROM (SELECT cat_id, 0 as cat_parent FROM Categories c WHERE CAT_PARRENT = 0) AS top_level INNER JOIN Categories AS child_category ON child_category.CAT_PARRENT = top_level.CAT_ID OR child_category.CAT_ID = top_level.CAT_ID INNER JOIN Posts p ON child_category.CAT_ID = p.CAT_ID AND p.VALIDATE = 1 ) SELECT * FROM t WHERE row_num &lt;= @quantity </code></pre> <p>I modified some part which helps the query select top 15 according to descending date instead of ID ascending </p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USDucDigital
    1. USDucDigital
    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
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow do i query this complex query in T-SQL / Linq?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow do i query this complex query in T-SQL / Linq?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POHow do i query this complex query in T-SQL / Linq?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhat does the Posts table have to do with the question, given your query and model?
      singulars
      1. POHow do i query this complex query in T-SQL / Linq?
      1. USJarrett Widman
    2. COsorry, i forgot to include some thing
      singulars
      1. POHow do i query this complex query in T-SQL / Linq?
      1. USDucDigital
    3. COI've added the details for this question.
      singulars
      1. POHow do i query this complex query in T-SQL / Linq?
      1. USDucDigital
 

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