I've got a situation as follows (note this setup has been created as a minimal reproducible example). I have a table of houses, created like this: I have a large table called house_listings, like this: So, let's say I run the following query: and I get the following output: and then I run the following query: select * from house_listings

SQL – Returning all rows that are not ‘vice versa’ of one another

I’ve got a situation as follows (note this setup has been created as a minimal reproducible example). I have a table of houses, created like this:

CREATE TABLE houses (
    id character varying(24) NOT NULL,
);

I have a large table called house_listings, like this:

CREATE TABLE house_listings(
    id character varying(24) NOT NULL,
    house_one character varying(24) NOT NULL,
    house_two character varying(24) NOT NULL,
    for_sale bit NOT NULL,
    PRIMARY KEY (house_one, house_two),
    FOREIGN KEY (house_one) REFERENCES houses(id),
    FOREIGN KEY (house_two) REFERENCES houses(id)
);

So, let’s say I run the following query:

select * from house_listings where house_one = '12345';

and I get the following output:

+------------------------+------------------------+-------------------+--------------+
| id                     | house_one              | house_two         | for_sale     |
+------------------------+------------------------+-------------------+--------------+
| 9RV3fyeJuUbAsHYdAb2Qnm | 12345                  | 8888              | 0x00         |
+------------------------+------------------------+-------------------+--------------+

and then I run the following query:

select * from house_listings where house_two = '12345' and I get the following result:

+------------------------+-------------------+------------------------+--------------+
| id                     | house_one         | house_two              | for_sale     |
+------------------------+-------------------+------------------------+--------------+
| 1oFH1oDkLyng6ZHNafqcXr | 8888              | 12345                  | 0x00         |
| vW4eNAC7jgZVxWAGxH4xAR | 7777              | 12345                  | 0x00         |
+------------------------+-------------------+------------------------+--------------+

Notice how the first row of this result corresponds to the first row of the other result in a sort of vice-versa way (i.e. in this case house_one and house_two are flipped).

However, what I’m interested in here is the second row of this result. Where house_one is 7777 and house_two and 12345.

I’d like to somehow run a query that will essentially return all rows like the second. So, given a specific house id, like 12345, it would only return (in this example):

+------------------------+-------------------+------------------------+--------------+
| id                     | house_one         | house_two              | for_sale     |
+------------------------+-------------------+------------------------+--------------+
| vW4eNAC7jgZVxWAGxH4xAR | 7777              | 12345                  | 0x00         |
+------------------------+-------------------+------------------------+--------------+

I have been experimenting with self joins here, as I think this might be the best way to go, but something joining where house_one = house_two isn’t quite what I want, and I’m not sure how to best approach this issue.

Would appreciate any help here!

Answer

You can use not exists:

select hl.*
from house_listings hl
where not exists (select 1
                  from house_listings hl2
                  where hl2.house_one = hl1.house_two and
                        hl2.house_two = hl1.house_one
                 );

This returns all such singletons. You can, of course, add a where clause to the outer query to limit to a particular value of house_one.

Advertisement

Answer