CODEWITHSUNDEEP
asp.netsqllinq
Ozkan
Ozkan

Reputation: 2041

selecting another row that has the same column value

For example I have the following table

    id      name                        taxid     taxname
    180555 'All Ballooning Services'    11         Meetjesland
    180555 'All Ballooning Services'    12         Aalter
    184672 'All Inn'                    13         geen classement
    184672 'All Inn'                    14         Regio's
    184672 'All Inn'                    15         Gent

I am given only a taxid (for example say: 11)

Now I am doing a search on taxid, for example:

var q = from e in db where e.taxid == 11 select e;

But after this query I need to get the id (180555, which is same for taxid 11 and 12) and return the row with taxid 12

Anybody knows how I can do it in one single query?

Upvotes: 1

Views: 119

Answers (2)

user2745484
user2745484

Reputation: 1

Use this query. It will work.

var query = from e in db 
            join e1 in db on e.id equlas e1.id 
            where e.taxid == 11 
            select e1;

Upvotes: 0

KV Prajapati
KV Prajapati

Reputation: 94645

Try,

 var query = from e1 in db
               where e1.id ==
                (
                  from p1 in db
                  where p1.taxid == 1
                  select p1.id).FirstOrDefault()
              select e1;

Upvotes: 2

Related Questions