query regarding getting records after comparing

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 2005 Forums

Transact-SQL (2005)

query regarding getting records after comparing

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-03-12 : 09:21:17

amiteshraja writes "I have to find How many rows are there in a table after camparing it with another table on one field called "PURL"

Actually there is one table name "abc"
And there is another table name "xyz"

Both table has one common field name "purl"

the purl field in "abc" table has any of the below type values
"*.*.*.*.*" or "*.*.*.*" or "*.*.*"

And the purl field in "xyz" has only one type values
"*.*.*"

Now my task is to find out all the records from table "abc" which has same "purl" field values also in 2nd table "xyz"

Examples of PURL value in "abc" table
"michmcguire.testing.com"
"www.carlwilkerich.testing.com"
"aaa.alejandrajimenez.testing.com"
"http.courtneycochran.testing.com".... etc

Examples of PURL value in "xyz" table
"michmcguire.testing.com"
"tracybissonnette.testing.com"
"eliasalkhoury.testing.com"
"jamesgajeski.testing.com"

Now can you please provide me one single query so that I can fetch all the records from "abc" table which has same PURL values as in "xyz" table.

More brief : First I have to ommit for e.g "www." or "aaa." or "http." from PURL field of "abc" table then compare this new PURL value from 2nd table "xyz"

I need this in one single query & need it urgently as I tried it from last 3-4 hour to solve but not success.

Thanking You,
Amitesh
Software professional
India
"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-03-12 : 12:54:06

Will this work for you?

-- Set up

DECLARE @abc TABLE(purl VARCHAR(255))
DECLARE @xyz TABLE(purl VARCHAR(255))

INSERT @abc
SELECT 'michmcguire.testing.com' UNION ALL
SELECT 'www.carlwilkerich.testing.com' UNION ALL
SELECT 'aaa.alejandrajimenez.testing.com' UNION ALL
SELECT 'http.courtneycochran.testing.com'

INSERT @xyz
SELECT 'michmcguire.testing.com' UNION ALL
SELECT 'tracybissonnette.testing.com' UNION ALL
SELECT 'eliasalkhoury.testing.com' UNION ALL
SELECT 'jamesgajeski.testing.com'

-- Query
SELECT 
	a.purl
FROM 
	@abc a
INNER JOIN
	@xyz x
	ON
	CASE 
		WHEN PARSENAME(a.purl, 4) IS NULL THEN a.purl
		ELSE PARSENAME(a.purl, 3) + '.' + PARSENAME(a.purl, 2) + '.' + PARSENAME(a.purl, 1)
	END = x.purl

-Ryan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources