Databases
StackOverflow2013
Postsdbo
POSimple SQL to check if parent has any child rows or not
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSimple SQL to check if parent has any child rows or not
    primarykey
    data
    text
    <p>I show a grid with parent data and need to show icon if it has a relevant child row(s) exist. <em>My DB is in SQL Server 2008</em>. Let me simplify, I've the following two tables - </p> <blockquote> <p>Order (PK : ID)</p> <p>File (PK: FileID, FK: OrderID)</p> </blockquote> <p>An <code>Order</code> can have zero or more files related to it. The <code>File</code> table has a column <code>OrderID</code> which holds FK reference to <code>Order</code>. Now, I'm displaying a grid which lists all the <code>Orders</code> and I want to display an icon image indicating if that <code>Order</code> has any child rows (files) or not.</p> <p>Here's a tricky way I've experimented but not sure how efficient/scalable it is -</p> <pre><code>SELECT DISTINCT o.ID, o.OrderNum, ... ,(CASE f.ID/f.ID WHEN 1 THEN 1 ELSE 0 END) as FilesExist ... FROM Order AS o LEFT OUTER JOIN dbo.FileHeader as f ON f.OrderID = o.ID </code></pre> <p>The <code>CASE</code> statement seems to work perfectly as required. It'll return 1 if one or more file(s) exists, else 0. If multiple file rows exist then it will try to repeat the <code>Order</code> row for which I've added the DISTINCT and I'm not selecting <code>f.ID</code> but <code>f.ID/f.ID</code> which will be 1 (it exists) and 0 for null (doesn't exist). I've learned that JOINs are better than inline <code>SELECT COUNT(*)</code> statements.</p> <p>I've tested and it works but I need expert opinion and need to make sure that this is the best way of doing it. This is a very simple example but my <code>SELECT</code> statement is complex as there are many lookups and its going to be a costly fetch so I need to make sure its scalable.</p> <p>Thank you.</p> <p><strong>EDIT #1:</strong> To conclude - either it is going to be <strong>internal SELECT</strong> with a COUNT(*) </p> <pre><code>SELECT c.ClaimNo,(SELECT COUNT(*) FROM dbo.FileHeader AS f WHERE f.ClaimID = c.ID ) AS FilesHExist FROM dbo.Claim AS c </code></pre> <p><img src="https://i.stack.imgur.com/Ck5kr.jpg" alt="Internal Select"></p> <p>or the <strong>LEFT OUTER JOIN approach</strong> that I've mentioned.</p> <pre><code>SELECT DISTINCT c.ClaimNo, (CASE fh.ID / fh.ID WHEN 1 THEN 1 ELSE 0 END) AS FilesHExist FROM dbo.Claim AS c LEFT OUTER JOIN dbo.FileHeader AS fh ON fh.ClaimID = c.ID </code></pre> <p><img src="https://i.stack.imgur.com/GtoFY.jpg" alt="My JOIN approach"></p> <p>Attached two images of query execution plan. Kindly suggest which one is better.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USErwin Brandstetter
    1. USHemant Tank
    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. POSimple SQL to check if parent has any child rows or not
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSimple SQL to check if parent has any child rows or not
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POSimple SQL to check if parent has any child rows or not
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhat type of SQL are you using ? Also it seems like a left outer join to `File` would yield `NULL` values, is this somehow handled by `dbo.FileHeader` with an `ISNULL()`? Further did you mean `f.ID/o.ID` ? As obviously `f.ID/f.ID` would *always* == 1?
      singulars
      1. POSimple SQL to check if parent has any child rows or not
      1. USGibron
    2. COMy DB is in SQL Server 2008. My idea is to have 0 when the JOIN yields a NULL value. So, f.ID/f.ID will always be 1 if it exists and 0 if its NULL. That is what the CASE statement implies - it serves the purpose without having to include any intermediate SELECT * or COUNT statement.
      singulars
      1. POSimple SQL to check if parent has any child rows or not
      1. USHemant Tank
    3. COPlease mark the answer yes if you got the answer
      singulars
      1. POSimple SQL to check if parent has any child rows or not
      1. USSubhash Lama
 

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