joining 2 tables, different conditions on tables

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)

joining 2 tables, different conditions on tables

Author

Topic

nbalraj
Starting Member

21 Posts

Posted - 2009-04-16 : 09:19:59

Hi,
I have these 2 tables:

table1

cntTable1 cntTable2 ysnaward
1 NULL 0
2 NULL 0
3 NULL 0
4 4 1

cntTable2 is the primarykey in table2

table 2
cntTable2 strTable2 ysnapproved
1 sampledesc1 0
2 sampledesc2 1
3 sampledesc3 1
4 sampledesc4 1

Need help in selecting all the records from table2, but need to avoid records, for which the table1 has ysnaward = 1 and in table2 ysnaward = 1

the end result should be like this

cntTable2 strTable2 ysnapproved ysnaward
2 sampledesc2 0 1
3 sampledesc3 0 1

thanks for any help,
NB

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-04-16 : 09:34:49

That doesn't make any sense as you don't have ysnaward in table2.

I guess what you are after is:


select b.cntTable2 b.strTable2 b.ysnapproved a.ysnaward
from Table1 a
inner join table2 b
on a.cntTable1 = b.cntTable2
where a.ysnaward <> 1
and b.ysnapproved = 1

nbalraj
Starting Member

21 Posts

Posted - 2009-04-17 : 13:21:12

Hi,
Thanks for your reply, let me try to recreate the scenario:

table1

cnttable1 strdesc ysnApproved
1 desc1 1
2 desc2 1
3 desc3 0
4 desc4 1
5 desc5 1
6 desc6 1

table2
cnttable2 cnttable1 ysnAward
1 NULL 1
2 NULL 0
3 NULL 0
4 1 0
5 2 1

cnttable1 is the primary key for table1

expected results from table1

cnttable1 strdesc ysnApproved
2 desc2 1
4 desc4 1
5 desc5 1
6 desc6 1

the criteria need to use is ysnapproved = 1 and ysnAward = 1
all the records from table1, where ysnapproved = 1 and if the record is in table2 show only if the ysnaward = 1

hope this helps. and thanks for ur help.
-NB

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-04-17 : 13:57:54

maybe this...
declare @t1 table (cnttable1 int,strdesc varchar(10),ysnApproved int)
insert @t1
select 1, 'desc1', 1 union all
select 2, 'desc2', 1 union all
select 3, 'desc3', 0 union all
select 4, 'desc4', 1 union all
select 5, 'desc5', 1 union all
select 6, 'desc6', 1

declare @t2 table (cnttable2 int, cnttable1 int, ysnAward int)
insert @t2
select 1, NULL, 1 union all
select 2, NULL, 0 union all
select 3, NULL, 0 union all
select 4, 1, 0 union all
select 5, 2, 1

select t1.cnttable1 , t1.strdesc , t1.ysnApproved,t2.ysnAward
from @t1 t1 left join
@t2 t2
on t1.cnttable1 = t2.cnttable1
where t1.ysnApproved = 1
and ISNULL(t2.ysnAward,1) = 1

Result
------
cnttable1 strdesc ysnApproved ysnAward
----------- ---------- ----------- -----------
2 desc2 1 1
4 desc4 1 NULL
5 desc5 1 NULL
6 desc6 1 NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-17 : 14:00:04

[code]SELECT t1.*
FROM table1 t1
LEFT JOIN table2 t2
ON t2.cnttable1=t1.cnttable1
WHERE t1.ysnApproved=1
AND (t2.ysnAward=1 OR t2.ysnAward IS NULL)
[/code]

nbalraj
Starting Member

21 Posts

Posted - 2009-04-17 : 22:31:35

thanks guys.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources