Query Help - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Query Help

Author

Topic

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-09-10 : 12:55:06

[code]
Table:Ipmaster
--------------

ipid name ip
---- ------ ----------------------
20 test1 1.12.340.1.123444.3.3.2
27 test2 1.12.340.1.123444.3
28 test3 1.12.340.1.123444.3.3

Table:Iptrans
-------------

ID Ip
-- -----------------------------------
1 1.12.340.1.123444.3.3.2.2.2.1
2 1.12.340.1.123444.3.3.2.2.2.1.1
3 1.12.340.1.123444.3.3.2.2.2.1.2
4 1.12.340.1.123444.3.3.2.2.2.1.3
5 1.12.340.1.123444.3.3.2.2.2.1.4
6 1.12.340.1.123444.3.3.2.2.2.1.5
7 1.12.340.1.123444.3.3.2.2.2.1.6

Expected output:

ID Ip ipid
-- ----------------------------------- -----
1 1.12.340.1.123444.3.3.2.2.2.1 20
2 1.12.340.1.123444.3.3.2.2.2.1.1 20
3 1.12.340.1.123444.3.3.2.2.2.1.2 20
4 1.12.340.1.123444.3.3.2.2.2.1.3 20
5 1.12.340.1.123444.3.3.2.2.2.1.4 20
6 1.12.340.1.123444.3.3.2.2.2.1.5 20
7 1.12.340.1.123444.3.3.2.2.2.1.6 20

I want a query which should get the ipid from IPmaster for the IPtrans table of transacation records.

[/code]

Thanks for your help in advance..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-10 : 12:59:49

[code]
SELECT i.*,t.ipid
FROM Iptrans i
CROSS APPLY (SELECT TOP 1 ipid
FROM Ipmaster
WHERE i.Ip LIKE Ip + '%'
ORDER BY LEN(Ip) DESC)t
[/code]

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

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-09-10 : 13:38:14

Thanks a lot visakh..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-10 : 14:33:20

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources