StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You can go with this type of structure</p> <pre><code>select * from applicationMaster | APPID | APPNAME | ------------------------ | 1 | Application1 | | 2 | Application2 | </code></pre> <p>ApplicationMaster will go with main Application related details which won't be repeated such Name, date etc.</p> <p><strong>Query 2</strong>:</p> <pre><code>select * from applicationField | FIELDID | APPID | FIELDNAME | --------------------------------- | 1 | 1 | NoOfUsers | | 2 | 1 | NoOfGroups | | 3 | 2 | NoHourusage | </code></pre> <p>ApplicationField can adjust any number of field for a particular appId. So AppId 1 has 2 fields <code>NoofUsers</code> and <code>NoOfGroups</code>. It is also capable to adjust newer fields for a particular app if you want.</p> <p><strong>Query 3</strong>:</p> <p>ApplicationValue will have the values for every license aplication so it will have compId which represents which company has applied using fieldId which refers to <code>applicationField</code> table we can get for which app values are stored.</p> <pre><code>select * from applicationValue | ID | COMPID | FIELDID | FIELDVALUE | -------------------------------------- | 1 | 1 | 1 | 50 | | 2 | 1 | 2 | 150 | | 3 | 2 | 3 | 350 | | 4 | 3 | 1 | 450 | | 5 | 3 | 2 | 50 | </code></pre> <p>applicationPriceMaster stores the price package for each application. There could be multiple package for a application.</p> <pre><code>select * from applicationPriceMaster | APPPACKAGE | APPID | TOTALPRICE | ----------------------------------- | 1 | 1 | 50 | | 2 | 1 | 100 | </code></pre> <p>For each application package its details will posted in this table.</p> <pre><code>select * from applicationPriceDetail | APPPACKAGE | FIELDID | QUANT | -------------------------------- | 1 | 1 | 1 | | 1 | 2 | 1 | | 2 | 1 | 10 | | 2 | 2 | 1 | </code></pre> <p><strong>NOTE</strong> Please check the structure as it is now too complex and check what type of queries you would be running on these table and its performance.</p> <pre><code>select apm.APPPACKAGE, TOTALPRICE from applicationPriceMaster apm inner join (select APPPACKAGE from applicationPriceDetail where FIELDID=1 and QUANT=1)a on apm.APPPACKAGE = a.APPPACKAGE inner join (select APPPACKAGE from applicationPriceDetail where FIELDID=2 and QUANT=1)b on a.APPPACKAGE=b.APPPACKAGE </code></pre> <h2><strong><a href="http://sqlfiddle.com/#!2/22818/4" rel="nofollow">SQL FIDDLE</a></strong>:</h2> <pre><code>| APPPACKAGE | TOTALPRICE | --------------------------- | 1 | 50 | </code></pre> <p>For single filter you have to use this query, so you have to increase number of inner query with the number of inner filter.</p> <pre><code>select apm.APPPACKAGE, TOTALPRICE from applicationPriceMaster apm inner join (select APPPACKAGE from applicationPriceDetail where FIELDID=1 and QUANT=1)a on apm.APPPACKAGE = a.APPPACKAGE </code></pre> <p><strong>NOTE</strong>-This query is quite complex and will only work if the values are same as mentioned in the packagedetail table and will work only if the values are 2 filter you have to remove 1 inner join if there is only 1 filter. So I suggest you to reconsider before using this approach.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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