Conditional clause form two tables in sql server

Seema Bawa

I have two tables. I want to get all the rows from first table but want the where clause from another table. Here is my query. In following query I don't want any row from table tbTasks1 but the following query showing me rows from tbTasks1 also. Could u please modify this query.

select * from tbDocument,tbTasks1
where
tbTasks1.IsDelete = 0

_Damian S_

There must be something that joins the two tables together... what is it? Use a join to attach the tables together like this:

select tbDocument.*
from tbDocument inner join tbTasks1 on tbDocument.FOREIGNKEY = tbTasks1.PRIMARYKEY
where tbTasks1.IsDelete = 0

Silence is golden... but duct tape is silver!! Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!

Varsha Ramnani

This will give same result as op's Query... The op's Query is using implicit inner join. Rather than inner join, Left Or Right Join will work for op's requirement.

"Insanity is doing the same thing over and over again but expecting different results.” — Rita Mae Brown "If you tell the truth, you don't need to remember anything" - Mark Twain

Varsha Ramnani

Try this Query :

Select * from tbDocument
Left join tbTasks1 On tbDocument.Column=tbTasks1.Column and tbTasks1.IsDelete = 0

Your Query is using implicit inner join & inner Join by default applies condition (Filters) on Both tables. You need to use Left Join Or Right Join for you requirement.

"Insanity is doing the same thing over and over again but expecting different results.” — Rita Mae Brown "If you tell the truth, you don't need to remember anything" - Mark Twain

David Skelly

V@rsh@ wrote:

This will give same result as op's Query

No, it won't. There is a big difference between these two queries:

select * from tbDocument,tbTasks1
where
tbTasks1.IsDelete = 0

select tbDocument.*
from tbDocument inner join tbTasks1 on tbDocument.FOREIGNKEY = tbTasks1.PRIMARYKEY
where tbTasks1.IsDelete = 0

The first one is called a "cartesian join" and is almost always a mistake.

David Skelly

I'm not sure what you mean when you say that you don't want any rows from tbTasks1. I assume that you mean that you don't want to see the tbTasks1 columns in your result set. In which case you should specify which columns you want, something like:

select tbDocument.col1, tbDocument.col2, tbDocument.col3
from tbDocument inner join tbTasks1
on tbDocument.aKey = tbTasks.aKey
where tbTasks1.IsDelete = 0

Edit: which I've just realised is exactly the same answer Damian gave earlier. Doh!

sri080188

if both the tables having same structure u can use select * from tbDocument union all select * from tbTasks1 where tbTasks1.IsDelete = 0

Varsha Ramnani

Agreed op has not used any condition to join both tables in the "Where" clause because of which it uses Cartesian product rather than Inner Join... My fault :( But it will be same as

Select * from tab1
Inner Join tab2 on 1=1
where tab2.somefield=somevalue

So I think that was actually representing implicit inner join which uses cartesian product :~

"Insanity is doing the same thing over and over again but expecting different results.” — Rita Mae Brown "If you tell the truth, you don't need to remember anything" - Mark Twain

SilimSayo

You could use

SELECT Col1.T1, Col2.T1, Col3.T1
FROM table1 T1 join table2 T2
ON T1.Col1=Coln.T2

OR

SELECT Col1.T1, Col2.T1, Col3.T1
FROM table1 T1
WHERE COL1 in (SELECT Col1 FROM table 2 where Coln=something1 and colm=somethingelse and yadayada)