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-06-11 : 14:55:39

[code]
I m looking for a query to validate.

the tranip table ip must exist in master table if not error out the record.

Ex:

Masterip
--------
5.46.200.1.462222.2.1.1.1.1.1.1
5.46.200.1.462222.2.1.1.1.1.1.2
5.46.200.1.462222.2.1.1.1.1.1.3
5.46.200.1.462222.2.1.1.1.1.1.4

Tranip
-------
5.46.200.1.462222.2.1.1.1.1.1.1.5.6
5.46.200.1.462222.2.1.1.1.1.1.2.3.2
5.46.200.1.462222.2.1.1.1.1.1.3.2.3.6.4
5.46.200.1.462222.2.1.1.1.1.1.4.1.2.8.4

Expected output
-----------------

ip status Masterip
---------------------------------- ------ --------
5.46.200.1.462222.2.1.1.1.1.1.1.5.6 Valid 5.46.200.1.462222.2.1.1.1.1.1.1
5.46.200.1.462222.2.1.1.1.1.1.2.3.2 Valid 5.46.200.1.462222.2.1.1.1.1.1.2
5.46.200.1.462222.2.1.1.1.1.1.3.2.3.6.4 Valid 5.46.200.1.462222.2.1.1.1.1.1.3
5.46.200.1.462222.2.1.1.1.1.1.4.1.2.8.4 Valid 5.46.200.1.462222.2.1.1.1.1.1.4[/code]

Thanks for yur help in advance..

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-11 : 15:08:29

If you assume Masterips are unique in the MasteripTable, you can write a query like this:

SELECT
	t.Tranip,
	CASE WHEN m.Masterip IS NOT NULL THEN 'Valid' ELSE 'Invalid' END AS [status],
	m.MasterIp
FROM
	TranipTable t
	LEFT JOIN MasteripTable m ON t.Tranip LIKE m.MasterIp+'%'

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-06-11 : 17:16:40

Thanks Sunitabeck

It works...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources