joins - 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

General SQL Server Forums

New to SQL Server Programming

joins

Author

Topic

pureclass85
Starting Member

29 Posts

Posted - 2009-03-11 : 08:51:37

select event_name, location_name, ob_date, season
from table a
inner join seasons on EXTRACT(MONTH FROM observation_date) = month_id
where season = 'summer'

this works great shows the table with the seasons
where i am stuck is i have another table that has no dates but has seasons

i need the sql adding on that will show any entries that are in the second table but not the fist

pcsodo code:

table a - table b........ but it has to work with the code above

thanks

pureclass85
Starting Member

29 Posts

Posted - 2009-03-11 : 11:17:53

where iam i going wrong?
i wont this to work:

select event_name, location_name, ob_date, season
from table a
inner join seasons s on EXTRACT(MONTH FROM observation_date) = month_id
where s.season = 'summer' and event_name is not (select event_name2 from table2 where table2.season = 'summer')

this should show any unexpected events in summer
table 2 being a list of expected events
and first table being events that happoned

i wont to show the ones that were un expected in summer

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-11 : 11:33:00

This looks like MySQL and not SQL server...anyways..can u try this...

select event_name, location_name, ob_date, season
from table a
inner join seasons s on EXTRACT(MONTH FROM observation_date) = month_id
where s.season = 'summer' and event_name not in(select event_name2 from table2 where table2.season = 'summer')

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-11 : 11:41:14

Ssame question here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=121397

E 12°55'05.63"
N 56°04'39.26"

pureclass85
Starting Member

29 Posts

Posted - 2009-03-11 : 13:50:03

sorry about the same question but i though it was getting confusing 4 people so i set up a diff thread, that code works

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-11 : 13:56:51

also


1.select event_name, location_name, ob_date, season
from table a
inner join seasons s on EXTRACT(MONTH FROM observation_date) = month_id
left join table2 t2 on t2.event_name2=s.event_name  and t2.season = 'summer'
where s.season = 'summer' and t2.event_name2 is null

2.select event_name, location_name, ob_date, season
from table a
inner join seasons s on EXTRACT(MONTH FROM observation_date) = month_id
where s.season = 'summer' and  not exists(select 1 from table2 where table2.season = 'summer' and event_name2=s.event_name)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources