Edit: SQL multiple table instances

Question

I have this table:

SUBSCRIPTION NAME SUBSCRIBER 

This is essentially a table with a list of people who subscribe to each other. I want to write a query that displays a list of tuples in such a way that if the set B represents all those who subscribe to A, B also represents those people whom A does not subscribe. those. find a list of people who are not mutually subscribed to each other.

This is the query I wrote:

 SELECT A.name, B.name FROM subscription AS A, subscription AS B WHERE A.subscriber=B.name AND A.name!=B.subscriber; 

those. it should display two columns A and B, where B subscribes to A and A, does not subscribe to B.

All I get is garbage with lots of rows duplicated. What am I doing wrong in this?

Well, I was mistaken in specifying the samples. Samples look like this:

Melissa, Joan sign up for John. Charles, John sign up for Joan. Charles agrees with Melissa. Melissa, Joan subscribe to Charles.

the first part refers to the subscriber column, and the second to the name column. those. name of John and Melissa, Joan signs up for John.

he must output (melissa, john) because john does not subscribe to melissa.

How will this change the request?

Answer 1

This pulls out all the entries from the subscription table, where in the subscription table there is no corresponding match for the opposite subscription.

 select subscriber, name from subscription a where not exists ( select 1 from subscription b where a.subscriber = b.name and a.name = b.subscriber) 

Answer 2

Looks like you want to make a LEFT JOIN. This will return all the names of A and associate them with all the names of B. For each name A there will be at least 1 record, but possibly a lot. Before how many names B are subscribed to name A. If there is no B-subscriber, then there will be one name A, but column B will be NULL.

 SELECT A.name, B.name FROM subscription AS A LEFT JOIN subscription AS B ON A.subscriber=B.name AND A.name!=B.subscriber;