Cross referencing one table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Cross referencing one table

Author

Topic

bd528
Starting Member

23 Posts

Posted - 2011-12-06 : 07:26:02

Hello,

I have a table called meter with the following basis structure

meter_link
meterno
parentlink

When I run my query, I get the following example results

meter_link meterno parent_link
1231 ABC 2353
5674 GHY 6436
6436 OUY 4356

Each parent_link refers to a meter_link. What I would like (using the above results as an example) is another column showing the parentlink's meterno, such as :-

meter_link meterno parent_link meterno2
5674 GHY 6436 OUY

The code I have written so far is :-

SELECT meter.meter_link, meter.meterno, meter.parent_link
FROM meter

I'd be most grateful for any help!

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-06 : 07:32:43

[code]
SELECT m1.meter_link, m1.meterno, m1.parent_link,m2.meterno as meterno2
FROM meter m1
JOIN meter m2
ON m2.meter_link = m1.parent_link
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bd528
Starting Member

23 Posts

Posted - 2011-12-06 : 08:13:08

The above works great - thank you.

If the parent_link is "0", then there cannot be a corresponding meter_link. When using your code above, these entries are excluded from the query results. Is it possible to still include these? So example results could be :-

meter_link meterno parent_link meterno2
1231 ABC 0
5674 GHY 6436 OUY
6436 OUY 0

Thanks again.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2011-12-06 : 09:09:38

quote:
Originally posted by bd528

The above works great - thank you.

If the parent_link is "0", then there cannot be a corresponding meter_link. When using your code above, these entries are excluded from the query results. Is it possible to still include these? So example results could be :-

meter_link meterno parent_link meterno2
1231 ABC 0
5674 GHY 6436 OUY
6436 OUY 0

Thanks again.

Change it to a LEFT join as below

SELECT m1.meter_link, m1.meterno, m1.parent_link, COALESCE(m2.meterno,'0') as meterno2
FROM meter m1
LEFT JOIN meter m2
ON m2.meter_link = m1.parent_link

bd528
Starting Member

23 Posts

Posted - 2011-12-06 : 12:46:15

Excellent, thanks for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources