CODEWITHSUNDEEP
mysql
fischer
fischer

Reputation: 269

MySQL retrieve count of pairs

I have a table with two fields: ID_From, ID_To.

Now the fields are populated like this:

ID_From    ID_To
  1000      2000           
  2000      1000            
  1000      5000            
  3000      2000            
  4000      3000
  5000      1000

What I would like is to query the database the ID of 1000 and then make it return, how many pairs there are. In this case it would return 2. Quering with ID 2000 would return 1. ID 3000 and 4000 would return zero.

Hope I have made it clear enough. Thanks in advance, Fischer.

Upvotes: 0

Views: 232

Answers (5)

Chris Morgan
Chris Morgan

Reputation: 2080

For 1000:

SELECT COUNT(1) FROM my_table a 
INNER JOIN my_table b ON a.ID_From = b.ID_To
AND b.ID_From = a.ID_To
WHERE a.ID_From = 1000;

Upvotes: 1

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115630

SELECT COUNT(*)
FROM myTable a
  JOIN myTable b
    ON a.ID_To = b.ID_From
    AND a.ID_From = b.ID_To
WHERE a.ID_From = 1000 ;

Upvotes: 2

piotrm
piotrm

Reputation: 12366

select count(*) from table_name t1 
join table_name t2 
on t1.ID_from = t2.ID_to
and t1.ID_to = t2.ID_from
where t1.ID_from = 1000

Upvotes: 2

chx
chx

Reputation: 11790

SELECT COUNT(*)
FROM t
WHERE :value > MIN(ID_From, ID_to) AND :value_1 < MAX(ID_From, ID_to)

both :value and :value_1 gets the same value, of course.

Edit: there is no MIN / MAX in MySQL for this apparently so you need to do IF(ID_from < ID_to, ID_From, ID_to) if MIN:

SELECT COUNT(*)
FROM t
WHERE :value > IF(ID_From < ID_to, ID_From, ID_to) AND :value_1 < IF(ID_From < ID_to, ID_to, ID_From)

I kept the original as it's more understandable even if it's just pseudocode.

Upvotes: -1

dtbarne
dtbarne

Reputation: 8210

SELECT COUNT(*) FROM tbl WHERE (ID_From >= 1000 AND 1000 <= ID_To) OR (ID_To >= 1000 AND 1000 <= ID_From);

Upvotes: 0

Related Questions