Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Assuming you wanted the tables split as different payment types, use views and not separate tables or each time you insert a row into Orders, you need to also insert into the relevant Order[PaymentType] table through some mechanism (manually, trigger etc).</p> <p>As W3schools puts it:</p> <blockquote> <p>In SQL, a view is a virtual table based on the result-set of an SQL statement.</p> </blockquote> <p>You define a query that returns a result set, then you can treat that like you would a table. This means as you insert rows into Orders, you can view them in your view if they match the conditions, you do not need to worry about updating another table.</p> <p>Assuming you wish to split them on payment types, and you had a similar payment type table as the following:</p> <pre><code>PaymentTypeId PaymentTypeDesc ---------------------------------- 1 Cash 2 Cheque 3 Credit Card </code></pre> <p>then the following might be what you're looking for.</p> <pre><code>CREATE VIEW vw_OrdersCash AS SELECT * FROM Orders WHERE PAYMENT_TYPE = 1 CREATE VIEW vw_OrdersCheque AS SELECT * FROM Orders WHERE PAYMENT_TYPE = 2 CREATE VIEW vw_OrdersCreditCard AS SELECT * FROM Orders WHERE PAYMENT_TYPE = 3 </code></pre> <p>Just treat them as tables to do whatever you wish.</p> <pre><code>SELECT * FROM vw_OrdersCash </code></pre> <p>EDIT:</p> <p>Reading one of your comments to your question, it sounds like you want it more dynamic than the suggestion above. It's hard to tell without sample output what it is you're trying to achieve, but if you wanted 3 dynamic tables you could extend the above, and rather than the views filtering to a specific payment type, they would filter to the 1st, 2nd and 3rd most common at the time of execution.</p> <p>An idea of how to do this is below. The lookup view vw_OrdersTopThreePaymentMethods looks at the orders table and returns the top three payment types in descending order of number of orders (ie 1st, 2nd, 3rd). There are then views that will grab all of the orders on that specific type, with all the order information available for you to query as you want.</p> <pre><code>-- A lookup view that returns the top 3 payment methods of orders CREATE VIEW vw_OrdersTopThreePaymentMethods AS SELECT TOP 3 ROW_NUMBER() OVER(ORDER BY a.OrderCount DESC) AS Row, PAYMENT_TYPE, OrderCount FROM ( SELECT PAYMENT_TYPE , COUNT(*) as 'OrderCount' FROM Orders GROUP BY PAYMENT_TYPE ) a ORDER BY a.OrderCount desc -- 3 views that then get the orders for the top three methods based on output of vw_OrdersTopThreePaymentMethods CREATE VIEW vw_OrdersPrimaryPayment AS SELECT * FROM Orders WHERE PAYMENT_TYPE IN ( SELECT PAYMENT_TYPE FROM vw_OrdersTopThreePaymentMethods WHERE Row = 1 ) CREATE VIEW vw_OrdersSecondaryPayment AS SELECT * FROM Orders WHERE PAYMENT_TYPE IN ( SELECT PAYMENT_TYPE FROM vw_OrdersTopThreePaymentMethods WHERE Row = 2 ) CREATE VIEW vw_OrdersTertiaryPayment AS SELECT * FROM Orders WHERE PAYMENT_TYPE IN ( SELECT PAYMENT_TYPE FROM vw_OrdersTopThreePaymentMethods WHERE Row = 3 ) </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POCounting payment types and assigning labels to clients based on most common orders
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USRichard Raby
    1. USRichard Raby
    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. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

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