[RESOLVED]How to select within select with FOREACH loop

I need to make selection from a table then for each result make new query (from the same table)

Here is how i`m trying to do:

var contracts = db.Query(   "SELECT DISTINCT NrKontrates " +
                                "FROM PPI",
                                fillimi, perfundimi);

var payments = "SELECT * FROM PPI WHERE NrKontrates = '@0'";

@foreach(var contract in contracts){
            <table id="datatable1" class="table table-striped table-hover display">
                <thead>
                    <tr><td colspan="4">Numri i Kontrates: @kontrata.NrKontrates</td></tr>
                    <tr>
                        <th>Provizion</th>
                        <th>RDKA</th>
                        <th>ArsyeAn</th>  
                        <th>PrimiAn</th>
                    </tr>
                </thead>
                <tbody>
                    @foreach(var payment in db.Query(payments, kontrata.NrKontrates))
                    {
                        <tr>
                            <td>@payment.IDPol</td>
                            <td>@payment.Seria</td>
                            <td>@payment.Primi</td>
                            <td>@payment.NrKontrates</td>
                        </tr>
                    }
                </tbody>
            </table>
            }

I got no errors but i dont get what i except

pitoaxiu

var contracts = db.Query(   "SELECT DISTINCT NrKontrates " +
                                "FROM PPI",
                                fillimi, perfundimi);

You have passed in two parameter values (fillimi and perfundimi) but have no placeholders in your SQL for them, so they have no effect at all.

pitoaxiu

var payments = "SELECT * FROM PPI WHERE NrKontrates = '@0'";

You have surrounded your parameter placeholder with single quotes which turns it into a literal string. Parameter placeholders should not have quotes around them:

var payments = "SELECT * FROM PPI WHERE NrKontrates = @0";

I think all you need is this:

var sql = "SELECT * From PPI";
var data = db.Query(sql).GroupBy(d => d.NrKontrates);
@foreach(var group in data){
    <table id="datatable1" class="table table-striped table-hover display">
        <thead>
	    <tr><td colspan="4">Numri i Kontrates: @group.Key</td></tr>
	    <tr>
	        <th>Provizion</th>
                <th>RDKA</th>
                <th>ArsyeAn</th>  
                <th>PrimiAn</th>
            </tr>
        </thead>
        <tbody>
        @foreach(var payment in group)
        {
            <tr>
                <td>@payment.IDPol</td>
                <td>@payment.Seria</td>
                <td>@payment.Primi</td>
                <td>@payment.NrKontrates</td>
            </tr>
        }
        </tbody>
    </table>
}

You might need to alter the SQL depending on whether the parameters are supposed to be used as filters or something, but the approach above results in just one query and much better performance.

That is because of the WHERE clause. You wanted to pass a parameter, but you’re passing a string to it. 

You should be using this code instead of the code you’re having right now. 

var payments = "SELECT * FROM PPI WHERE NrKontrates = @0";

This would pass the kontrata.NrKontrates as a parameter. Secondly, why are you using concatenation when you can write that query command in one string. Like this, 

// And also, no parameter will be allowed in Query
// There is no WHERE column_name = @0 AND column_name2 = @1

db.Query("SELECT DISTINCT NrKontrates FROM PPI", fillimi, perfundimi);

Are you missing any thing in the first query? If not, simply skip this one and change your second query. 

Thank you a lot Mike i have also looking for this answer elsewhere and got too many suggestion but this is exactly what i wanted.

Thank you again

Leave a Reply