0

I have 2 below tables:

tblCustomers:

id    customerName 
1     John 
2     Philip 
3     Steve

tblOredrs:

id     customerId     ordId     payment
1     1                     100       True
2     1                     101       True
3     1                     102       False 
4     2                     101       True
5     2                     102       True
6     2                     103       False 
7     3                     101       True

my condition is:

where (orderId = 101 and orderId = 102)

I want to see all payment true order, for the customers that have orders (101 & 102). but orderids may be more than 2, it must be dynamic.

i must get:

john      100
john      101 
Philip    101
Philip    102

Edited by Mohammad_10

2
Contributors
6
Replies
19
Views
4 Years
Discussion Span
Last Post by Mohammad_10
0

What exactly is your problem? You need the SQL syntax for select? Or the linq syntax?
And based on your example where (orderId = 101 and orderId = 102) you would never get the result you mentioned. (which include ordId 100).

0

thank you, i need linq. it must be group each person-buy and check if he/she buy is cover (101 nad 102). i mean i know who buy 101 and 102, second part can be omited, maybe i can do second part in programming.
i know in sql it is possible,i can send solution if you need, but i need in linq.

0

I think I understood what you want. Select the customers that have at least 1 order 101 and at least 1 order 102 and payment true. Notice you said "and" not "or".

Here is what I came up with so far.

Get customer ids that have both at least 1 order of 101 and 102 and payment true:

var customerIds = tblOrders.Where(o => o.payment == true && (o.ordId == 101 || o.ordId == 102))
                .GroupBy(o => o.customerId)
                .Select(grp => new { Count = grp.Count(), CustomerId = grp.Key })
                .Where(g => g.Count > 1).Select(c => c.CustomerId);

Select based on joined list of customer ids

var results = from o in tblOrders
                join c in tblCustomers on o.customerId equals c.id
                where customerIds.Contains(o.customerId) && o.payment == true
                select new { o.id, c.name, o.ordId };

If you meant to say 101 OR 102 it's easy... select the customer ids from orders where your conditions meet and do a join with this list of ids.

0

is it possible to write in vb, any way:
1 - order Id must be in a list, it can be 3, 4, or more.
2 - i need who (customers) buy 101 and 102(in first step) then return rows of this customer that payed(second step).
but in you solution use or (o.ordId == 101 || o.ordId == 102).

0

The solution was something to get you started with. The problem with this one is that if you have multiple orders of same type and 0 orders of the other it will still show up.

be in a list, it can be 3, 4, or more.

d who (customers) buy 101 and 102(in first step) then retu

2 - i need who (customers) buy 101 and 102(in first step) then return rows of this customer that payed(second step).
but in you solution use or (o.ordId == 101 || o.ordId == 102).

Of course it is ||. You can't have an order with ordId = 101 && ordId = 102. That's like saying give me the row which has both values in the ordId. My condition selects all orders with those id's.

To bypass the issue I mentioned you could select orders with 101, than orders with 102 and intersect those lists to see where you have the same customer.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.