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

Transact-SQL (2005)

sql help

Author

Topic

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-08-14 : 02:49:40

Precarious situation for me. Can't think of the best way to do it.

Table 1(all field are nullable,, no primary key, rows can be repeated over and over again)
col1,col2
---------
n,s
n1,s

Table 2(all field are nullable,, no primary key, rows can be repeated over and over again)
id,xcol1,xcol2,rule
------------------
1,n,null,rul1
2,null,s,rul2
3,n,s,rul3

Basis Table1 records, I have to look in table2 to find the first match of col1 on xcol1, if I don't get it, I have to look for the first match of col2 on xcol2.

So, the output will be

n,s,rul1.
n1,s,rul2

If the records are like this :

Table 1
col1,col2
-----------
n,s
n1,s1

Table 2

id,xcol1,xcol2,rule
------------------
2,null,s,rul2
3,null,s,rul3

output
n,s,rul2
n1,s1,norul

If i find nothing, I display "norul". The output count will always be the same as table1.

Hope I am clear. I am using sql 2000 btw.

Any help would be appreciated. Thanks.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-14 : 03:01:30

you say you need to find the 'first match', but that it has no primary key (no clustered index?), so what determines the order that will help you find the 'first match'?

Em

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-08-14 : 03:03:04

sid. The lowest one.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-08-14 : 14:52:05

anyone ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-14 : 15:01:01

[code]SELECT t1.col1,t1.col2,t2.rule
FROM Table1 t1
CROSS APPLY(SELECT Top 1 rule
FROM Table2
WHERE xcol1=t1.col1
OR xcol2=t1.col2
ORDER BY id)t2[/code]

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-08-14 : 17:11:34

quote:

Originally posted by visakh16

SELECT t1.col1,t1.col2,t2.rule
FROM Table1 t1
CROSS APPLY(SELECT Top 1 rule
            FROM Table2
            WHERE xcol1=t1.col1
            OR xcol2=t1.col2
            ORDER BY id)t2

I am using Sql 2000. Is there anyway to do the same in sql 2000.I am sure there is, If you can tell me the logic, I'll code it myself. I don't know what cross apply is for.
Thanks a lot.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-08-14 : 17:17:20

This may help:
http://www.sqlteam.com/article/using-cross-apply-in-sql-server-2005

Greetings
Webfred

Too Old to Rock 'n' Roll, Too Young to Die

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-08-14 : 19:37:49

quote:
Originally posted by sakets_2000

quote:
Originally posted by visakh16

SELECT t1.col1,t1.col2,t2.rule
FROM Table1 t1
CROSS APPLY(SELECT Top 1 rule
FROM Table2
WHERE xcol1=t1.col1
OR xcol2=t1.col2
ORDER BY id)t2

I am using Sql 2000. Is there anyway to do the same in sql 2000.I am sure there is, If you can tell me the logic, I'll code it myself. I don't know what cross apply is for.
Thanks a lot.

Just a quick question, Would the result of your select stay the same (to the select I have mentioned down below) , if I were to do something like this :->

--add a column to t1 called derived_rul
--Perform the following updates::
update table1
set derived_rul=rule
from table1 t1,table2 t2
where
t1.col1=t2.col1

update table1
set derived_rul=rule
from table1 t1,table2 t2
where
t1.col1=t2.col1
and rule is not null

select * from table1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-14 : 22:39:44

quote:
Originally posted by sakets_2000

quote:
Originally posted by visakh16

SELECT t1.col1,t1.col2,t2.rule
FROM Table1 t1
CROSS APPLY(SELECT Top 1 rule
FROM Table2
WHERE xcol1=t1.col1
OR xcol2=t1.col2
ORDER BY id)t2

I am using Sql 2000. Is there anyway to do the same in sql 2000.I am sure there is, If you can tell me the logic, I'll code it myself. I don't know what cross apply is for.
Thanks a lot.

but you've posted this in 2005 forum

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-08-16 : 10:24:54

quote:
Originally posted by visakh16

quote:
Originally posted by sakets_2000

quote:
Originally posted by visakh16

SELECT t1.col1,t1.col2,t2.rule
FROM Table1 t1
CROSS APPLY(SELECT Top 1 rule
FROM Table2
WHERE xcol1=t1.col1
OR xcol2=t1.col2
ORDER BY id)t2

I am using Sql 2000. Is there anyway to do the same in sql 2000.I am sure there is, If you can tell me the logic, I'll code it myself. I don't know what cross apply is for.
Thanks a lot.

but you've posted this in 2005 forum

Ah, am sorry.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources