CODEWITHSUNDEEP
sql
user2598997
user2598997

Reputation: 297

SQL Server: joins in one table

I'm trying to extract data from SQL to an excel file, Here is sample data from the table. The data exists in only one table and i cannot control the way data are inserted in it so cannot use joins between more one table.

 +------+--------------------------------+--------------------------------------+------------------------------------+
 |  ID  |              URL               |                TITLE                 |              LINKS_TO              |
 +------+--------------------------------+--------------------------------------+------------------------------------+
 | 1    | http://www.loc.gov/            | Library                              | http://www.loc.gov/library         |
 | 2    | http://www.loc.gov/library     | Catalog                              | NULL                               |
 | 3    | http://www.nasa.gov/           | NASA                                 | http://www.nasa.gov/images         |
 | 4    | http://www.nasa.gov/images     | National Institutes of Health (NIH)  | NULL                               |

I need to extract the ORIGINAL_URL and the TITLE of it's outlink(LINKS_TO) as in the output i put below. I tried joining the table to itself but the it got too complex for me. Here's the wanted output.

 +------------------------------+---------------------------------------+
 |          ORIGIN_URL          |                 TITLE                 |
 +------------------------------+---------------------------------------+
 | http://www.loc.gov/          | Catalog                               |
 | http://www.nasa.gov/         | National Institutes of Health (NIH)   |

Any help please?

Upvotes: 0

Views: 46

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460208

You can LEFT OUTER JOIN this table with itself:

SELECT U.URL AS ORIGIN_URL,          
       Linked.TITLE
FROM dbo.URLs U
LEFT OUTER JOIN dbo.URLs Linked
   ON U.LINKS_TO = Linked.URL

Or use INNER JOIN if you only want those which are linked:

SELECT U.URL AS ORIGIN_URL,          
       Linked.TITLE
FROM dbo.URLs U
INNER JOIN dbo.URLs Linked
   ON U.LINKS_TO = Linked.URL

Upvotes: 2

Related Questions