How to create complex VIEW and join this tables

Question

5.00/5 (1 vote)

See more:

hi,
first i'm sorry if i cant write a good title and welcome to any recommendations
i have "st_items" that have "stitems_Type" field , this field determine if the item is "stock" or "stock with custom serial".

st_items data rows examples :-

----------------------------------------------------
stitems_ID | stitems_Type | stitems_MinBalance | ---
1          | 0            | 10                 | ---
2          | 0            | 20                 | ---
3          | 1            | 5                  | ---
4          | 1            | 15                 | ---
----------------------------------------------------

if "stitems_Type = 0" , the item is "stock"
in this case all stocks information that related to this item will stored in table called "stock_noserials" with this rows example :

---------------------------------
id | stitems_ID | StockQnty | ---
1  | 1          | 5         | ---
2  | 1          | 3         | ---
3  | 2          | 500       | ---
4  | 2          | 150       | ---
---------------------------------

as "stock_noserials " table , the stock of item 1 = 8 (sum of StockQnty colmun) and the stock of item 2 = 650 (sum of StockQnty colmun)

if "stitems_Type = 1" , the item is "stock with serials"
in this case all stocks information that related to this item will stored in table called "purchases_item_seriels" with this rows example :

-------------------------------------
pis_ID| stitems_ID | pis_Statues| ---
1     | 3          | 1          | ---
2     | 3          | 2          | ---
3     | 3          | 4          | ---
4     | 3          | 5          | ---
5     | 4          | 2          | ---
-------------------------------------

pis_Statues (1 = in stock , 2 = sold , 4 = lost , 5 = returned sales) so , to get the stock will count the item that have this pis_Statues values (1,5,6)

as "purchases_item_seriels" table , the stock of item 3 = 2 (count of records where pis_Statues colmun value is (1,5,6)) and the stock of item 4 = 0

i want to create VIEWS that will provide me with the Shortages Items as this results :

--------------------------------------------------
stitems_ID | stitems_MinBalance | stock      | ---
1          | 10                 | 8          | ---
3          | 5                  | 2          | ---
4          | 15                 | 0          | ---
--------------------------------------------------

so i can also create Stored Procedure that will based on this VIEW ..

What I have tried:

I'm thinking of creating a View1 to join st_itemswith stock_noserials and creating a View2 to join st_itemswith purchases_item_seriels . but i don't know how i can create view to combine the two views

now i get shortages with Entity Framework code but it is very slow ..

st_itemsBindingSource.DataSource = nDB01.st_items
               .GroupBy(x => x.stitems_ID)
               .Select(grp => new
               {
                   itemID = grp.Key,
                   sumNoSerial = nDB01.stock_noserials.Where(u => u.stitems_ID == grp.Key).Sum(s => s.StockQnty),
                   sumWithSerial = nDB01.purchases_item_seriels.Where(u => (u.stitems_ID == grp.Key && u.pis_Statues == 0) ||
                                                                           (u.stitems_ID == grp.Key && u.pis_Statues == 5) ||
                                                                           (u.stitems_ID == grp.Key && u.pis_Statues == 6))
                                                                           .Count(),
                   minBalance = grp.Select(s => s.stitems_MinBalance).FirstOrDefault()
               })
                   .Where(u => u.minBalance >= (u.sumNoSerial+u.sumWithSerial)).ToList();

also i get its count with this code :

//Shortages النواقص
            int Shortages_StockNoSer = nDB01.stock_noserials
                .GroupBy(x => x.stitems_ID)
                .Select(grp => new
                {
                    itemID = grp.Key,
                    sum = grp.Sum(x => x.StockQnty),
                    min = grp.Select(s => s.st_items.stitems_MinBalance).FirstOrDefault()
                }).Where(u => u.sum < u.min && u.min != 0)
                .Count();
            int Shortages_StockWithSer = nDB01.purchases_item_seriels
               .GroupBy(x => x.stitems_ID)
               .Select(grp => new
               {
                   itemID = grp.Key,
                   sum = grp.Count(),
                   min = grp.Select(s => s.st_items.stitems_MinBalance).FirstOrDefault()
               }).Where(u => u.sum < u.min && u.min != 0)
               .Count();

            int CountShortagesItems = Convert.ToInt32( Shortages_StockNoSer) + Convert.ToInt32(Shortages_StockWithSer);

Posted 28-Dec-18 9:28am

Golden Basim

Updated 28-Dec-18 10:02am

v3

Add a Solution

2 solutions

Solution 1

You should create the View on the database server to take advantage of indexing and database server performance. I would be surprised if LINQ was designed to handle complex scenarious like this even though you can formulate them in theory (or practice).

Posted 28-Dec-18 9:35am

Dirk Bahle

Comments

Golden Basim 28-Dec-18 15:43pm

thank you , i checked this documentation and others .. but i can't create the required query

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2018-12-28T10:03:00

Solution 2

Not sure if I understood the question correctly, but you could try something like

SQL

WITH CurrentAmounts AS (
   SELECT sns.stitems_ID, 
          SUM(sns.StockQnty) AS Amount
   FROM stock_noserials sns
   GROUP BY sns.stitems_ID
) 
SELECT si.stitems_ID,
      si.stitems_MinBalance,
      COALESCE(ca.Amount,0) AS Stock
FROM st_items si,
     LEFT JOIN CurrentAmounts ca ON ca.stitems_ID = si.stitems_ID
WHERE COALESCE(ca.Amount,0) < si.stitems_MinBalance

And to create a view for the query above, just place it inside a CREATE VIEW statement[^]

ADDITION:

Without CTE

SQL

SELECT si.stitems_ID,
      si.stitems_MinBalance,
      COALESCE(ca.Amount,0) AS Stock
FROM st_items si,
     LEFT JOIN (SELECT sns.stitems_ID, 
                       SUM(sns.StockQnty) AS Amount
                FROM stock_noserials sns
                GROUP BY sns.stitems_ID) ca 
     ON ca.stitems_ID = si.stitems_ID
WHERE COALESCE(ca.Amount,0) < si.stitems_MinBalance

If needs to be added into amount, something like

SQL

SELECT si.stitems_ID,
      si.stitems_MinBalance,
      COALESCE(ca.Amount + pa.Amount,0) AS Stock
FROM st_items si,
     LEFT JOIN (SELECT sns.stitems_ID, 
                       SUM(sns.StockQnty) AS Amount
                FROM stock_noserials sns
                GROUP BY sns.stitems_ID) ca 
     ON ca.stitems_ID = si.stitems_ID
     LEFT JOIN (SELECT pis.stitems_ID, 
                       COUNT(*) AS Amount
                FROM purchases_item_seriels pis
                WHERE pis.pis_Statues IN (1, 5, 6)
                GROUP BY pis.stitems_ID) pa
     ON pa.stitems_ID = si.stitems_ID
WHERE COALESCE(ca.Amount + pa.Amount, 0) < si.stitems_MinBalance

Posted 28-Dec-18 10:03am

Wendelius

Updated 28-Dec-18 11:15am

v3

Comments

Golden Basim 28-Dec-18 16:43pm

thanks for reply , 1- it say unexpected 'with' (with)
2- i went to this table "purchases_item_seriels" to your query

Wendelius 28-Dec-18 17:14pm

For the item 1. Perhaps you're using an older version of MySQL which doesn't recognize CTE. I updated the question to use an inline view for the query.

What comes to the item 2, not sure if I understand the meaning of the table purchases_item_seriels. Should those rows be added to the amount in stock? If that is the case, have a look at the third variation in the answer.

Golden Basim 29-Dec-18 4:53am

yes the last query i think that it what i mean .. i will try it now
thanks

Golden Basim 29-Dec-18 4:56am

i have error with syntax again in (LEFT ,and SUM) , i use MySQL server 5.7.21

CREATE OR REPLACE VIEW `shortages` as
SELECT si.stitems_ID,
si.stitems_MinBalance,
COALESCE(ca.Amount + pa.Amount,0) AS Stock
FROM st_items si,
LEFT JOIN (SELECT sns.stitems_ID,
SUM(sns.StockQnty) AS Amount
FROM stock_noserials sns
GROUP BY sns.stitems_ID) ca
ON ca.stitems_ID = si.stitems_ID
LEFT JOIN (SELECT pis.stitems_ID,
COUNT(*) AS Amount
FROM purchases_item_seriels pis
WHERE pis.pis_Statues IN (1, 5, 6)
GROUP BY pis.stitems_ID) pa
ON pa.stitems_ID = si.stitems_ID
WHERE COALESCE(ca.Amount + pa.Amount, 0) < si.stitems_MinBalance;

Wendelius 29-Dec-18 5:08am

Sorry there's an extra comma after si. Try with

CREATE OR REPLACE VIEW `shortages` as
   SELECT si.stitems_ID,
          si.stitems_MinBalance,
          COALESCE(ca.Amount, 0) + COALESCE(pa.Amount,0) AS Stock
   FROM st_items si
   LEFT JOIN (SELECT sns.stitems_ID,
                     SUM(sns.StockQnty) AS Amount
              FROM stock_noserials sns
              GROUP BY sns.stitems_ID) ca
   ON ca.stitems_ID = si.stitems_ID
   LEFT JOIN (SELECT pis.stitems_ID,
                     COUNT(*) AS Amount
              FROM purchases_item_seriels pis
              WHERE pis.pis_Statues IN (1, 5, 6)
              GROUP BY pis.stitems_ID) pa
   ON pa.stitems_ID = si.stitems_ID
   WHERE (COALESCE(ca.Amount, 0) + COALESCE(pa.Amount,0)) < si.stitems_MinBalance;

But before making the view, you can try the SELECT statement on itself, just to test if it's giving correct results

Golden Basim 29-Dec-18 6:55am

i tested it with real values .. it is work very very very good .. thank you i will build many VIEWs based on you query ,,, so what you suggest it will be good if i select * from this view with EF or i should to use Stored Procedure also ?

Wendelius 29-Dec-18 9:35am

You're welcome :)

I don't think a stored procedure would give any extra benefit here so I personally would select the view directly.

Golden Basim 29-Dec-18 13:58pm

thanks :)