CODEWITHSUNDEEP
sqlvb.netcomparison
E-r Gabriel Doronila
E-r Gabriel Doronila

Reputation: 533

How to check if a column data from table exist in the same table?

How can I do this in SQL select statement..

Considering I have this table

ID_A - DATE1 - DATE2
=====================
CD99 - 11/25 - 12/08
AB23 - 11/20 - 11/22
AB23 - 11/22 - 12/01
XP72 - 11/23 - 12/08

You will notice that ID_A=AB23 has two entries and DATE2 of first line is equal to DATE1 of the second line, this means that these two lines are connected.

So, how can I get or create my select statement to view these lines that are connected to each other?

EDIT: What I was trying to do in my select statement is this: a. check DATE2 if is not null b. from there, check DATE2 if it exists in the DATE1 of whole table and return the value of columns.

What I would like to get should look like this:

ID_A - DATE1 - DATE2
=====================
AB23 - 11/20 - 11/22
AB23 - 11/22 - 12/01

P.S. I was hoping to not do this using a loop.. As this tend to get the server too slow to respond when I get to have too much of data. - Let me note that i am comparing columns within the same database..

@fthiella here is my sample data with the scenario im referring to..

ID_A - DATE1 - DATE2
=====================
CD99 - 11/25 - 12/08
AB23 - 11/20 - 11/22
AB23 - 11/22 - 12/01
XP72 - 11/23 - 12/08
PQ10 - 11/20 - -n/a-
LM88 - 11/21 - -n/a-
PQ10 - 11/15 - 11/20

from there i would like to get these:

ID_A - DATE1 - DATE2
=====================
CD99 - 11/25 - 12/08
AB23 - 11/22 - 12/01
XP72 - 11/23 - 12/08

here's a quick explanation of why i excluded the others:

ID_A - DATE1 - DATE2
=====================
CD99 - 11/25 - 12/08 - not excluded
AB23 - 11/20 - 11/22 - excluded because DATE2 is connected to DATE1 of same ID_A
AB23 - 11/22 - 12/01 - not excluded
XP72 - 11/23 - 12/08 - not excluded
PQ10 - 11/20 - -n/a- - excluded because DATE2 is null
LM88 - 11/21 - -n/a- - excluded because DATE2 is null
PQ10 - 11/15 - 11/20 - excluded because DATE2 is connected to DATE1 of same ID_A (regardless if DATE2 of that line is null)

priority of condition can be - exclude null DATE2 first, then check comparison and exclude those lines that are preceeding other lines..

Sorry for having so much of your time, I really appreciate every help you have given me.. for now this question can be tagged as ANSWERED, thanks again fthiella..

Upvotes: 3

Views: 3776

Answers (3)

Juan Filipe
Juan Filipe

Reputation: 403

SELECT date1.* FROM DATES date1 JOIN DATES date2 On date1.ID_A = date1.ID_A
WHERE date1.DATE2 = date2.DATE1
UNION
SELECT D2.* FROM DATES date1 JOIN DATES date2 On date1.ID_A = date2.ID_A
WHERE date1.DATE2 = date2.DATE1

Upvotes: 1

fthiella
fthiella

Reputation: 49049

I would do this:

SELECT
  your_table.*
FROM
  your_table inner join your_table your_table_1
  on your_table.ID_A=your_table_1.ID_A
     and (your_table.DATE2 = your_table_1.DATE1
          or your_table.DATE1 = your_table_1.DATE2)

I'm joining your_table with itself, using an inner join, so it will return rows only if for each row there's a connection (same ID_A, DATE2=DATE1 or DATE1=DATE2).

EDIT: and this should give you records that don't have a connection, based on your comment:

SELECT your_table.*
FROM
  your_table left join your_table your_table_1
  on your_table.ID_A=your_table_1.ID_A
     and (your_table.DATE2 = your_table_1.DATE1)
WHERE your_table_1.ID_A is null
      and your_table.DATE2 is not null

Here I'm joining your_table with itself, using a left join, and filtering results with ID_A is null. This means that this query will return every record in your_table, except the ones that have DATE2 equal to a DATE1 in the same table. If DATE2 is null already, the join won't succeed so rows will be returned.

Upvotes: 0

Yograj Gupta
Yograj Gupta

Reputation: 9869

You can try this 

SELECT D1.* FROM DATES D1
JOIN DATES D2
On D1.ID_A = D2.ID_A
WHERE D1.DATE2 = D2.DATE1

UNION

SELECT D2.* FROM DATES D1
JOIN DATES D2
On D1.ID_A = D2.ID_A
WHERE D1.DATE2 = D2.DATE1

Upvotes: 0

Related Questions