none
Update Table1 with data from Table2 where there are multiple records in Table 2 that need tallied in Table1 with criteria per row... RRS feed

  • Question

  • This is probably simple and I'm missing it - I'm tempted to use a cursor - but it seems too simple to have to go that route just yet - hopefully someone here can help.

    Here is my code and sample data:

    drop table if exists Customer
    create table Customer (
    ID int not null,
    [Name] nvarchar(20),
    Balance money not null
    )
    
    drop table if exists Transact
    create table Transact (
    ID int not null,
    CustomerID int not null,
    [Type] int not null,  -- 1 = payment, 2 = invoice
    Amount money not null
    )
    
    insert into Customer
    	Values (1, 'Bob', 100), (2, 'John', 500), (3, 'Dave', 300)
    
    insert into Transact
    	Values (1, 1, 2, 50), (2, 1, 1, 25), (3, 1, 2, 10),
    	(4, 2, 2, 100), (5, 2, 1, 50), (6, 2, 1, 200)
    
    select * from Customer
    select * from Transact

    This gives this result:

    ID Name Balance 1 Bob 100.00 2 John 500.00 3 Dave 300.00

    and

    ID	CustomerID	Type	Amount
    1	1		2	50.00
    2	1		1	25.00
    3	1		2	10.00
    4	2		2	100.00
    5	2		1	50.00
    6	2		1	200.00

    What I want to do is add the Amount values from the Transact table to the the Balance column of the Customer table. This is the code I tried:

    update Customer
    set Customer.Balance = Customer.Balance + t.Amount
    from Transact t
    where Customer.ID = t.CustomerID

    But this is giving this result - not what I want:

    Customer Table:

    ID	Name	Balance
    1	Bob	150.00
    2	John	600.00
    3 Dave 300.00

    I am trying to get this result:

    ID	Name	Balance
    1	Bob	135.00
    2	John	350.00
    3 Dave 300.00

    I assume in the Update that the Customer Balance is fixed and even if it iterated across all the matching Transact records - basically the last one wins.

    Thanks in advance!



    "When money ceases to be the tool by which men deal with one another, then men become the tools of men. Blood, whips and guns–or dollars. Take your choice–there is no other..."
        - Ayn Rand - Atlas Shrugged






    • Edited by AMeador Sunday, September 15, 2019 1:06 PM
    Sunday, September 15, 2019 12:51 AM

Answers

  • I got this solution from someone else on another help site. I want to post it on here too. I'm curious @Viorel_ if you have a modified solution with the additional criteria I added. @Arulmouzhi, your solution does work - including with my update criteria - but I think this is more concise. Thank you for your effort and solution - if I had not gotten this option - yours would be the only working solution I've gotten so far. Thank you

    UPDATE c
    SET c.Balance = c.Balance + t.Amount
    FROM Customer AS c
    CROSS APPLY (
        SELECT SUM(CASE WHEN Type = 2 THEN Amount ELSE Amount * -1 END) AS Amount FROM Transact
        WHERE CustomerID = c.ID
        GROUP BY CustomerID 
    ) t


    "When money ceases to be the tool by which men deal with one another, then men become the tools of men. Blood, whips and guns–or dollars. Take your choice–there is no other..."
        - Ayn Rand - Atlas Shrugged

    • Marked as answer by AMeador Sunday, September 15, 2019 2:06 PM
    Sunday, September 15, 2019 1:24 PM
  • In my full scenario, there are Customer records with a Balance in the Customer table that do not have a record in the Transact table. [...]


    Try this fix:

    update Customer

    set Balance +=

       (

          select ISNULL(SUM(Amount * (Type * 2 - 3)), 0)

          from Transact as t where t.CustomerID = Customer.ID

       )

     

    But if you are interested in performance in case of large amount of data, you should check various variants.

    By the way, the solutions can be simplified if you remove the Type column and store the payments with negative sign, invoices with positive sign.




    • Edited by Viorel_MVP Sunday, September 15, 2019 2:50 PM
    • Marked as answer by AMeador Sunday, September 15, 2019 3:45 PM
    Sunday, September 15, 2019 2:42 PM

All replies

  • Good day,

    Please check the following solution

    select c.ID, c.Name, c.Balance +
    	(SELECT 
    		SUM(t.Amount * (CASE WHEN t.Type = 2 THEN 1 ELSE -1 END) )
    	from Transact t
    	where t.CustomerID = c.ID
    	)
    from Customer c


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Sunday, September 15, 2019 2:01 AM
    Moderator
  • I can run a Select to get that values right - but I'm having a problem Updating Customer Balance in the same way. My select skills are better than my Update skills :( So, I still don't know how to get this logic implemented on the Update...

    Know how to do that?

    Thanks!


    "When money ceases to be the tool by which men deal with one another, then men become the tools of men. Blood, whips and guns–or dollars. Take your choice–there is no other..."
        - Ayn Rand - Atlas Shrugged

    Sunday, September 15, 2019 2:08 AM
  • Just to add addition information - this is to get an Update query. This cursor approach does what I want - but I'm trying to use set-based if I can and avoid the cursor approach:

    Declare @CustomerID int
    Declare @TransType int
    Declare @Amount money
    declare trans_cursor cursor for
    	select	t.CustomerID, t.[Type], T.Amount
    	from	Transact t
    open trans_cursor
    fetch next from trans_cursor into @CustomerID, @TransType, @Amount
    while @@FETCH_STATUS = 0 -- Are there any more rows to fetch? So long as the last fetch was successful, the while loop will run again
    	begin
    		Update C
    		SET C.Balance = C.Balance + (case when @TransType = 2 then @Amount when @TransType = 1 then (-(@Amount)) end)
    		from Customer C
    		where C.ID = @CustomerID
    		fetch next from trans_cursor into @CustomerID, @TransType, @Amount
    	end
    close trans_cursor
    deallocate trans_cursor

    and the result is as expected:

    ID	Name	Balance
    1	Bob	135.00
    2	John	350.00
    3	Dave	300.00


    "When money ceases to be the tool by which men deal with one another, then men become the tools of men. Blood, whips and guns–or dollars. Take your choice–there is no other..."
        - Ayn Rand - Atlas Shrugged


    • Edited by AMeador Sunday, September 15, 2019 1:08 PM
    Sunday, September 15, 2019 2:36 AM
  • Check this query too:

     

    ;

    with P as

    (

           select CustomerID, SUM(Amount) as s

           from Transact

           where Type = 1

           group by CustomerID

    ),

    I as

    (

           select CustomerID, SUM(Amount) as s

           from Transact

           where Type = 2

           group by CustomerID

    )

    update Customer

    set Balance += ISNULL(I.s, 0) - ISNULL(P.s, 0)

    from Customer as c

    left join P on P.CustomerID = ID

    left join I on I.CustomerID = ID

     

     

    or this:

     

    update Customer

    set Balance +=

       (

           select SUM(Amount * (Type * 2 - 3))

           from Transact as t where t.CustomerID = Customer.ID

       )

     




    • Edited by Viorel_MVP Sunday, September 15, 2019 8:37 AM
    Sunday, September 15, 2019 5:35 AM
  • I can run a Select to get that values right - but I'm having a problem Updating Customer Balance in the same way. My select skills are better than my Update skills :( So, I still don't know how to get this logic implemented on the Update...

    Know how to do that?

    Thanks!


    "When money ceases to be the tool by which men deal with one another, then men become the tools of men. Blood, whips and guns–or dollars. Take your choice–there is no other..."
        - Ayn Rand - Atlas Shrugged

    Hi,

    If your SELECT skills are good then your UPDATE skills are the same. You just don't know it yet😀

    You can use UPDATE FROM SELECT, or you can use UPDATE with CTE (while the CTE includes the SELECT query)

    which mean you can update anything you know to create the SELECT query

    The basic format is:

    UPDATE
        Table_Name
    SET
        Table_Name.col1 = Selected_Table.col1
    FROM
        Selected_Table
    WHERE
        Selected_Table.col3 = 'cool'
    GO
    
    
    UPDATE
        Table_Name
    SET
        Table_Name.col1 = Selected_Table.col1
    FROM
        (Select x,y,z from t1 INNER JOIN t2 on t1.id=t2.id where t1.id > 2314) as Selected_Table
    GO
    
    * If you need more help which fit your specific case, then please provide DML+DML

    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Sunday, September 15, 2019 7:29 AM
    Moderator
  • Hi AMeador,

    Hope the below code can help you!

    -unit tested and also attached select query for viewing the expected result befoire doing actual update.

    drop table if exists Customer
    create table Customer (
    ID int not null,
    [Name] nvarchar(20),
    Balance money not null
    )
    
    drop table if exists Transact
    create table Transact (
    ID int not null,
    CustomerID int not null,
    [Type] int not null,  -- 1 = payment, 2 = invoice
    Amount money not null
    )
    
    insert into Customer
    	Values (1, 'Bob', 100), (2, 'John', 500)
    
    insert into Transact
    	Values (1, 1, 2, 50), (2, 1, 1, 25), (3, 1, 2, 10),
    	(4, 2, 2, 100), (5, 2, 1, 50), (6, 2, 1, 200)
    
    select * from Customer
    select * from Transact
    
    --select query for checking before do update
    select c.id,c.[name],c.balance,(c.balance+t1.type1_amount+t1.type2_amount) as updatable_value 
    from customer c inner join
    (
    select c.id,c.[name],sum(case [type] when 1 then (-1*amount) else 0 end) as type1_amount,
    sum(case [type] when 2 then (1*amount) else 0 end) as type2_amount
    from Customer c join transact t on c.id=t.CustomerID
    group by c.id,c.[name]) t1 on t1.id=c.id;
    
    --update query
    update c set c.balance=(c.balance+t1.type1_amount+t1.type2_amount)
    from customer c inner join
    (
    select c.id,c.[name],sum(case [type] when 1 then (-1*amount) else 0 end) as type1_amount,
    sum(case [type] when 2 then (1*amount) else 0 end) as type2_amount
    from Customer c join transact t on c.id=t.CustomerID
    group by c.id,c.[name]) t1 on t1.id=c.id;
    
    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks in Advance,
    Arulmouzhi


    • Edited by Arulmouzhi Sunday, September 15, 2019 8:14 AM
    Sunday, September 15, 2019 8:12 AM
  • Your bottom Update is very nice and clean - and does work in the setup I gave. BUT ;)

    In my full scenario, there are Customer records with a Balance in the Customer table that do not have a record in the Transact table. I didn't think about this additional criteria when I posted. Your Update works perfectly with the test data I gave, but fails when it tries to update the Customer.Balance to null for Customer with no record in the Transact table. So simply change the Customer insert test data to:

    insert into Customer
    	Values (1, 'Bob', 100), (2, 'John', 500), (3, 'Dave', 300)

    And this error occurs: Cannot insert the value NULL into column 'Balance', table 'testDB.dbo.Customer'; column does not allow nulls. UPDATE fails.

    I do not know what to change in your code to stop this.


    "When money ceases to be the tool by which men deal with one another, then men become the tools of men. Blood, whips and guns–or dollars. Take your choice–there is no other..."
        - Ayn Rand - Atlas Shrugged

    Sunday, September 15, 2019 12:58 PM
  • I got this solution from someone else on another help site. I want to post it on here too. I'm curious @Viorel_ if you have a modified solution with the additional criteria I added. @Arulmouzhi, your solution does work - including with my update criteria - but I think this is more concise. Thank you for your effort and solution - if I had not gotten this option - yours would be the only working solution I've gotten so far. Thank you

    UPDATE c
    SET c.Balance = c.Balance + t.Amount
    FROM Customer AS c
    CROSS APPLY (
        SELECT SUM(CASE WHEN Type = 2 THEN Amount ELSE Amount * -1 END) AS Amount FROM Transact
        WHERE CustomerID = c.ID
        GROUP BY CustomerID 
    ) t


    "When money ceases to be the tool by which men deal with one another, then men become the tools of men. Blood, whips and guns–or dollars. Take your choice–there is no other..."
        - Ayn Rand - Atlas Shrugged

    • Marked as answer by AMeador Sunday, September 15, 2019 2:06 PM
    Sunday, September 15, 2019 1:24 PM
  • Hi,

    I am glad you solve your issue.

    In any case, you should close the thread (by marking one or more of the messages as answer), so other members will not spend their tikme in coming here and read the entire thread, only to find-out that the issue was solved :-)

    If you do not find a message which fit as answer in your opinion then you can  mark your summarize message as the answer (you do get points by answering your own thread).

    * I hope you saw my previous response, as it should give you general (short) explanation on the topic and might help you in future cases as well.

    Have a great day,


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Sunday, September 15, 2019 1:52 PM
    Moderator
  • I did see your response. I know about using Select for Updates - but could not seem to find a pattern that worked for this issue - it was not Updating correctly. Your initial response was a Select not an Update. Doing the simple Update - Select pattern was resulting in only 1 value in the Transact table being added to the Customer.Balance - not all of them. Notice the answer I posted that used a Cross Apply to get the correct result or @Arulmouzhi's answer that used multiple joins and grouping to come to the solution.

    I was thinking to leave the solution I posted as not answered to wait for responses from the two I mentioned in that post - in particular @Viorel_ was very close and even more concise. But, I see your point - I don't want to waste peoples time. I will mark my post as the answer and hope @Viorel_ will still update their answer further - if they want - I do like to see better solutions to learn by.

    Thanks


    "When money ceases to be the tool by which men deal with one another, then men become the tools of men. Blood, whips and guns–or dollars. Take your choice–there is no other..."
        - Ayn Rand - Atlas Shrugged




    • Edited by AMeador Sunday, September 15, 2019 2:17 PM
    Sunday, September 15, 2019 2:06 PM
  • Hi AMeador,

    Hope the current query can solve your issue. (changed as LEFT JOIN and handled NULL -solves the issue).

    drop table if exists Customer
    create table Customer (
    ID int not null,
    [Name] nvarchar(20),
    Balance money not null
    )
    
    drop table if exists Transact
    create table Transact (
    ID int not null,
    CustomerID int not null,
    [Type] int not null,  -- 1 = payment, 2 = invoice
    Amount money not null
    )
    
    insert into Customer
    	Values (1, 'Bob', 100), (2, 'John', 500),(3, 'Dave', 300)
    
    insert into Transact
    	Values (1, 1, 2, 50), (2, 1, 1, 25), (3, 1, 2, 10),
    	(4, 2, 2, 100), (5, 2, 1, 50), (6, 2, 1, 200)
    
    --select * from Customer
    --select * from Transact
    
    --select query for checking before do update
    select c.id,c.[name],c.balance,(c.balance+isnull(t1.type1_amount,0)+isnull(t1.type2_amount,0)) as updatable_value 
    from customer c left join
    (
    select c.id,c.[name],sum(case [type] when 1 then (-1*amount) else 0 end) as type1_amount,
    sum(case [type] when 2 then (1*amount) else 0 end) as type2_amount
    from Customer c join transact t on c.id=t.CustomerID
    group by c.id,c.[name]) t1 on t1.id=c.id;
    
    --update query
    update c set c.balance=(c.balance+isnull(t1.type1_amount,0)+isnull(t1.type2_amount,0))
    from customer c left join
    (
    select c.id,c.[name],sum(case [type] when 1 then (-1*amount) else 0 end) as type1_amount,
    sum(case [type] when 2 then (1*amount) else 0 end) as type2_amount
    from Customer c join transact t on c.id=t.CustomerID
    group by c.id,c.[name]) t1 on t1.id=c.id;

    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks,
    Arulmouzhi

    Sunday, September 15, 2019 2:32 PM
  • In my full scenario, there are Customer records with a Balance in the Customer table that do not have a record in the Transact table. [...]


    Try this fix:

    update Customer

    set Balance +=

       (

          select ISNULL(SUM(Amount * (Type * 2 - 3)), 0)

          from Transact as t where t.CustomerID = Customer.ID

       )

     

    But if you are interested in performance in case of large amount of data, you should check various variants.

    By the way, the solutions can be simplified if you remove the Type column and store the payments with negative sign, invoices with positive sign.




    • Edited by Viorel_MVP Sunday, September 15, 2019 2:50 PM
    • Marked as answer by AMeador Sunday, September 15, 2019 3:45 PM
    Sunday, September 15, 2019 2:42 PM
  • Your original solution seemed to work fine. It seemed from what I could tell that @Viorel_'s solution would try to insert NULL for Customer.Balance when it was trying to Update Customer.Balaces where there were no records related to the Customer in the Transact table. From my testing in this scenario, your original solution handled this fine and did not try to do this. Of course - the extra security of your revision here so Null valued Amounts in Transact would not cause an issue - but the Transact table does not allow nulls for the Amount either - so it shouldn't be a issue.

    Thanks again for looking at this. See my comment in the solution I marked as the answer. Your solution thus far has been the only one that has fully worked - including with the change is criteria from my original post. The only reason I listed the solution I posted as the answer is that it also works - but is more concise.

    Thank you!


    "When money ceases to be the tool by which men deal with one another, then men become the tools of men. Blood, whips and guns–or dollars. Take your choice–there is no other..."
        - Ayn Rand - Atlas Shrugged

    Sunday, September 15, 2019 2:44 PM
  • As none of the Amounts from the Transact table are null, my though was that this was originally trying to update the Customer.Balance for 'Dave' and as there is no Transact record for 'Dave, it was trying to update the Customer.Balance for 'Dave' with a null. I am misunderstanding why the first solution didn't work. I tested this revision and it is working. Where is the null coming from? I see how you are fixing it with isnull, but why is a null coming into the picture at all?

    Thanks!


    "When money ceases to be the tool by which men deal with one another, then men become the tools of men. Blood, whips and guns–or dollars. Take your choice–there is no other..."
        - Ayn Rand - Atlas Shrugged

    Sunday, September 15, 2019 3:04 PM
  • Hi AMeador,

    Great to know that the revised version solves your problem fully!

    For your doubt,

    The SQL LEFT JOIN returns all rows from the left table, even if there are no matches in the right table. This means that if the ON clause matches 0 (zero) records in the right table; the join will still return a row in the result, but with NULL in each column from the right table.

    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks,
    Arulmouzhi

    Sunday, September 15, 2019 3:10 PM
  • [...] why is a null coming into the picture at all?

    By design, ‘Select SUM(…)’ returns NULL if there is nothing to sum, for example: ‘select SUM(Amount) from Transact where CustomerID = -1’.


    Sunday, September 15, 2019 3:12 PM
  • But, it is summing the records in Transact - which all have non-null Amounts and the where clause is restricting it to when t.CustomerID = Customer.ID - so I am not understanding when the sum would ever be null.

    If it is summing Amount per Customer in Transact but has to do it for all Customers in Customer - then why doesn't it Update ALL Customer.Balances? It doesn't look like it is. I mean - if were adding 0 to their Balances, of course there would be 'no change' - but it it doesn't say (65000 rows effected) in the results/message window of SSMS - it only shows the number of Customers Balances that actually changed.

    See what I mean?


    "When money ceases to be the tool by which men deal with one another, then men become the tools of men. Blood, whips and guns–or dollars. Take your choice–there is no other..."
        - Ayn Rand - Atlas Shrugged

    Sunday, September 15, 2019 3:23 PM
  • Does the Update happen for all Customers - but since there is not actually a change in any of the Customer.Balances - SQL Server just doesn't update them? In other words, does this solution create an Update for every Customer - but the server only performs the ones that actually change the record - this the rows effected count is only for the Customer.Balances that changed?

    Does your method, or the one I posted from another forum, or @Arulmouzhi's solution actually work any differently? I think if I understand them they maybe are all creating Updates for all Customers.Balance - but the net result is that the ones that actually change the Balance get updated. I mean - the efficiencies look like the would be the same as they appear to create the same Update set. Does that make sense?


    "When money ceases to be the tool by which men deal with one another, then men become the tools of men. Blood, whips and guns–or dollars. Take your choice–there is no other..."
        - Ayn Rand - Atlas Shrugged

    Sunday, September 15, 2019 3:54 PM
  • Sound perfect :-)

    Thanks for closing the thread and will be nice if you can follow a bit more as you said to see if they will make the final fix to their solution.

    +5



    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Sunday, September 15, 2019 4:52 PM
    Moderator
  • You have a whole series of classic errors in your posting. First of all table models a set so it's name has to be a collective or plural noun. But since you're still writing code for filesystems, you name your tables for the individual unit of work (the record) that that model of data uses. Then you believe in a magical, universal, Kabbalah number called "ID", which does not exist in RDBMS. It's a leftover from sequential files and numbers the records in the order in which they were put into physical storage. RDBMS uses relational keys. In fact, a table by definition has to have a key and you don't have any of these are not tables. Finally the old Sybase money data type is a leftover from COBOL that violates the principle that storage and display should be in separate tears. And you might want to learn all of the rounding errors and problems that it has which make it illegal to use. The reason Sybase put it in their product was to keep COBOL programmers happy. Let's clean up your tables. 

    CREATE TABLE Customers -- more than one?
    (customer_id CHAR(16) NOT NULL PRIMARY KEY, -- not an option!
     cust_name NVARCHAR(20), -- what does null means?
     customer_balance_amt DECIMAL (8,2) NOT NULL); -- bad design but will get to that later

    INSERT INTO Customer
    VALUES ('01', 'Bob', 100,00), ('02', 'John', 500,00), ('03', 'Dave', 300,00);

    In your next table, the id switches its meaning completely. Sometimes it's a customer! sometimes it's a transaction! can it also be an automobile or squid? Do you remember your first logic course? You should have learned the law of identity, which says that to be is to be something in particular; to be nothing in particular or everything in general is to be nothing at all. Also your transactions are completely unrelated to your customers; where is the reference? Again we need to fix is garbage.

    CREATE TABLE Transactions -- transact is a verb, not a name
    (trans_nbr CHAR(3) NOT NULL PRIMARY KEY,
     customer_id CHAR(16) NOT NULL 
        REFERENCES Customers(customer_id )
    trans_type INTEGER NOT NULL  
       CHECK (trans_type IN (1,2), -- 1 = payment, 2 = invoice
    trans_amt DECIMAL(8,2) NOT NULL);

    Notice how the data element names been changed to conform with ISO 10179 naming standards. Notice the check constraint but most of all notice the references back to the customers. This is what the are in RDBMS stands for! You just use a lot of SQL to write an old 1950s tape file..

    INSERT INTO Transact
    VALUES ('001', '01', 2, 50,00), ('002, '01', 1, 25,00), ('003, '01', 2, 10,00),
    ('004', '02', 2, 100,00), ('005, '02', 1, 50,00), ('006, '02', 1, 200,00);

    >> What I want to do is add the trans_amt from the Transact table to the cust_balance_amt column of the Customer table. <<

    You're still thinking in terms of punchcards and sequential files, which have to materialize the data in a physical form. An SQL programmer would think in terms of a view, so that any new transactions or changes would be reflected in the summary.

    >> This is the code I tried:

    UPDATE Customers
    SET  Customers.balance_amt_amt = Customers.balance_amt + T.trans_amt
    FROM Transactions AS T
    WHERE Customers.cust_id = T.cust_id;

    Please google around and find out why nobody in their right mind uses the old Sybase UPDATE .. FROM syntax. It basically does not work. It has horrible cardinality errors. But you just discovered that for yourself! Actually this gets worse because with indexes and other things, the choice the final physical record from which it draws the logical row can be very random. If multiple users go for the same tables at the same time it's almost unpredictable.

    This is why we now have the MERGE statement in SQL.

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Sunday, September 15, 2019 6:10 PM
  • Thanks!

    "When money ceases to be the tool by which men deal with one another, then men become the tools of men. Blood, whips and guns–or dollars. Take your choice–there is no other..."
        - Ayn Rand - Atlas Shrugged

    Sunday, September 15, 2019 6:11 PM
  • CELKO! Long time since having the pleasure of your obnoxious diatribes! Please feel free to ignore my posts. :)

    Oh, you do know that many people have differing opinions than yourself - right? Also, much of what is put on here and in forums in general is for quick answers thus the code and samples given aren't of the actual system it is derived from. Yes - I understand indexes, relationships, etc that are in the realm of RDBMS. As I made this sample a very simplified form of my actual design for the purpose of being simple and timely - the need for such detail is unnecessary. For such a simplistic pseudo structure to need to employ all of the constraints, relations, indexes, triggers, etc... that sit behind it all is quite inane, nor do I think many people on here want or need it. If it gets oversimplified - more can be given.

    So, as before - if you want to provide help on the question at hand - please feel free without your digs and snide comments - if not please go somewhere else - maybe focus on writing another book that I won't buy! :)

    Have a nice day!


    "When money ceases to be the tool by which men deal with one another, then men become the tools of men. Blood, whips and guns–or dollars. Take your choice–there is no other..."
        - Ayn Rand - Atlas Shrugged



    • Edited by AMeador Sunday, September 15, 2019 6:46 PM
    Sunday, September 15, 2019 6:43 PM