Outer Join that limits one side

I'm trying to retrieve a result set that uses an Outer Join, but I want to
limit the records in one of tables. The problem is when I insert the Where
condition, it doesn't show the other "Outer" records from the other table.
Here is the current SQL Syntax:
SELECT ES.WebContactID, P.ProjectName, P.ProjectID, PF.FamilyName
FROM PROJECT_FAMILY PF INNER JOIN
PROJECT P ON PF.FamilyID = P.FamilyID LEFT OUTER JOIN
EMAIL_SIGNUP ES ON P.ProjectID = ES.ProjectID
WHERE ES.WebContactID = 1
When I remove the WHERE condition, it displays all the records from PROJECT,
which is what I want, but also all the EMAIL_SIGNUP records, which I don't
want. I want to limit the EMAIL_SIGNUP to one ID, which is the primary key.
The result should look like this:
1 (Project Name A) 1 (Family Name X)
NULL (Project NameB ) 2 (Family Name X)
1 (Project Name C) 3 (Family Name Y)
Instead it looks like this:
1 (Project Name A) 1 (Family Name X)
1 (Project Name C) 3 (Family Name Y)
I assuming I need to move the "ES.WebContactID = 1". Thanks.Try:
SELECT ES.webcontactid, P.projectname, P.projectid, PF.familyname
FROM project_family AS PF
INNER JOIN project AS P
ON PF.familyid = P.familyid
LEFT OUTER JOIN email_signup AS ES
ON P.projectid = ES.projectid
AND ES.webcontactid = 1
David Portas
SQL Server MVP
--|||As soon as you put a predicate in the where clause, which operates on the
"wrong" side of an outer join, you effectively destroy the "Outerness".
Think of it this way, The Join creates a temporarily constructed resultset
consisting of everything put together up to then, plus the records from the
new table being joined, using the join conditions... WEach Join repeats this
process, using only the conditions assiated with that specific join.
The where clause conditions, on the other hand, apply to the last
constructed resultset, after all joins have been done.
So before your where clause operates, all those record s were in there,
including the ones from PROJECT_FAMILY and PROJECT that had no counterparts
in EMAIL_SIGNUP - but for every one of those, the columns from EMAIL_SIGNUP
were NULL. then you say
Where ES.WebContactID = 1, and that eliminates all of them, because
ES.WebContactID Is NULL for all of them...
What you need to do is add this additional predicate condition in the Join
conditions.
Like THis:
Select ES.WebContactID, P.ProjectName, P.ProjectID, PF.FamilyName
From PROJECT_FAMILY PF
Join PROJECT P
On PF.FamilyID = P.FamilyID
Left Join EMAIL_SIGNUP ES
On P.ProjectID = ES.ProjectID
And ES.WebContactID = 1
"jmhmaine" wrote:

> I'm trying to retrieve a result set that uses an Outer Join, but I want to
> limit the records in one of tables. The problem is when I insert the Where
> condition, it doesn't show the other "Outer" records from the other table.
> Here is the current SQL Syntax:
> SELECT ES.WebContactID, P.ProjectName, P.ProjectID, PF.FamilyName
> FROM PROJECT_FAMILY PF INNER JOIN
> PROJECT P ON PF.FamilyID = P.FamilyID LEFT OUTER JOIN
> EMAIL_SIGNUP ES ON P.ProjectID = ES.ProjectID
> WHERE ES.WebContactID = 1
> When I remove the WHERE condition, it displays all the records from PROJEC
T,
> which is what I want, but also all the EMAIL_SIGNUP records, which I don't
> want. I want to limit the EMAIL_SIGNUP to one ID, which is the primary ke
y.
> The result should look like this:
> 1 (Project Name A) 1 (Family Name X)
> NULL (Project NameB ) 2 (Family Name X)
> 1 (Project Name C) 3 (Family Name Y)
> Instead it looks like this:
> 1 (Project Name A) 1 (Family Name X)
> 1 (Project Name C) 3 (Family Name Y)
> I assuming I need to move the "ES.WebContactID = 1". Thanks.|||jmhmaine wrote on Tue, 15 Mar 2005 07:35:02 -0800:

> I'm trying to retrieve a result set that uses an Outer Join, but I want to
> limit the records in one of tables. The problem is when I insert the Where
> condition, it doesn't show the other "Outer" records from the other table.
> Here is the current SQL Syntax:
> SELECT ES.WebContactID, P.ProjectName, P.ProjectID, PF.FamilyName
> FROM PROJECT_FAMILY PF INNER JOIN
> PROJECT P ON PF.FamilyID = P.FamilyID LEFT OUTER JOIN
> EMAIL_SIGNUP ES ON P.ProjectID = ES.ProjectID
> WHERE ES.WebContactID = 1
You are limiting the results to only those that have a WebContactID value of
1. You need to add an OR for the NULL values where there is no matching
record.
SELECT ES.WebContactID, P.ProjectName, P.ProjectID, PF.FamilyName
FROM PROJECT_FAMILY PF INNER JOIN
PROJECT P ON PF.FamilyID = P.FamilyID LEFT OUTER JOIN
EMAIL_SIGNUP ES ON P.ProjectID = ES.ProjectID
WHERE ES.WebContactID = 1 OR ES.WebContactID IS NULL
Dan|||No that just lists the all the records in Project, and webcontactid is NULL.
Josh.
"David Portas" wrote:

> Try:
> SELECT ES.webcontactid, P.projectname, P.projectid, PF.familyname
> FROM project_family AS PF
> INNER JOIN project AS P
> ON PF.familyid = P.familyid
> LEFT OUTER JOIN email_signup AS ES
> ON P.projectid = ES.projectid
> AND ES.webcontactid = 1
> --
> David Portas
> SQL Server MVP
> --
>|||Ignore this message. That worked thanks.
Josh.
"jmhmaine" wrote:
> No that just lists the all the records in Project, and webcontactid is NUL
L.
> Josh.
> "David Portas" wrote:
>|||jmhmaine,
For those records in Project which have no matching record in email_signup,
the value of webcontactid MUST BE NULL. There's no way around that... Thos
e
records don't HAVE a webContactID, cause that data item is in the
email_signup Table...
"jmhmaine" wrote:
> No that just lists the all the records in Project, and webcontactid is NUL
L.
> Josh.
> "David Portas" wrote:
>

outer join quesiont, pls help!

I have two tables. One (table1) is look up table that has 48 records for tim
e
interval. They are:
interval
0:00 - 0:30
0:30 - 1:00
1:00 - 1:30
:
:
23:00 - 23:30
23:30 - 24:00
The other table (table2) has real data. It looks like that:
Interval user value
6:00 - 6:30 user1 1
7:00 - 7:30 user1 1
15:00 - 15:30 user2 3
17:00 - 17:30 user2 2
23:00 - 23:30 user3 2
The result I need is
interval user value
00:00 - 00:30 user1 Null (since no data for user1
in this interval)
00:30 - 01:00 user1 Null
:
6:00 - 6:30 user1 1
7:00 - 7:30 user1 1
:
23:00 - 23:30 user1 Null
23:30 - 24:00 user1 Null
I am trying to use left outer join to do it but it only return me:
6:00 - 6:30 user1 1
7:00 - 7:30 user1 1
my query is
select t1.inteval, t2.user, t2.value from table1 t1 left outer join table2
t2 on
t1.interval = t2.itnerval where user='user1'
Can somebody tell me what wrong is with my query. How to modify it to get
the result I need.
Thanks in advance!>> Can somebody tell me what wrong is with my query. How to modify it to get
Most likely moving the predicate in WHERE clause to ON clause will fix it.
Other wise, please read www.aspfaq.com/5006 and post DDL, sample data &
expected results in a usable format so that others can repro your problem
scenario.
Anith|||The problem is the where clause - you basically turn the outer join into an
inner join (not literally, but in effect).
Change it like this:
where (user = 'user1' or user is null)
ML|||you could try:
select t1.inteval, t2.user, t2.value from table1 t1 left outer join table2
t2 on
t1.interval = t2.itnerval AND t2.user='user1'
"Jean" wrote:

> I have two tables. One (table1) is look up table that has 48 records for t
ime
> interval. They are:
> interval
> 0:00 - 0:30
> 0:30 - 1:00
> 1:00 - 1:30
> :
> :
> 23:00 - 23:30
> 23:30 - 24:00
> The other table (table2) has real data. It looks like that:
> Interval user value
> 6:00 - 6:30 user1 1
> 7:00 - 7:30 user1 1
> 15:00 - 15:30 user2 3
> 17:00 - 17:30 user2 2
> 23:00 - 23:30 user3 2
> The result I need is
> interval user value
> 00:00 - 00:30 user1 Null (since no data for user1
> in this interval)
> 00:30 - 01:00 user1 Null
> :
> 6:00 - 6:30 user1 1
> 7:00 - 7:30 user1 1
> :
> 23:00 - 23:30 user1 Null
> 23:30 - 24:00 user1 Null
> I am trying to use left outer join to do it but it only return me:
> 6:00 - 6:30 user1 1
> 7:00 - 7:30 user1 1
> my query is
> select t1.inteval, t2.user, t2.value from table1 t1 left outer join table2
> t2 on
> t1.interval = t2.itnerval where user='user1'
> Can somebody tell me what wrong is with my query. How to modify it to get
> the result I need.
> Thanks in advance!
>|||It works! Thanks to you all!!!

Outer Join qry help!

The first query returns 46 rows (correct result set) while the second one
returns all of the records from app_type table. The second query is similar
(atleast thats what I'm trying to accomplish) to the first one, difference
being non-ansii statement.
Can someone help me figure out whats wrong with the second query?
Thanks.
qry1
select distinct ap.type,ap.subsystem
from app_type ap left outer join app_code ac on ap.type=ac.type
where ac.type is null
qry2
select distinct ap.type, ap.subsystem
from app_type ap , app_code ac
where ap.type*=ac.type
and ac.type is null
Arul,
you just found out why you should not use the depricated outer join
syntax.
Check out this posting, and see if that helps:
http://groups.google.nl/groups?selm=...utput=g plain
Gert-Jan
Arul wrote:
> The first query returns 46 rows (correct result set) while the second one
> returns all of the records from app_type table. The second query is similar
> (atleast thats what I'm trying to accomplish) to the first one, difference
> being non-ansii statement.
> Can someone help me figure out whats wrong with the second query?
> Thanks.
> qry1
> select distinct ap.type,ap.subsystem
> from app_type ap left outer join app_code ac on ap.type=ac.type
> where ac.type is null
> qry2
> select distinct ap.type, ap.subsystem
> from app_type ap , app_code ac
> where ap.type*=ac.type
> and ac.type is null

Outer Join qry help!

The first query returns 46 rows (correct result set) while the second one
returns all of the records from app_type table. The second query is similar
(atleast thats what I'm trying to accomplish) to the first one, difference
being non-ansii statement.
Can someone help me figure out whats wrong with the second query?
Thanks.
qry1
select distinct ap.type,ap.subsystem
from app_type ap left outer join app_code ac on ap.type=ac.type
where ac.type is null
qry2
select distinct ap.type, ap.subsystem
from app_type ap , app_code ac
where ap.type*=ac.type
and ac.type is nullArul,
you just found out why you should not use the depricated outer join
syntax.
Check out this posting, and see if that helps:
[url]http://groups.google.nl/groups?selm=eAtctS7DBHA.968%40tkmsftngp07&output=gplain[/u
rl]
Gert-Jan
Arul wrote:
> The first query returns 46 rows (correct result set) while the second one
> returns all of the records from app_type table. The second query is simil
ar
> (atleast thats what I'm trying to accomplish) to the first one, difference
> being non-ansii statement.
> Can someone help me figure out whats wrong with the second query?
> Thanks.
> qry1
> select distinct ap.type,ap.subsystem
> from app_type ap left outer join app_code ac on ap.type=ac.type
> where ac.type is null
> qry2
> select distinct ap.type, ap.subsystem
> from app_type ap , app_code ac
> where ap.type*=ac.type
> and ac.type is null

Outer Join qry help!

The first query returns 46 rows (correct result set) while the second one
returns all of the records from app_type table. The second query is similar
(atleast thats what I'm trying to accomplish) to the first one, difference
being non-ansii statement.
Can someone help me figure out whats wrong with the second query?
Thanks.
qry1
select distinct ap.type,ap.subsystem
from app_type ap left outer join app_code ac on ap.type=ac.type
where ac.type is null
qry2
select distinct ap.type, ap.subsystem
from app_type ap , app_code ac
where ap.type*=ac.type
and ac.type is nullArul,
you just found out why you should not use the depricated outer join
syntax.
Check out this posting, and see if that helps:
http://groups.google.nl/groups?selm=eAtctS7DBHA.968%40tkmsftngp07&output=gplain
Gert-Jan
Arul wrote:
> The first query returns 46 rows (correct result set) while the second one
> returns all of the records from app_type table. The second query is similar
> (atleast thats what I'm trying to accomplish) to the first one, difference
> being non-ansii statement.
> Can someone help me figure out whats wrong with the second query?
> Thanks.
> qry1
> select distinct ap.type,ap.subsystem
> from app_type ap left outer join app_code ac on ap.type=ac.type
> where ac.type is null
> qry2
> select distinct ap.type, ap.subsystem
> from app_type ap , app_code ac
> where ap.type*=ac.type
> and ac.type is nullsql

Outer Join Help!

The first query below returns 46 rows (correct result set) while the second
one
returns all of the records from app_type table. The second query is similar
(atleast thats what I'm trying to accomplish) to the first one, difference
being non-ansii statement.
Can someone help me figure out whats wrong with the second query?
Thanks.
qry1
select distinct ap.type,ap.subsystem
from app_type ap left outer join app_code ac on ap.type=ac.type
where ac.type is null
qry2
select distinct ap.type, ap.subsystem
from app_type ap , app_code ac
where ap.type*=ac.type
and ac.type is nullPlease do not post the same question independently to multiple newsgroups.
Your original post has already been answered in .server.
"Arul" <Arul@.discussions.microsoft.com> wrote in message
news:68D344E0-2384-4C56-95EE-EDF80DB5869A@.microsoft.com...
> The first query below returns 46 rows (correct result set) while the
second
> one
> returns all of the records from app_type table. The second query is
similar
> (atleast thats what I'm trying to accomplish) to the first one, difference
> being non-ansii statement.
> Can someone help me figure out whats wrong with the second query?
> Thanks.
> qry1
> select distinct ap.type,ap.subsystem
> from app_type ap left outer join app_code ac on ap.type=ac.type
> where ac.type is null
> qry2
> select distinct ap.type, ap.subsystem
> from app_type ap , app_code ac
> where ap.type*=ac.type
> and ac.type is null
>|||Arul,
The second query is not the equivalent of the first. It should be:
http://www.microsoft.com/sql/techin...ment/July23.asp
AMB
"Arul" wrote:

> The first query below returns 46 rows (correct result set) while the secon
d
> one
> returns all of the records from app_type table. The second query is simil
ar
> (atleast thats what I'm trying to accomplish) to the first one, difference
> being non-ansii statement.
> Can someone help me figure out whats wrong with the second query?
> Thanks.
> qry1
> select distinct ap.type,ap.subsystem
> from app_type ap left outer join app_code ac on ap.type=ac.type
> where ac.type is null
> qry2
> select distinct ap.type, ap.subsystem
> from app_type ap , app_code ac
> where ap.type*=ac.type
> and ac.type is null
>|||>> Can someone help me figure out whats wrong with the second query? <<
It is not SQL! There is no such operator as *=. And when it was a
dialect, it never worked right anyway. Why are you using it or are you
trtying migrate old code?

Out of Memery Error while reading records

Hi,

I'm using CR9 with PostgreSQL.

While trying to run a report for a particular date, it returns

"Failed to open rowset"

"Out of memory while trying to read tuples"

But while running the same thing as a sql query from Postgres by limiting the records count it's working fine. So I think the problem is with the crystal reports.

Is there any way to limit the no of records? Also I tried to limit the records in CR RAS. It's not working.

Any idea, pls. help me

ThanksWhat's the maximum size of the table/ Max no of records Crystal Reports Support?

OR

Is it depends on the physical size of the machine.

I caught by the error "Out of Memory while reading tuples". The same table is working while filtering the records by another field which returns smaller no of records.

Have any idea, pls share with me.

Thankssql

OSQL question

Hi I am trying to write a batch file that will give me all records relating to the query.

osql -S sqlserver -d database -U sa -P "" -Q "select docloc from mhgroup.docmaster where docloc like '%1054193%'"

The query is truncating the 1054193 to 054193....

Is there a problem with using LIKE and the % wildcard in an OSQL statement?

Thanks,
Darrin Carterhow do you know it's truncating? did you see it in profiler? i just ran it here (slightly modified) and it runs correctly, profiler shows the exact string.|||Originally posted by ms_sql_dba
how do you know it's truncating? did you see it in profiler? i just ran it here (slightly modified) and it runs correctly, profiler shows the exact string.

I run it from the batch file that I created. In the DOS window it is missing the 1 at the beginning as well as the % signs

This is the actual statement:

osql -S sqlserver -d database -U sa -P "" -Q "select docloc from mhgroup.docmaster where docloc like '%1054193%'"

This is how it shows up in the DOS window.

I:\>osql -S sqlserver -d database -U sa -P "" -I -Q "select docloc from mhgroup.docmaster where docloc like 054193"|||ok, now I get it. %1 is a replacement for the first argument following the batch file name, which is the reason why %1 disappears. I am not sure about the other % sign.|||you probably will have to use DOS escape codes to make %' visible to OSQL.|||Just use two percent signs (eg : %%) to make one visible to DOS

eg your actual statement should be :

osql -S sqlserver -d database -U sa -P "" -Q "select docloc from mhgroup.docmaster where docloc like '%%1054193%%'"|||Originally posted by Enigma
Just use two percent signs (eg : %%) to make one visible to DOS

eg your actual statement should be :

osql -S sqlserver -d database -U sa -P "" -Q "select docloc from mhgroup.docmaster where docloc like '%%1054193%%'"

That did it. Thanks!