quering 1 tables with conditions

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

SQL Server Development (2000)

quering 1 tables with conditions

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-04-04 : 09:23:00

i have 2 tables
which has same a column called id.
table1.id and table2.id
(id is not unique can appear more then once)
i want to do like this:
if the id exist in table1 the return all of its rows,
if and only if there is not row from table1 then and only then get them from table2!
is this possbiel?
thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-04 : 09:25:36

Use left outer join

Madhivanan

Failing to plan is Planning to fail

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-04-04 : 09:32:31

how execlly???

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-04 : 09:34:59

Try this

Select t1.*,t2.* from table1 T1 left outer join table2 t2 on t1.id=t2.id

Otherwise post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-04-04 : 15:22:27

for example
table1 :
id
3
6
8

table2
3
11
77
i want to:
select from table1 where id in(3,8)
if there is NO result!!! then select from table2 :
select from table2 where id in(3,8)
is this helps???

thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-04 : 15:25:49

SELECT * FROM Table1 WHERE [id] IN (3, 8)

IF @@ROWCOUNT = 0
SELECT * FROM Table2 WHERE [id] IN (3, 8)

But perhaps what you want is a UNION. It's hard to know without seeing DDL for the tables and some more sample data.

Tara Kizer
aka tduggan

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-04-04 : 16:07:52

how can i use IF @@ROWCOUNT = 0
???
the whole point is to do this in 1 sql select syntax from asp!

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-04 : 16:13:37

Well we need more information. Please see my last post.

Tara Kizer
aka tduggan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-05 : 01:44:32

>>the whole point is to do this in 1 sql select syntax from asp!

Why do you want to do this in a single query?
Create stored procedure having Tara's query and run that

or

If exists(SELECT * FROM Table1 WHERE [id] IN (3, 8))
SELECT * FROM Table1 WHERE [id] IN (3, 8)
else
SELECT * FROM Table2 WHERE [id] IN (3, 8)

Madhivanan

Failing to plan is Planning to fail

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-04-05 : 01:54:32

quote:
Why do you want to do this in a single query?
Create stored procedure having Tara's query and run that

i know how to do thi with SP but that isnt the point!
i waned to seif its possible to do it some how withought SP, and if yes to use it.

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-05 : 02:35:31

No. You need to do this in sp for better performance

Madhivanan

Failing to plan is Planning to fail

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-04-05 : 02:37:01

again i want to know if its posiible and if yes how to do it
(and yes i know SP will give better performance)

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-05 : 12:29:57

I'll repeat it, since you haven't provided the information: "But perhaps what you want is a UNION. It's hard to know without seeing DDL for the tables and some more sample data."

Tara Kizer
aka tduggan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources