CODEWITHSUNDEEP
sqlpostgresql
BoxMan0617
BoxMan0617

Reputation: 140

SQL Join Two Columns In Same Table

I need to get records that are not used. My tables go like this:

segment table

| id | name |
-------------
| 1  | AAA  |
| 2  | BBB  |
| 3  | CCC  |
| 4  | DDD  |

segment_segment_assoc

| id | segment_name | child_name |
----------------------------------
| 1  | AAA          | BBB        |
| 2  | AAA          | CCC        |

The segment_segment_assoc table stores the parent/child relationships between the segments. I need a query that will be able to a count of how many segments in the segment table are not in the the segment_segment_assoc table.

The way I want to do this is somehow get the segment_segment_assoc table to transform to a single name type table. In example, it should turn to this =>

| name |
--------
| AAA  |
| BBB  |
| CCC  |

Where the segment_segment_assoc.segment_name and segment_segment_assoc.child_name columns are "appended" or "joined" together. Once I have that, I could then do a "NOT IN" to see which segments are not being "used" in the segment_segment_assoc table.

This should be written in SQL but just for context, I am using Postgres.

Thanks in advanced!

Upvotes: 1

Views: 942

Answers (1)

A. El-Helw
A. El-Helw

Reputation: 201

Using UNION should do the trick:

SELECT count(*) FROM segment
WHERE name NOT IN
(
    SELECT segment_name FROM segment_segment_assoc
    UNION
    SELECT child_name FROM segment_segment_assoc
)

Upvotes: 2

Related Questions