Basic Query Help

[Jeff Proctor]

my 7 day query....

select s.name, l.ondate from
site s, log l where
substring(id,3,6) in(select name from site)
and ondate between getdate()-8 and getdate()-1

This returns those sites that exist in the current log, however what I want is a result set that has a row for each name regardless if they are in the log.

I have 2 tables...

table1 sites
name
smithj
anandt
burtot
proctj
etc....

table2 log
id ondate
02anandt 07/01/01
02anandt 07/03/01
02anandt 07/03/01
01smithj 07/03/01
03burtot 07/05/01
01smithj 07/05/01
03burtot 07/05/01
etc.....

[RickD]

Make log an outer join to site.

e.g

select s.name, l.ondate from
site s
RIGHT OUTER JOIN log l ON <Join Clause> where
substring(id,3,6) in(select name from site)
and ondate between getdate()-8 and getdate()-1

You didn&#39;t seem to have any logical join between the tables, but i&#39;m sure you have, just put this in the <Join Clause>.

Oh, and read BoL, it helps no end.....

------------
Jeff Proctor at 7/10/01 11:18:31 PM

my 7 day query....

select s.name, l.ondate from
site s, log l where
substring(id,3,6) in(select name from site)
and ondate between getdate()-8 and getdate()-1

This returns those sites that exist in the current log, however what I want is a result set that has a row for each name regardless if they are in the log.

I have 2 tables...

table1 sites
name
smithj
anandt
burtot
proctj
etc....

table2 log
id ondate
02anandt 07/01/01
02anandt 07/03/01
02anandt 07/03/01
01smithj 07/03/01
03burtot 07/05/01
01smithj 07/05/01
03burtot 07/05/01
etc.....

[RickD]

Make log an outer join to site.

e.g

select s.name, l.ondate from
site s
RIGHT OUTER JOIN log l ON <Join Clause> where
substring(id,3,6) in(select name from site)
and ondate between getdate()-8 and getdate()-1

You didn&#39;t seem to have any logical join between the tables, but i&#39;m sure you have, just put this in the <Join Clause>.

Oh, and read BoL, it helps no end.....

------------
Jeff Proctor at 7/10/01 11:18:31 PM

my 7 day query....

select s.name, l.ondate from
site s, log l where
substring(id,3,6) in(select name from site)
and ondate between getdate()-8 and getdate()-1

This returns those sites that exist in the current log, however what I want is a result set that has a row for each name regardless if they are in the log.

I have 2 tables...

table1 sites
name
smithj
anandt
burtot
proctj
etc....

table2 log
id ondate
02anandt 07/01/01
02anandt 07/03/01
02anandt 07/03/01
01smithj 07/03/01
03burtot 07/05/01
01smithj 07/05/01
03burtot 07/05/01
etc.....