0

I have a table "trail' that maintains the records of how point a is connected to point b. and if there are any intermediate points the table records them as well. There is a column in trails 'type' that indicates whether this connection is main or backup (type can have only 2 values 'Main' or 'Backup', having backup path is optional) and other column Conn_ID ('Main' and 'Backup' connections will have same Conn_ID) and may or may not pass through some common points.

I want to list all the connections (Conn_ID basically) passing through a point say X (an intermediate point) that will be affected if X is taken out. Is this possible, If so how? let me know if you need more info. Table has almost 140 columns of which only the 2 above are to be used.

I tried using minus and it didn't work. The Logic I applied is below:

select Conn_id from trail where point = 'X'
MINUS
select Conn_id from trail where point in (select distinct(Conn_id) from trail where point = 'X') AND Type = 'Backup'

effectively giving me all the paths (both Main and Backup) passing through X or not having any backup path. Thanks

Rahul Katarey
  • 7
  • 5
  • `MINUS` is Oracle specific. The SQL standard for that is `EXCEPT` neither of that is supported in MySQL –  Nov 18 '14 at 09:11
  • http://stackoverflow.com/questions/13019865/except-all-equivalent-in-mysql or –  Nov 18 '14 at 09:12
  • Possible duplicate of http://stackoverflow.com/questions/8386280/minus-operator-in-mysql – JoDev Feb 02 '17 at 08:04

1 Answers1

0

MySQL doesn't support MINUS. With one column only you can replace it easily with NOT IN however:

select Conn_id 
from trail 
where point = 'X'
and Conn_id not in
(
  select Conn_id 
  from trail 
  where point in (select distinct(Conn_id) from trail where point = 'X') 
  and Type = 'Backup'
);
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • Hi Thorsten, So far the query looks good I have started to get some positive output. – Rahul Katarey Nov 18 '14 at 10:45
  • Well, I did nothing else but copy your query and replace MINUS with the NOT IN part. I don't know, if this query does exactly what you need (I didn't bother to think about what it does to tell the truth :-) I notice now that you use `distinct(Conn_id)`. DISTINCT is usually not used with parentheses; it always works on the complete row anyhow. Moreover with IN you are dealing with a set of values. 2 is in (1,2,3) and 2 is in (1,2,2,2,3). You don't have to tell the dbms that the set must be distinct. It may mean unnecessary work for the dbms. So replace `distinct(Conn_id)` with `Conn_id`. – Thorsten Kettner Nov 18 '14 at 10:53