enter image description here

This really is my database tables representation.

I'm doing inner join to obtain the data from all of these tables the totally the following.

Query-1

 select * from Trips
        Inner join TripPoints
               On Trips.tripkey=Trippoints.tripkey 
        inner join Cars
               On Trips.carid=Cars.cid 
                and Cars.cid IN ('1','2','3','4','5','6')
                where (lat>='4.0' and lat<='5.0')
                  and 
                (long>='52' and long <='54')

Query-2

    for(int carId=1;carId<=6;carId++)
        {
        select * from Trips
        Inner join TripPoints
           On         
          Trips.tripkey=TripPoints.tripkey 
          inner join Cars
          on 
          Trips.carid=Cars.cid and CARS.cid = carId
        where 
         (lat>='4.0' and lat<='5.0') 
           and 
         (long>='52' and long <='54')
        }

Both queries executes effectively.

But Query-1 gives lesser records compared to Query-2 ?

What difference within the query makes result difference?

Within the second query, this:

Trips.carid=Cars.cid and CARS.cid = carId

...is going to be treated because this:

Trips.carid = Cars.cid AND Cars.cid = Trips.carid 

...which obviously is equivalent to this:

Trips.carid = Cars.cid

Quite simply, the look of carId within the SQL has been treated because the column carid in table Trips and never your C# variable carId while you might be expecting. Therefore, the only real semantic distinction between the 2 queries would be that the first has this:

and Cars.cid IN ('1','2','3','4','5','6')

...that the second doesn't, hence the very first query returns less rows since there must exist Cars.cid values apart from the values '1' to '6' inclusive.

The very first query returns just one result using the info that you'll require (6 rows).

The 2nd query returns 6 results since you make 6 different chooses (1 row per each).


Each choose provides a result!