?
Solved

SQL View updating

Posted on 2012-08-17
8
Medium Priority
?
439 Views
Last Modified: 2012-09-02
Hi experts!

So I have a problem with a SQL view I am trying to build that will need to be editable (linked to a Delphi grid which will have grid entry). I have two tables I am using, one that holds individual job records, and another that holds multiple different tasks that will be linked back to the job. Here's a basic idea of what the tables look like:

JobTbl

JobID
Name
Address
Comments

TaskTbl

TaskID
JobID
TotalQty
CompletedQty

Basically what I have done is create several derived tables for each different task, and linked them back to the job table in order to create one job record with a different set of columns per each task. Ex:

SELECT JobID, Milling_TotalQty, Framing_TotalQty
FROM JobTbl LEFT JOIN 
(SELECT TotalQty AS Milling_TotalQty, CompletedQty AS Milling_CompletedQty
 FROM TaskTbl 
WHERE TaskID = 1) AS Milling ON JobTbl.JobID = TaskTbl.JobID LEFT JOIN
(SELECT TotalQty AS Framing_TotalQty, CompletedQty AS Framing_CompletedQty 
FROM TaskTbl 
WHERE TaskID = 2) AS Framing ON JobTbl.JobID = TaskTbl.JobID 

Open in new window


 The problem I'm having though, is that I need to use a left join to ensure all job records are accounted for (as we don't post any records for zero task quantities, and thus does not show a record during an inner join), but doing so makes my view no longer editable. Is there a way to create an editable view using left or right joins? and if not, is there a way to use inner joins but still retain the orginal record count of the job table?
0
Comment
Question by:Bianca
  • 3
5 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 1000 total points
ID: 38306799
>Is there a way to create an editable view using left or right joins?
Nope.  UPDATEs are allowed only for one table, so when you have a JOIN expression it makes it read-only.

>if not, is there a way to use inner joins but still retain the orginal record count of the job table?
Since you're doing a LEFT JOIN, and I don't see any WHERE limitations on it, Count(JobTbl.AnyColumn) should return the record count of the job table.

Since now you have an aggregate, you'l have to Group the other columns:

SELECT JobID, Milling_TotalQty, Framing_TotalQty, Count(JobTbl.AnyColumn) as the_count
FROM JobTbl
LEFT JOIN
   (SELECT TotalQty AS Milling_TotalQty, CompletedQty AS Milling_CompletedQty
    FROM TaskTbl
   WHERE TaskID = 1) AS Milling ON JobTbl.JobID = TaskTbl.JobID
LEFT JOIN
   (SELECT TotalQty AS Framing_TotalQty, CompletedQty AS Framing_CompletedQty
   FROM TaskTbl
    WHERE TaskID = 2) AS Framing ON JobTbl.JobID = TaskTbl.JobID
GROUP BY JobID, Milling_TotalQty, Framing_TotalQty
0
 
LVL 12

Assisted Solution

by:sachitjain
sachitjain earned 1000 total points
ID: 38310931
I think your query needs to be like this

select JobId, Milling_TotalQty, Framing_TotalQty
from JobTbl
      left join (select JobId, SUM(TotalQty) Milling_TotalQty, SUM(CompletedQty) Milling_CompletedQty
                        from TaskTbl where TaskID = 1 group by JobId) Milling on JobTbl.JobId = Milling.JobId
      left join (select JobId, SUM(TotalQty) Framing_TotalQty, SUM(CompletedQty) Framing_CompletedQty
                        from TaskTbl where TaskID = 2 group by JobId) Framing on JobTbl.JobId = Framing.JobId



Secondly, what is the need to have an updatable view? If Milling_TotalQty and Framing_TotalQty are the fields that are editable in your grid then how would disperse these updated values to individual row values for TotalQty column in the table because Milling_TotalQty and Framing_TotalQty has to come as consolidated values for taskIds 1 and 2 respectively. I guess you need to write a smart stored procedure to do the needful instead of updatable view.
0
 

Author Comment

by:Bianca
ID: 38334815
I've requested that this question be deleted for the following reason:

Question is no longer pertinent.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38334816
Being not pertinent does not disqualify it from being a valid question.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38347171
250-250 is fine.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Integration Management Part 2
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question