outer-join results to cartesian product .... help!

All,

A very happy New Year to you all!!!

I have two tables f and sm

structure for f:

uid int not null,
cbuid int not null,
pid int not null,
gid int not null,
sid int not null,
mnth tinyint not null

structure for sm:

sid int not null,
mnth tinyint not null

contents/rows in table f are:

uid cbid pid gid sid mnth
-- -- -- -- -- --
8 92 10057 4 40 2
8 92 10057 4 40 3
8 92 10057 4 40 4
18 125 10057 4 40 2

contents/rows in table sm are:

sid mnth
-- --
40 2
40 3
40 4
40 5

The requirement is compare (f, sm) and return matching and non-matching
rows.

now the sql:

1)

select f.uid, f.cbid, f.pid, f.gid, f.sid, f.mnth, sm.mnth
from f left join sm on f.sid = sm.sid
where f.Pid = 10057 AND
f.gid = 4 AND f.cbid = '125'

output:

uid cbid pid gid sid
mnth mnth
---- ------- ---- ---- ----
-- --
18 125 10057 4 40
2 5 -> Row retrieved
18 125 10057 4 40
2 4
18 125 10057 4 40
2 3
18 125 10057 4 40
2 2

The above output returns as expected until I change the predicate....
See below:

select f.uid, f.cbid, f.pid, f.gid, f.sid, f.mnth, sm.mnth
from f left join sm on f.sid = sm.sid
where f.Pid = 10057 AND
f.gid = 4 AND f.cbid = '92'

output:

uid cbid pid gid sid
mnth mnth
---- ------- ---- ---- ----
-- --
8 92 10057 4 40
2 5
8 92 10057 4 40
3 5
8 92 10057 4 40
4 5
8 92 10057 4 40
2 4
8 92 10057 4 40
3 4
8 92 10057 4 40
4 4
8 92 10057 4 40
2 3
8 92 10057 4 40
3 3
8 92 10057 4 40
4 3
8 92 10057 4 40
2 2
8 92 10057 4 40
3 2
8 92 10057 4 40
4 2

The above output seems to be cartesian ?

Please help on how to resolve ...

2)

Is there a way where I could have non-matching rows like MINUS in
Oracle... I even tried NOT EXISTS but that did not work...

Any thoughts would be highly appreciated...
Thanks a bunch in advance,
AnuOn 12 Jan 2005 19:24:20 -0800, anuu_radhaa@.yahoo.com wrote:

(snip)
>The above output seems to be cartesian ?
>Please help on how to resolve ...

Hi Anu,

The output appears to be correct. Three rows in table f match the filter
condition in the WHERE clause. Each of these three rows matches the join
condition in the ON clause for all 4 rows in table sm, so you'll get a
result set of (3 x 4 =) 12 rows.

You seem to expect different results, but you didn't specify what the
desired results are and why.

>2)
>Is there a way where I could have non-matching rows like MINUS in
>Oracle... I even tried NOT EXISTS but that did not work...

I don't know Oracle, nor the MINUS operator. Is MINUS the Orcale
implementation of the ANSI-standard EXCEPT operation? Or does it something
else?

Both of your questions can be answered lots better if you provide
a) A SQL script to create your tables (including constraints and indexes,
but excluding irrelevant columns) and fill them with some sample data, and
b) The expected output, along with aan explanation.

Also, read http://www.aspfaq.com/5006.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||2)

You can do it with an outer join. Example:

CREATE TABLE A (x INTEGER PRIMARY KEY)
CREATE TABLE B (x INTEGER PRIMARY KEY)

INSERT INTO A VALUES (1)

SELECT A.x
FROM A
LEFT JOIN B
ON A.x = B.x
WHERE B.x IS NULL

--
David Portas
SQL Server MVP
--|||Thanks all for the info.

Here are the details

CREATE TABLE [FCast] (
[BusinessUnitId] [int] NOT NULL ,
[UserId] [int] NOT NULL ,
[SeasonId] [int] NOT NULL ,
[FMonth] [tinyint] NULL ,
[DivisionId] [int] NOT NULL ,
[ProductId] [int] NOT NULL
)
GO

INSERT INTO [FCast] VALUES ( 92, 8, 40, 2, 4, 10057 )
GO

INSERT INTO [FCast] VALUES ( 92, 8, 40, 3, 4, 10057 )
GO

INSERT INTO [FCast] VALUES ( 92, 8, 40, 4, 4, 10057 )
GO

INSERT INTO [FCast] VALUES ( 125, 18, 40, 2, 4, 10057 )
GO

CREATE TABLE [SMonths] (
[SeasonId] [int] NOT NULL ,
[SMonth] [tinyint] NOT NULL
)

GO

INSERT INTO [SMONTHS] VALUES (40, 2)
GO

INSERT INTO [SMONTHS] VALUES (40, 3)
GO

INSERT INTO [SMONTHS] VALUES (40, 4)
GO

INSERT INTO [SMONTHS] VALUES (40, 5)
GO

one of my colleage happened to delete all those values having 'null'
which caused the problem.

for every month in smonths there would be a row in fcast for a
productid. earlier, the application,
would insert a row into fcast table with month value as 'null'. This
was actually a application bug.
to resolve this, my colleage did took up a hasty decision and wrote a
SQL which really blew up
all the rows in production environment...

the funniest part is, it is almost 3 months after this SQL is executed.

so, database restore is not possible...

hence, thought of writing a SQL which populates the missing rows in
fcast table.

so, now the requirement is to insert the missing rows in fcast table.

the query which i framed works fine for businessunitid = 125 and fails
for businessunitid = 92

the output should be:

userid businessunitid productid divisionid seasonid smonth
-- ----- --- ---- --- --
18 125 10057 4 40 3
18 125 10057 4 40 4
18 125 10057 4 40 5
8 92 10057 4 40 5

this output would then be inserted into fcast table...

any ideas or thoughts would really help...
thanks in advance,

Anu|||On 13 Jan 2005 18:06:42 -0800, anuu wrote:

>Thanks all for the info.
>Here are the details
(snip)

Hi Anu,

Thanks. Unfortunately, therre still are some questions to ask.

1. What are the keys for your tables? For SMonths, either SMonth or
(SeasonId, SMonth) are logical possibilities. For FCast, I can't even
begin to guess.

2. In your example, the input for business unit 125 consists of one row;
the output has three rows, with the "missing" months and the remaining
columns taken from the one row that is present. Fine. For business unit
92, the situation gets muddy: you start withh three rows and want to
create one extra row for the "missing" month, again with the remainig
columns taken from the rows already presen. But which one? In your
example, the three rows for BU 92 all have user 8, division 4 and product
10057. What would be the expected output if the input changes to
INSERT INTO [FCast] VALUES ( 92, 8, 40, 2, 4, 10057 )
INSERT INTO [FCast] VALUES ( 92, 7, 40, 3, 3, 10056 )
INSERT INTO [FCast] VALUES ( 92, 6, 40, 4, 2, 10055 )

3. From your examples, it appears that there always is a row for the
"first" month of the season (month 2), but rows for subsequent months
might be missing. Is this a correct assumption or is your example
incomplete?

Here's some code that will produce the requested output from your sample
data, but relies very heavy on several assumptions. If my assumptions are
wrong, the code will produce incorrect results. I didn't try to optimize
it, as this is probably (hopefully!) a one-time operation.

SELECT f.BusinessUnitId, f.UserId, f.SeasonId,
s.SMonth, f.DivisionId, f.ProductId
FROM FCast AS f
INNER JOIN SMonths AS s
ON s.SeasonId = f.SeasonId
WHERE f.FMonth = (SELECT MIN(s2.SMonth)
FROM SMonths AS s2
WHERE s2.SeasonId = f.SeasonId)
AND NOT EXISTS (SELECT *
FROM FCast AS f2
WHERE f2.BusinessUnitId = f.BusinessUnitId
AND f2.FMonth = s.SMonth)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis wrote:
> On 13 Jan 2005 18:06:42 -0800, anuu wrote:
> >Thanks all for the info.
> >Here are the details
> (snip)
> Hi Anu,
> Thanks. Unfortunately, therre still are some questions to ask.
[Anu]: No issues, Hugo. Ready to answer the questions. Find below
embedded

> 1. What are the keys for your tables? For SMonths, either SMonth or
> (SeasonId, SMonth) are logical possibilities. For FCast, I can't even
> begin to guess.
[Anu]: For SMonths (SeasonId, SMonth) and for FCast (SeasonId, FMonth)
which relates to SMonths

> 2. In your example, the input for business unit 125 consists of one
row;
> the output has three rows, with the "missing" months and the
remaining
> columns taken from the one row that is present. Fine. For business
unit
> 92, the situation gets muddy: you start withh three rows and want to
> create one extra row for the "missing" month, again with the remainig
> columns taken from the rows already presen. But which one? In your
> example, the three rows for BU 92 all have user 8, division 4 and
product
> 10057. What would be the expected output if the input changes to
> INSERT INTO [FCast] VALUES ( 92, 8, 40, 2, 4, 10057 )
> INSERT INTO [FCast] VALUES ( 92, 7, 40, 3, 3, 10056 )
> INSERT INTO [FCast] VALUES ( 92, 6, 40, 4, 2, 10055 )
[Anu]: Fine. If SMonths has these values

SI Mo SI = SeasonId, Mo = Month
---
40, 2
40, 3
40, 4
40, 5

then for the above input below would be output
BU = Business Unit, UI = User Id, SI = Season ID, DI =
Division ID, Mo = Month, PI = Product ID

BU UI SI DI Mo PI
--------
92, 8, 40, 2, 2, 10057
92, 8, 40, 2, 3, 10057
92, 8, 40, 2, 5, 10057

92, 7, 40, 3, 2, 10056
92, 7, 40, 3, 4, 10056
92, 7, 40, 3, 5, 10056

92, 6, 40, 4, 3, 10055
92, 6, 40, 4, 4, 10055
92, 6, 40, 4, 5, 10055

In short, FCast table would have per ProductId, per BU, all the months
available for a season.

> 3. From your examples, it appears that there always is a row for the
> "first" month of the season (month 2), but rows for subsequent months
> might be missing. Is this a correct assumption or is your example
> incomplete?

[Anu]: Nope, the assumption is not correct. For a season, the months
spread would be defined in SMonths
table. So, for example, the SeasonId 40 has 12,1,2,3,4 defined then the
output for the above input (in point 2) would differ. The available
rows in FCast would _be_ the ones defined in SMonths.

> Here's some code that will produce the requested output from your
sample
> data, but relies very heavy on several assumptions. If my assumptions
are
> wrong, the code will produce incorrect results. I didn't try to
optimize
> it, as this is probably (hopefully!) a one-time operation.
> SELECT f.BusinessUnitId, f.UserId, f.SeasonId,
> s.SMonth, f.DivisionId, f.ProductId
> FROM FCast AS f
> INNER JOIN SMonths AS s
> ON s.SeasonId = f.SeasonId
> WHERE f.FMonth = (SELECT MIN(s2.SMonth)
> FROM SMonths AS s2
> WHERE s2.SeasonId = f.SeasonId)
> AND NOT EXISTS (SELECT *
> FROM FCast AS f2
> WHERE f2.BusinessUnitId = f.BusinessUnitId
> AND f2.FMonth = s.SMonth)
[Anu]: Thanks, Hugo. I would start working on this and see if I could
accomplish. Meanwhile, let me know if you need more info....

> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On 14 Jan 2005 17:10:05 -0800, anuu wrote:

Hi Anu,

(snip)
>> 1. What are the keys for your tables? For SMonths, either SMonth or
>> (SeasonId, SMonth) are logical possibilities. For FCast, I can't even
>> begin to guess.
>>
>[Anu]: For SMonths (SeasonId, SMonth) and for FCast (SeasonId, FMonth)
>which relates to SMonths

Huh? The sample data you posted in your original post in this thread
violates the key you state for FCast - it has two rows for SeasonId 40,
FMonth 2, which would not be possible with the key you state above!

>> 2. In your example, the input for business unit 125 consists of one
>row;
>> the output has three rows, with the "missing" months and the
>remaining
>> columns taken from the one row that is present. Fine. For business
>unit
>> 92, the situation gets muddy: you start withh three rows and want to
>> create one extra row for the "missing" month, again with the remainig
>> columns taken from the rows already presen. But which one? In your
>> example, the three rows for BU 92 all have user 8, division 4 and
>product
>> 10057. What would be the expected output if the input changes to
>> INSERT INTO [FCast] VALUES ( 92, 8, 40, 2, 4, 10057 )
>> INSERT INTO [FCast] VALUES ( 92, 7, 40, 3, 3, 10056 )
>> INSERT INTO [FCast] VALUES ( 92, 6, 40, 4, 2, 10055 )
>>
>[Anu]: Fine. If SMonths has these values
>SI Mo SI = SeasonId, Mo = Month
>---
>40, 2
>40, 3
>40, 4
>40, 5
>
>then for the above input below would be output
>BU = Business Unit, UI = User Id, SI = Season ID, DI =
>Division ID, Mo = Month, PI = Product ID
>BU UI SI DI Mo PI
>--------
>92, 8, 40, 2, 2, 10057
>92, 8, 40, 2, 3, 10057
>92, 8, 40, 2, 5, 10057
>92, 7, 40, 3, 2, 10056
>92, 7, 40, 3, 4, 10056
>92, 7, 40, 3, 5, 10056
>92, 6, 40, 4, 3, 10055
>92, 6, 40, 4, 4, 10055
>92, 6, 40, 4, 5, 10055
>In short, FCast table would have per ProductId, per BU, all the months
>available for a season.

Again: huh? This data would never be accepted in the table if the primary
key for FCast is (SeasonID, FMonth), as you state above. So I guess that's
not the primary key after all.

Also, in a previous post you wrote "for every month in smonths there would
be a row in fcast for a productid". Now, you write that you need to have a
row for every month "per ProductId, per BU". Not exactly the same, right?

I guess I could now make a new guess at the primary key in FCast, then
change the code I posted before to reflect my new guess. But there would
still be a lot of uncertainty. So instead of wasting time on writing a new
query on insufficient specs, I'll now refer you to www.aspfaq.com/5006,
where you will find instructions on how to assemble the details you should
post here to get help, in the best format for this group: SQL.

Also, please tell me the expected output if the input looks like this:

BU UI SI DI Mo PI
--------
92, 8, 40, 2, 2, 10057
92, 7, 40, 2, 3, 10057
92, 7, 40, 3, 5, 10057

From your description above, I guess there should be one extra row, for BU
92, PPI 10057, SI 40 and Mo 4 - but what should be the values for UI and
DI?

>> 3. From your examples, it appears that there always is a row for the
>> "first" month of the season (month 2), but rows for subsequent months
>> might be missing. Is this a correct assumption or is your example
>> incomplete?
>>
>[Anu]: Nope, the assumption is not correct. For a season, the months
>spread would be defined in SMonths
>table. So, for example, the SeasonId 40 has 12,1,2,3,4 defined then the
>output for the above input (in point 2) would differ. The available
>rows in FCast would _be_ the ones defined in SMonths.

And if the SeasonId 40 has months 12, 1, 2, 3, and 4, would there than be
any months that is "complete", such as month 2 was "complete" in your
original sample data?
Please post better sample data (as INSERT statements - see the link I
supplied above), indicating all possible situations. The "garbage in,
garbage out" principle applies in this group as much as anywhere else!

>[Anu]: Thanks, Hugo. I would start working on this and see if I could
>accomplish. Meanwhile, let me know if you need more info....

I don't "need" more info. But if could probably help you better if you
provided more info...

If you need more help, then please provide table structure (as CREATE
TABLE statements, including constrainst but excluding irrelevant columns),
sample data (as INSERT statements) and expected output. In case you missed
the link above: see www.aspfaq.com/5006.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Anu,

(snip)

>> 1. What are the keys for your tables? For SMonths, either SMonth or
>> (SeasonId, SMonth) are logical possibilities. For FCast, I can't
even
>> begin to guess.

>[Anu]: For SMonths (SeasonId, SMonth) and for FCast (SeasonId, FMonth)
>which relates to SMonths

Huh? The sample data you posted in your original post in this thread
violates the key you state for FCast - it has two rows for SeasonId 40,
FMonth 2, which would not be possible with the key you state above!

[Anu_Again] Hugo, the key mentioned is Foreign keys and not Primary.

- Hide quoted text -
- Show quoted text -

>> 2. In your example, the input for business unit 125 consists of one
>row;
>> the output has three rows, with the "missing" months and the
>remaining
>> columns taken from the one row that is present. Fine. For business
>unit
>> 92, the situation gets muddy: you start withh three rows and want to
>> create one extra row for the "missing" month, again with the
remainig
>> columns taken from the rows already presen. But which one? In your
>> example, the three rows for BU 92 all have user 8, division 4 and
>product
>> 10057. What would be the expected output if the input changes to

>> INSERT INTO [FCast] VALUES ( 92, 8, 40, 2, 4, 10057 )
>> INSERT INTO [FCast] VALUES ( 92, 7, 40, 3, 3, 10056 )
>> INSERT INTO [FCast] VALUES ( 92, 6, 40, 4, 2, 10055 )
>[Anu]: Fine. If SMonths has these values

>SI Mo SI = SeasonId, Mo = Month

>---
>40, 2
>40, 3
>40, 4
>40, 5

>then for the above input below would be output

>BU = Business Unit, UI = User Id, SI = Season ID, DI =
>Division ID, Mo = Month, PI = Product ID

>BU UI SI DI Mo PI

>--------
>92, 8, 40, 2, 2, 10057
>92, 8, 40, 2, 3, 10057
>92, 8, 40, 2, 5, 10057

>92, 7, 40, 3, 2, 10056
>92, 7, 40, 3, 4, 10056
>92, 7, 40, 3, 5, 10056

>92, 6, 40, 4, 3, 10055
>92, 6, 40, 4, 4, 10055
>92, 6, 40, 4, 5, 10055

>In short, FCast table would have per ProductId, per BU, all the months
>available for a season.

Again: huh? This data would never be accepted in the table if the
primary
key for FCast is (SeasonID, FMonth), as you state above. So I guess
that's
not the primary key after all.

Also, in a previous post you wrote "

for every month in smonths there would
be a row in fcast for a productid

". Now, you write that you need to have a
row for every month "per ProductId, per BU". Not exactly the same,
right?

[Anu_again]: Hugo, it is foreign key and not primary key. primary key
is an identity column which I did not incude
in the structure as I thought that will not make any difference.
Nope, it is same. all these ProductID, BU are all foreign keys in FCast
table. the primary key
is only an identity column.

I guess I could now make a new guess at the primary key in FCast, then
change the code I posted before to reflect my new guess. But there
would
still be a lot of uncertainty. So instead of wasting time on writing a
new
query on insufficient specs, I'll now refer you to www.aspfaq.com/5006,
where you will find instructions on how to assemble the details you
should
post here to get help, in the best format for this group: SQL.

[Anu_again]: No guesses.....

Also, please tell me the expected output if the input looks like this:

BU UI SI DI Mo PI

--------
92, 8, 40, 2, 2, 10057
92, 7, 40, 2, 3, 10057
92, 7, 40, 3, 5, 10057

>From your description above, I guess there should be one extra row, for
BU
92, PPI 10057, SI 40 and Mo 4 - but what should be the values for UI
and
DI?

[Anu_again] : should be 92, 7, 40, 3, 4, 10057. You are right

>> 3. From your examples, it appears that there always is a row for the
>> "first" month of the season (month 2), but rows for subsequent
months
>> might be missing. Is this a correct assumption or is your example
>> incomplete?

>[Anu]: Nope, the assumption is not correct. For a season, the months
>spread would be defined in SMonths
>table. So, for example, the SeasonId 40 has 12,1,2,3,4 defined then
the
>output for the above input (in point 2) would differ. The available
>rows in FCast would _be_ the ones defined in SMonths.

And if the SeasonId 40 has months 12, 1, 2, 3, and 4, would there than
be
any months that is "complete", such as month 2 was "complete" in your
original sample data?
[Anu_again]: Complete ? The available months in FCast table are
considered as complete and the ones
not are to be INSERTed

Please post better sample data (as INSERT statements - see the link I
supplied above), indicating all possible situations. The "garbage in,
garbage out" principle applies in this group as much as anywhere else!

[Anu_again]: I feel, I did not communicate properly and this caused the
confusion otherwise you are in
the right track.

>[Anu]: Thanks, Hugo. I would start working on this and see if I could
>accomplish. Meanwhile, let me know if you need more info....

I don't "need" more info. But if could probably help you better if you
provided more info...

If you need more help, then please provide table structure (as CREATE
TABLE statements, including constrainst but excluding irrelevant
columns),
sample data (as INSERT statements) and expected output. In case you
missed
the link above: see

www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address|||On 16 Jan 2005 16:25:23 -0800, anuu wrote:

(snip)
>[Anu_again]: Hugo, it is foreign key and not primary key. primary key
>is an identity column which I did not incude
>in the structure as I thought that will not make any difference.

Hi Anu,

You're right, knowing that the primary key is an identity column doesn't
help me to help you. But knowing the natural key of your data would have
helped. I assume that you do know the difference between an artificial key
(identity) and a natural key? I also assume that you are aware than even
if you use an identity column as primary key, the natural key should still
be declared (using a UNIQEU constraint)?

If you had posted your table structure and illustrative sample data, as I
requested in my previous message, then I would now be able to see the
PRIMARY KEY constraint on the identity column, as well as the UNIQUE
constraint on whatever combination of columns makes up the natural key for
this table. This is information I really *need* in order to write a query
that returns the rows you need.

(snip)
>>Also, please tell me the expected output if the input looks like this:
>>
>>
>>BU UI SI DI Mo PI
>>
>>
>>--------
>>92, 8, 40, 2, 2, 10057
>>92, 7, 40, 2, 3, 10057
>>92, 7, 40, 3, 5, 10057
>>
>>>From your description above, I guess there should be one extra row, for
>>BU
>>92, PPI 10057, SI 40 and Mo 4 - but what should be the values for UI
>>and
>>DI?
>[Anu_again] : should be 92, 7, 40, 3, 4, 10057. You are right

While I still don't know the natural key of your table, your answer
supports my hunch that the natural key is the combination of (business
unit, productid, seasonid, month).
On the other hand, your answer also raises some questions. WHY should the
user id in the extra row be 7 (as in the rows for march and may), not 8
(as in the row for february)? And why should the division in the extra row
be 3 (as in the row for may), not 2 (as in the rows for february and
march)? This part of the specifications is still unclear!

(snip)
>>Please post better sample data (as INSERT statements - see the link I
>>supplied above), indicating all possible situations. The "garbage in,
>>garbage out" principle applies in this group as much as anywhere else!
>[Anu_again]: I feel, I did not communicate properly and this caused the
>confusion otherwise you are in
>the right track.

You're right. The proper way to communicate in this group, is to post your
table structure as CREATE TABLE statements, including all constraints and
properties, some illustrative sample data as INSERT statements and the
output expected from that sample data.
You did post a partial table structure in an earlier post, but you didn't
include the constraints. You also posted some sample data, but it was not
illustrative of your problem, so the query I wrote and tested against that
set of sample data will probably not be of much use.

If you still need assistance, I strongly urge you (again!) to read the
information at http://www.aspfaq.com/etiquette.asp?id=5006 and follow
those instructions to post the information and specifications that are
required to get a good working solution to your problem.
Without clear specifications, table structure and good sample data, I
really don't think I can help you.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Outer Joins

I am trying to create an outer join between two tables in a query that
includes several other tables.

When I double-click on the Join line, it presents three join options:

1) ONLY records from table1 and table2 where join fields are equal
2) ALL values from table1 and ONLY records from table2 where join
fields are equal
3) ALL values from table2 and ONLY records from table1 where join
fields are equal

In my case, I want option 2 - all values from table1, and if there is
no match to table2, I want a blank to appear in the output.

When I select this option, I get the following error:

"Can't have outer joins if there are more than two tables in the
query."

How can I get around this, since there are other tables in my query?

Thanks.

Dennis Hancy
Eaton Corporation
Cleveland, OHThis sounds like a limitation of the GUI application you are using to create
the query. Use Query Analyzer instead. Perhaps you can paste your SQL query
into QA and edit it.

Alternatively, if you are creating a view, Enterprise Manager's view
designer doesn't suffer from this particular restriction but it does have
other limitations so Query Analyzer is probably the best choice if you're
willing and able to write the query yourself.

--
David Portas
SQL Server MVP
--

OUTER JOIN with multiple tables and a plus sign?

I am trying to select specific columns from multiple tables based on a
common identifier found in each table.

For example, the three tables:

PUBACC_AC
PUBACC_AM
PUBACC_AN

each have a common column:

PUBACC_AC.unique_system_identifier
PUBACC_AM.unique_system_identifier
PUBACC_AN.unique_system_identifier

What I am trying to select, for example:

PUBACC_AC.name
PUBACC_AM.phone_number
PUBACC_AN.zip

where the TABLE.unique_system_identifier is common.

For example:

--------------
PUBACC_AC
=========
unique_system_identifier name
1234 JONES

--------------
PUBACC_AM
=========
unique_system_identifier phone_number
1234 555-1212

--------------
PUBACC_AN
=========
unique_system_identifier zip
1234 90210

When I run my query, I would like to see the following returned as one
blob, rather than the separate tables:

--------------------
unique_system_identifier name phone_number zip
1234 JONES 555-1212 90210
--------------------

I think this is an OUTER JOIN? I see examples on the net using a plus
sign, with mention of Oracle. I'm not running Oracle...I am using
Microsoft SQL Server 2000.

Help, please?

P. S. Will this work with several tables? I actually have about 15
tables in this mess, but I tried to keep it simple (!??!) for the above
example.

Thanks in advance for your help!

NOTE: TO REPLY VIA E-MAIL, PLEASE REMOVE THE "DELETE_THIS" FROM MY E-MAIL
ADDRESS.

Who actually BUYS the cr@.p that the spammers advertise, anyhow?!!!
(Rhetorical question only.)You do the following:

SELECT PUBACC_AC.Name + PUBACC_AM.Phone_number + PUBACC_AN.zip
FROM PUBACC_AC
INNER JOIN PUBACC_AM
ON PUBACC_AM.unique_col = PUBACC_AC.unique_col
INNER JOIN PUBACC_AN
ON PUBACC_AN.unique_col = PUBACC_AC.unique_col

That's it - you do inner join, if you want only those records, for which
unique_col value exists in all 3 tables.
Or, if you replace "INNER JOIN" with "FULL JOIN", which is the same as OUTER
JOIN in Oracle, you will get
as meny records as number of unique_col values.

Thats it!

Hope it helped,
Andrey aka Muzzy

"TeleTech1212" <tele_tech1212DELETE_THIS@.yahoo.com> wrote in message
news:Xns9556E99BEE9B4teletech1212DELETETH@.207.115. 63.158...
> I am trying to select specific columns from multiple tables based on a
> common identifier found in each table.
> For example, the three tables:
> PUBACC_AC
> PUBACC_AM
> PUBACC_AN
> each have a common column:
> PUBACC_AC.unique_system_identifier
> PUBACC_AM.unique_system_identifier
> PUBACC_AN.unique_system_identifier
>
> What I am trying to select, for example:
> PUBACC_AC.name
> PUBACC_AM.phone_number
> PUBACC_AN.zip
> where the TABLE.unique_system_identifier is common.
>
> For example:
> --------------
> PUBACC_AC
> =========
> unique_system_identifier name
> 1234 JONES
> --------------
> PUBACC_AM
> =========
> unique_system_identifier phone_number
> 1234 555-1212
> --------------
> PUBACC_AN
> =========
> unique_system_identifier zip
> 1234 90210
>
> When I run my query, I would like to see the following returned as one
> blob, rather than the separate tables:
> --------------------
> unique_system_identifier name phone_number zip
> 1234 JONES 555-1212 90210
> --------------------
>
> I think this is an OUTER JOIN? I see examples on the net using a plus
> sign, with mention of Oracle. I'm not running Oracle...I am using
> Microsoft SQL Server 2000.
> Help, please?
> P. S. Will this work with several tables? I actually have about 15
> tables in this mess, but I tried to keep it simple (!??!) for the above
> example.
> Thanks in advance for your help!
> NOTE: TO REPLY VIA E-MAIL, PLEASE REMOVE THE "DELETE_THIS" FROM MY E-MAIL
> ADDRESS.
> Who actually BUYS the cr@.p that the spammers advertise, anyhow?!!!
> (Rhetorical question only.)

Outer Join with 3 tables

Hope this is as easy as I think, but I am struggling to find answer in BOL,
etc.
I have 3 simple tables and want to link them on the same field, "ProductID".
The first table has all productid's on open SalesOrders and the qty sold.
The second table has productid's in Inventory for OnHand quanitity,
and a third table has productid's on PurchaseOrders for PurchaseOrder Qty.
I need to make sure all of the records in table one (SalesOrders) are
included regardless of the data in the other two tables. Additionally I wan
t
to return ONLY the ProductId's from the first table. LeftOuter Join isn't
working for me.
Obviously I am a newbie, and it's probably a dumb question, but you guys
always have the right answer really fast...Chuck,
I think that is equivalent to:
select ProductID from SalesOrders
AMB
"Chuck" wrote:

> Hope this is as easy as I think, but I am struggling to find answer in BOL
,
> etc.
> I have 3 simple tables and want to link them on the same field, "ProductID
".
> The first table has all productid's on open SalesOrders and the qty sold.
> The second table has productid's in Inventory for OnHand quanitity,
> and a third table has productid's on PurchaseOrders for PurchaseOrder Qty.
> I need to make sure all of the records in table one (SalesOrders) are
> included regardless of the data in the other two tables. Additionally I w
ant
> to return ONLY the ProductId's from the first table. LeftOuter Join isn't
> working for me.
> Obviously I am a newbie, and it's probably a dumb question, but you guys
> always have the right answer really fast...|||Sorry I was vague. I would like to return...
ProductID.SalesOrders
QtyOrdered.SalesOrders
QtyOnHand.Inventory
QtyOnPO.PurchaseOrders
returning all of the ProductID's and QtyOrdered in SalesOrders
and the quantities in Inventory and PurchaseOrders regardless of whether
that particular ProductID exists in that table (returning a null value if it
doesn't exist I presume).
In other words, two left outer joins to SalesOrders.
"Alejandro Mesa" wrote:
> Chuck,
> I think that is equivalent to:
> select ProductID from SalesOrders
>
> AMB
> "Chuck" wrote:
>|||Try,
select
so.ProductID,
so.QtyOrdered,
i.QtyOnHand,
po.QtyOnPO
from
SalesOrders as so
left join
Inventory as i
on so.ProductID = i.ProductID
left join
PurchaseOrders as po
on so.ProductID = po.ProductID
AMB
"Chuck" wrote:
> Sorry I was vague. I would like to return...
> ProductID.SalesOrders
> QtyOrdered.SalesOrders
> QtyOnHand.Inventory
> QtyOnPO.PurchaseOrders
> returning all of the ProductID's and QtyOrdered in SalesOrders
> and the quantities in Inventory and PurchaseOrders regardless of whether
> that particular ProductID exists in that table (returning a null value if
it
> doesn't exist I presume).
> In other words, two left outer joins to SalesOrders.
> "Alejandro Mesa" wrote:
>|||Thanks, That worked perfectly. For some reason I was getting an error when
attempting to create two outer joins in one query. Wonder why?
"Alejandro Mesa" wrote:
> Try,
> select
> so.ProductID,
> so.QtyOrdered,
> i.QtyOnHand,
> po.QtyOnPO
> from
> SalesOrders as so
> left join
> Inventory as i
> on so.ProductID = i.ProductID
> left join
> PurchaseOrders as po
> on so.ProductID = po.ProductID
>
> AMB
> "Chuck" wrote:
>sql

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 Syntax Problems (Multiple Tables)

Hello all--
I'm trying to run a SELECT on 3 tables:Class,Enrolled,Waiting.
I want to select the name of the class, the count of the students enrolled, and the count of the students waiting to enroll.
My current query...
SELECT Class.Name, COUNT(Enrolled.StudentID) AS EnrolledCount, COUNT(Waiting.StudentID) AS WaitingCount
FROM Class LEFT OUTER JOIN
Enrolled ON Class.ClassID = Enrolled.ClassID LEFT OUTER JOIN
Waiting ON Class.ClassID = Waiting.ClassID
GROUP BY Class.Name
...results in identical counts for enrolled and waiting, which I knowto be incorrect. Furthermore, it appears that the counts are beingmultiplied together (in one instance, enrolled should be 14, waitingshould be 2, but both numbers come back as 28).
If I run this query without one of the joined tables, the counts areaccurate. The problem only occurs when I try to pull counts from boththe tables.
Can anyone find the problem with my query? Should I be using something other than a LEFT OUTER JOIN?
Thanks very much for your time,
--Jeremy
Run this query and you'll see what it's doing:
SELECT Class.Name, Enrolled.StudentID AS EnrolledCount, Waiting.StudentID AS WaitingCount
FROM Class LEFT OUTER JOIN
Enrolled ON Class.ClassID = Enrolled.ClassID LEFT OUTER JOIN
Waiting ON Class.ClassID = Waiting.ClassID
Something like this will work:
SELECT c.Name, e.EnrolledCount, w.WaitingCount
FROM Class c
LEFT OUTER JOIN
(select classid, COUNT(*) AS EnrolledCount
from Enrolled
group by classid) e
on c.classid = e.classid
LEFT OUTER JOIN
(select classid, COUNT(*) AS WaitingCount
from Waiting
group by classid) w
on c.classid = w.classid
There's other ways to do it with subqueries. Something like this would also work
select c.classname, (select count(*) from enrolled where classid = c.classid) as Enrolled, (select count(*) from waiting where classid = c.classid) as Waiting
from Class c|||Thanks, PDraigh. I used your subquery example and it worked great.
Thanks!
--Jeremy

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 Problem - hardest query ever?

Hi - I'm struggling with a query, which is as follows.
(I have changed the context slightly for simplicity)

I have 4 tables: users, scores, trials, tests
Each pair of users takes a series of upto 4 tests in 1 trial, getting a score for each test.
There are a different numbers of trials for each pair of users.

In detail the tables are:
Users - userid(primary,int), name(varchar)
Scores - scoreid(primary,int), userid(int), trialid(int), userid(int), testid(int), score(int)
Trials - trialid(primary,int), attempt(int), location(varchar)
Tests - testid(primary,int), testname(varchar)

Important: Users do not take all tests.
EG TrialId 1 contains userA & userB with userA scoring 10 on test1, 20 on test2 and userB scoring 30 on test2, 40 on test3, 50 on test4 and is userA & userB's 1st attempt.
TrialId 2 may be the same, but their 2nd attempt.
TrialId 3 may be the 1st attempt for 2 different users etc.

Suppose the Tests table has 4 tests (1,test1),(2,test2),(3,test3),(4,test4)

There are always 2 users for each trial id.

I want a query which will return all scores for all users for all trials, BUT must include NULLs if a user did not take a test on that trial.

I thought it may involve a cross join between the Tests table and the Trials table.

Any help greatly appreciated.this cannot be correct --

Scores - scoreid(primary,int), userid(int), trialid(int), userid(int), testid(int), score(int)

you cannot have two columns in the same table with the same name

Outer Join Problem - hardest query ever?

Hi - I'm struggling with a query, which is as follows.
(I have changed the context slightly for simplicity)
I have 4 tables: users, scores, trials, tests
Each pair of users takes a series of upto 4 tests in 1 trial, getting a
score for each test.
There are a different numbers of trials for each pair of users.
In detail the tables are:
Users - userid(primary,int), name(varchar)
Scores - scoreid(primary,int), userid(int), trialid(int), userid(int),
testid(int), score(int)
Trials - trialid(primary,int), attempt(int), location(varchar)
Tests - testid(primary,int), testname(varchar)
Important: Users do not take all tests.
EG TrialId 1 contains userA & userB with userA scoring 10 on test1, 20 on
test2 and userB scoring 30 on test2, 40 on test3, 50 on test4 and is userA &
userB's 1st attempt.
TrialId 2 may be the same, but their 2nd attempt.
TrialId 3 may be the 1st attempt for 2 different users etc.
Suppose the Tests table has 4 tests (1,test1),(2,test2),(3,test3),(4,test4)
There are always 2 users for each trial id.
I want a query which will return all scores for all users for all trials,
BUT must include NULLs if a user did not take a test on that trial.
I thought it may involve a cross join between the Tests table and the Trials
table.
Any help greatly appreciated.
If you post your DDL and sample data, we can test a solution.
http://www.aspfaq.com/etiquette.asp?id=5006
When you post your DDL and sample data, I'll have a go at it.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"BlackKnight" <BlackKnight@.discussions.microsoft.com> wrote in message
news:980AE47D-8CA7-42A1-BFAC-1139F550DD76@.microsoft.com...
Hi - I'm struggling with a query, which is as follows.
(I have changed the context slightly for simplicity)
I have 4 tables: users, scores, trials, tests
Each pair of users takes a series of upto 4 tests in 1 trial, getting a
score for each test.
There are a different numbers of trials for each pair of users.
In detail the tables are:
Users - userid(primary,int), name(varchar)
Scores - scoreid(primary,int), userid(int), trialid(int), userid(int),
testid(int), score(int)
Trials - trialid(primary,int), attempt(int), location(varchar)
Tests - testid(primary,int), testname(varchar)
Important: Users do not take all tests.
EG TrialId 1 contains userA & userB with userA scoring 10 on test1, 20 on
test2 and userB scoring 30 on test2, 40 on test3, 50 on test4 and is userA &
userB's 1st attempt.
TrialId 2 may be the same, but their 2nd attempt.
TrialId 3 may be the 1st attempt for 2 different users etc.
Suppose the Tests table has 4 tests (1,test1),(2,test2),(3,test3),(4,test4)
There are always 2 users for each trial id.
I want a query which will return all scores for all users for all trials,
BUT must include NULLs if a user did not take a test on that trial.
I thought it may involve a cross join between the Tests table and the Trials
table.
Any help greatly appreciated.
|||On May 3, 4:19 pm, BlackKnight <BlackKni...@.discussions.microsoft.com>
wrote:
> Hi - I'm struggling with a query, which is as follows.
> (I have changed the context slightly for simplicity)
> I have 4 tables: users, scores, trials, tests
> Each pair of users takes a series of upto 4 tests in 1 trial, getting a
> score for each test.
> There are a different numbers of trials for each pair of users.
> In detail the tables are:
> Users - userid(primary,int), name(varchar)
> Scores - scoreid(primary,int), userid(int), trialid(int), userid(int),
> testid(int), score(int)
> Trials - trialid(primary,int), attempt(int), location(varchar)
> Tests - testid(primary,int), testname(varchar)
> Important: Users do not take all tests.
> EG TrialId 1 contains userA & userB with userA scoring 10 on test1, 20 on
> test2 and userB scoring 30 on test2, 40 on test3, 50 on test4 and is userA &
> userB's 1st attempt.
> TrialId 2 may be the same, but their 2nd attempt.
> TrialId 3 may be the 1st attempt for 2 different users etc.
> Suppose the Tests table has 4 tests (1,test1),(2,test2),(3,test3),(4,test4)
> There are always 2 users for each trial id.
> I want a query which will return all scores for all users for all trials,
> BUT must include NULLs if a user did not take a test on that trial.
> I thought it may involve a cross join between the Tests table and the Trials
> table.
> Any help greatly appreciated.
I think you are looking for this . Not tested
Select a.name,a.userid,a.testid,a.testname,
b.score,c.attempt,c.location
from
( select userid,name from users
cross join
select testid,testname from tests ) as a
left outer join scores b
on a.userid = b.userid
and a.testid = b.testid
left outer join trials c
on b.trialid = c.trialid

Outer Join Problem - hardest query ever?

Hi - I'm struggling with a query, which is as follows.
(I have changed the context slightly for simplicity)
I have 4 tables: users, scores, trials, tests
Each pair of users takes a series of upto 4 tests in 1 trial, getting a
score for each test.
There are a different numbers of trials for each pair of users.
In detail the tables are:
Users - userid(primary,int), name(varchar)
Scores - scoreid(primary,int), userid(int), trialid(int), userid(int),
testid(int), score(int)
Trials - trialid(primary,int), attempt(int), location(varchar)
Tests - testid(primary,int), testname(varchar)
Important: Users do not take all tests.
EG TrialId 1 contains userA & userB with userA scoring 10 on test1, 20 on
test2 and userB scoring 30 on test2, 40 on test3, 50 on test4 and is userA &
userB's 1st attempt.
TrialId 2 may be the same, but their 2nd attempt.
TrialId 3 may be the 1st attempt for 2 different users etc.
Suppose the Tests table has 4 tests (1,test1),(2,test2),(3,test3),(4,test4)
There are always 2 users for each trial id.
I want a query which will return all scores for all users for all trials,
BUT must include NULLs if a user did not take a test on that trial.
I thought it may involve a cross join between the Tests table and the Trials
table.
Any help greatly appreciated.If you post your DDL and sample data, we can test a solution.
http://www.aspfaq.com/etiquette.asp?id=5006
When you post your DDL and sample data, I'll have a go at it.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"BlackKnight" <BlackKnight@.discussions.microsoft.com> wrote in message
news:980AE47D-8CA7-42A1-BFAC-1139F550DD76@.microsoft.com...
Hi - I'm struggling with a query, which is as follows.
(I have changed the context slightly for simplicity)
I have 4 tables: users, scores, trials, tests
Each pair of users takes a series of upto 4 tests in 1 trial, getting a
score for each test.
There are a different numbers of trials for each pair of users.
In detail the tables are:
Users - userid(primary,int), name(varchar)
Scores - scoreid(primary,int), userid(int), trialid(int), userid(int),
testid(int), score(int)
Trials - trialid(primary,int), attempt(int), location(varchar)
Tests - testid(primary,int), testname(varchar)
Important: Users do not take all tests.
EG TrialId 1 contains userA & userB with userA scoring 10 on test1, 20 on
test2 and userB scoring 30 on test2, 40 on test3, 50 on test4 and is userA &
userB's 1st attempt.
TrialId 2 may be the same, but their 2nd attempt.
TrialId 3 may be the 1st attempt for 2 different users etc.
Suppose the Tests table has 4 tests (1,test1),(2,test2),(3,test3),(4,test4)
There are always 2 users for each trial id.
I want a query which will return all scores for all users for all trials,
BUT must include NULLs if a user did not take a test on that trial.
I thought it may involve a cross join between the Tests table and the Trials
table.
Any help greatly appreciated.|||On May 3, 4:19 pm, BlackKnight <BlackKni...@.discussions.microsoft.com>
wrote:
> Hi - I'm struggling with a query, which is as follows.
> (I have changed the context slightly for simplicity)
> I have 4 tables: users, scores, trials, tests
> Each pair of users takes a series of upto 4 tests in 1 trial, getting a
> score for each test.
> There are a different numbers of trials for each pair of users.
> In detail the tables are:
> Users - userid(primary,int), name(varchar)
> Scores - scoreid(primary,int), userid(int), trialid(int), userid(int),
> testid(int), score(int)
> Trials - trialid(primary,int), attempt(int), location(varchar)
> Tests - testid(primary,int), testname(varchar)
> Important: Users do not take all tests.
> EG TrialId 1 contains userA & userB with userA scoring 10 on test1, 20 on
> test2 and userB scoring 30 on test2, 40 on test3, 50 on test4 and is userA &
> userB's 1st attempt.
> TrialId 2 may be the same, but their 2nd attempt.
> TrialId 3 may be the 1st attempt for 2 different users etc.
> Suppose the Tests table has 4 tests (1,test1),(2,test2),(3,test3),(4,test4)
> There are always 2 users for each trial id.
> I want a query which will return all scores for all users for all trials,
> BUT must include NULLs if a user did not take a test on that trial.
> I thought it may involve a cross join between the Tests table and the Trials
> table.
> Any help greatly appreciated.
I think you are looking for this . Not tested
Select a.name,a.userid,a.testid,a.testname,
b.score,c.attempt,c.location
from
( select userid,name from users
cross join
select testid,testname from tests ) as a
left outer join scores b
on a.userid = b.userid
and a.testid = b.testid
left outer join trials c
on b.trialid = c.trialidsql

Outer Join Problem - hardest query ever?

Hi - I'm struggling with a query, which is as follows.
(I have changed the context slightly for simplicity)
I have 4 tables: users, scores, trials, tests
Each pair of users takes a series of upto 4 tests in 1 trial, getting a
score for each test.
There are a different numbers of trials for each pair of users.
In detail the tables are:
Users - userid(primary,int), name(varchar)
Scores - scoreid(primary,int), userid(int), trialid(int), userid(int),
testid(int), score(int)
Trials - trialid(primary,int), attempt(int), location(varchar)
Tests - testid(primary,int), testname(varchar)
Important: Users do not take all tests.
EG TrialId 1 contains userA & userB with userA scoring 10 on test1, 20 on
test2 and userB scoring 30 on test2, 40 on test3, 50 on test4 and is userA &
userB's 1st attempt.
TrialId 2 may be the same, but their 2nd attempt.
TrialId 3 may be the 1st attempt for 2 different users etc.
Suppose the Tests table has 4 tests (1,test1),(2,test2),(3,test3),(4,test4)
There are always 2 users for each trial id.
I want a query which will return all scores for all users for all trials,
BUT must include NULLs if a user did not take a test on that trial.
I thought it may involve a cross join between the Tests table and the Trials
table.
Any help greatly appreciated.If you post your DDL and sample data, we can test a solution.
http://www.aspfaq.com/etiquette.asp?id=5006
When you post your DDL and sample data, I'll have a go at it.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"BlackKnight" <BlackKnight@.discussions.microsoft.com> wrote in message
news:980AE47D-8CA7-42A1-BFAC-1139F550DD76@.microsoft.com...
Hi - I'm struggling with a query, which is as follows.
(I have changed the context slightly for simplicity)
I have 4 tables: users, scores, trials, tests
Each pair of users takes a series of upto 4 tests in 1 trial, getting a
score for each test.
There are a different numbers of trials for each pair of users.
In detail the tables are:
Users - userid(primary,int), name(varchar)
Scores - scoreid(primary,int), userid(int), trialid(int), userid(int),
testid(int), score(int)
Trials - trialid(primary,int), attempt(int), location(varchar)
Tests - testid(primary,int), testname(varchar)
Important: Users do not take all tests.
EG TrialId 1 contains userA & userB with userA scoring 10 on test1, 20 on
test2 and userB scoring 30 on test2, 40 on test3, 50 on test4 and is userA &
userB's 1st attempt.
TrialId 2 may be the same, but their 2nd attempt.
TrialId 3 may be the 1st attempt for 2 different users etc.
Suppose the Tests table has 4 tests (1,test1),(2,test2),(3,test3),(4,test4)
There are always 2 users for each trial id.
I want a query which will return all scores for all users for all trials,
BUT must include NULLs if a user did not take a test on that trial.
I thought it may involve a cross join between the Tests table and the Trials
table.
Any help greatly appreciated.|||On May 3, 4:19 pm, BlackKnight <BlackKni...@.discussions.microsoft.com>
wrote:
> Hi - I'm struggling with a query, which is as follows.
> (I have changed the context slightly for simplicity)
> I have 4 tables: users, scores, trials, tests
> Each pair of users takes a series of upto 4 tests in 1 trial, getting a
> score for each test.
> There are a different numbers of trials for each pair of users.
> In detail the tables are:
> Users - userid(primary,int), name(varchar)
> Scores - scoreid(primary,int), userid(int), trialid(int), userid(int),
> testid(int), score(int)
> Trials - trialid(primary,int), attempt(int), location(varchar)
> Tests - testid(primary,int), testname(varchar)
> Important: Users do not take all tests.
> EG TrialId 1 contains userA & userB with userA scoring 10 on test1, 20 on
> test2 and userB scoring 30 on test2, 40 on test3, 50 on test4 and is userA
&
> userB's 1st attempt.
> TrialId 2 may be the same, but their 2nd attempt.
> TrialId 3 may be the 1st attempt for 2 different users etc.
> Suppose the Tests table has 4 tests (1,test1),(2,test2),(3,test3),(4,test4
)
> There are always 2 users for each trial id.
> I want a query which will return all scores for all users for all trials,
> BUT must include NULLs if a user did not take a test on that trial.
> I thought it may involve a cross join between the Tests table and the Tria
ls
> table.
> Any help greatly appreciated.
I think you are looking for this . Not tested
Select a.name,a.userid,a.testid,a.testname,
b.score,c.attempt,c.location
from
( select userid,name from users
cross join
select testid,testname from tests ) as a
left outer join scores b
on a.userid = b.userid
and a.testid = b.testid
left outer join trials c
on b.trialid = c.trialid

Outer join problem

Hi guys

I have got two tables which I need to join

table 1

DHBName DHBService PU Budget Admission

ABC C1 M00 $200 Acute

ADC C2 M10 $300 Severe

Table 2

DHBService PU Admission Actuals

ABC M10 Severe 412.88

ADD M12 Acute 333

The 'DHB Service ' , 'PU' and 'Admission' are common in two tables but 'budget' and 'actuals' are different

I need to combine these two tables in such a way that I have all the fields from both the table

The sample result should be like this

DHBService PU Admission Budget Actuals

ABC M10 Severe Null 412

ADC M00 Acute 200 null

What should I do

I am trying this query but not getting the desired results:-

"SELECT ISNULL(dbo.part1.DHB_service, dbo.part2.DHB_service) , ISNULL(dbo.part1.PU, dbo.part2.PU)
, ISNULL(dbo.part1.budget, 0) , ISNULL(dbo.part2.actuals, 0) , ISNULL(dbo.part1.Admission,
dbo.part2.Admission) AS Expr6
FROM dbo.part1 FULL OUTER JOIN
dbo.part2 ON dbo.part1.PU = dbo.part2.PU AND dbo.part1.DHB_service = dbo.part2.DHB_service AND dbo.part1.Admission = dbo.part2.Admission"

There are a few issues here, beginning with the fact that the row of data:

ADC M00 Acute 200 null

Doesn't exist in your original data set. ADC has only the following values:

ADC C2 M10 $300 Severe

I think you're looking for a UNION query, or perhaps a subquery for the first set of data. Books Online has some great examples of those.

Buck Woody

OUTER JOIN problem

Hello

I have to tables ar and arb, ar holds articles and a swedish
description, arb holds descriptions in other languages.

I want to retreive all articles that match a criteria from ar and also
display their corresponding entries in arb, but if there is NO entry
in arb I still want it to show up as NULL or something, so that I can
get the attention that there IS no language associated with that
article.

I tried to use the following but it does not work correctly

create procedure q_spr_languagecheckpervg
@.varugruppkod varchar(20),
@.sprakkod int

AS

selectar.artnr,
ar.artbeskr,
arb.artbeskr AS 'sprak'
into##q_tbl_languagecheckpervg
fromar LEFT OUTER JOIN arb ON ar.artnr = arb.artnr
wherear.varugruppkod = @.varugruppkod and
arb.sprakkod = @.sprakkod

exec ('master..xp_cmdshell "bcp ##q_tbl_languagecheckpervg out
c:\outpath\adhoc\'+@.varugruppkod+'.xls -Usa -P13hla -c -C"')

drop table ##q_tbl_languagecheckpervg

The problem being that if an article has NO entry in arb it will not
be shown at all.

rgds
MattHi

The left outer joing should show the items in ar even though there is no
entry in arb. Could you please post ddl and example data (see
http://www.aspfaq.com/etiquette.asp?id=5006 ) as well as SQL Server
version.

John

"Matt" <matt@.fruitsalad.org> wrote in message
news:b609190f.0409250018.309f7157@.posting.google.c om...
> Hello
> I have to tables ar and arb, ar holds articles and a swedish
> description, arb holds descriptions in other languages.
> I want to retreive all articles that match a criteria from ar and also
> display their corresponding entries in arb, but if there is NO entry
> in arb I still want it to show up as NULL or something, so that I can
> get the attention that there IS no language associated with that
> article.
> I tried to use the following but it does not work correctly
> create procedure q_spr_languagecheckpervg
> @.varugruppkod varchar(20),
> @.sprakkod int
> AS
>
> select ar.artnr,
> ar.artbeskr,
> arb.artbeskr AS 'sprak'
> into ##q_tbl_languagecheckpervg
> from ar LEFT OUTER JOIN arb ON ar.artnr = arb.artnr
> where ar.varugruppkod = @.varugruppkod and
> arb.sprakkod = @.sprakkod
> exec ('master..xp_cmdshell "bcp ##q_tbl_languagecheckpervg out
> c:\outpath\adhoc\'+@.varugruppkod+'.xls -Usa -P13hla -c -C"')
> drop table ##q_tbl_languagecheckpervg
>
> The problem being that if an article has NO entry in arb it will not
> be shown at all.
> rgds
> Matt|||On 25 Sep 2004 01:18:52 -0700, Matt wrote:

>I want to retreive all articles that match a criteria from ar and also
>display their corresponding entries in arb, but if there is NO entry
>in arb I still want it to show up as NULL or something, so that I can
>get the attention that there IS no language associated with that
>article.

selectar.artnr,
ar.artbeskr,
arb.artbeskr AS 'sprak'
into##q_tbl_languagecheckpervg
fromar
LEFT OUTER JOIN arb
ON ar.artnr = arb.artnr
AND arb.sprakkod = @.sprakkod
wherear.varugruppkod = @.varugruppkod

You should move the test for arb.sprakkok from the where clause to the
join criteria. Otherwise, the non-matching rows (having NULL in all arb
columns) will be removed because NULL (arb.sprakkod) will never be equal
to any @.sprakkod.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis wrote:

> On 25 Sep 2004 01:18:52 -0700, Matt wrote:
>
>>I want to retreive all articles that match a criteria from ar and also
>>display their corresponding entries in arb, but if there is NO entry
>>in arb I still want it to show up as NULL or something, so that I can
>>get the attention that there IS no language associated with that
>>article.
>
> selectar.artnr,
> ar.artbeskr,
> arb.artbeskr AS 'sprak'
> into##q_tbl_languagecheckpervg
> fromar
> LEFT OUTER JOIN arb
> ON ar.artnr = arb.artnr
> AND arb.sprakkod = @.sprakkod
> wherear.varugruppkod = @.varugruppkod
> You should move the test for arb.sprakkok from the where clause to the
> join criteria. Otherwise, the non-matching rows (having NULL in all arb
> columns) will be removed because NULL (arb.sprakkod) will never be equal
> to any @.sprakkod.
> Best, Hugo

knock-knock!

What is OUTER for here? LEFT JOIN will return all ar.* entries and linked arb.* where exist; for the
rest in arb.* columns you get NULL. So it works just fine for the example.

And i'm not sure you can use OUTER & LEFT together - never headr of that and found just an oracle
example, nothing in t-sql.

Wrong?

Thank you,
Andrey|||Hi

If you check books online ("From clause") you will see that OUTER is an
optional keyword in LEFT JOIN. Hugo spotted the reason why this did not
work.

John

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||On Tue, 28 Sep 2004 02:33:13 GMT, Andrey wrote:

>knock-knock!
>What is OUTER for here?
(snip)
>And i'm not sure you can use OUTER & LEFT together - never headr of that and found just an oracle
>example, nothing in t-sql.

Hi Andrey,

The syntax for a left outer join is:
<table-source> LEFT [OUTER] JOIN <table-source> ON <condition
In other words: "OUTER" is an optional keyword (this is true for right
outer joins and full outer joins as well). Though I usually don't include
the OUTER keyword in my own code, I often do include it in newsgroups
postings, as it gives some extra documentation about what I'm doing.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis wrote:
> On Tue, 28 Sep 2004 02:33:13 GMT, Andrey wrote:
>
>>knock-knock!
>>
>>What is OUTER for here?
> (snip)
>>And i'm not sure you can use OUTER & LEFT together - never headr of that and found just an oracle
>>example, nothing in t-sql.
>
> Hi Andrey,
> The syntax for a left outer join is:
> <table-source> LEFT [OUTER] JOIN <table-source> ON <condition>
> In other words: "OUTER" is an optional keyword (this is true for right
> outer joins and full outer joins as well). Though I usually don't include
> the OUTER keyword in my own code, I often do include it in newsgroups
> postings, as it gives some extra documentation about what I'm doing.
> Best, Hugo

Brrr-rrr... Checked books online and it says that yes, OUTER is optional. But does it mean that it
changes anything? As i understood - not really. Correct?

Also i've seen there an example where just JOIN is used(w/o INNER/LEFT/RIGHT/FULL).
But didn't really find any explanation of what's the difference? So can you please explain what just
JOIN does? Seems to me it should be same as INNER JOIN... I'm confused...

Thank you,
Andrey|||From the SQL 2000 Books Online:

<Excerpt href="http://links.10026.com/?link=acdata.chm::/ac_8_qd_09_0zqr.htm"
Inner joins return rows only when there is at least one row from both tables
that matches the join condition. Inner joins eliminate the rows that do not
match with a row from the other table. Outer joins, however, return all rows
from at least one of the tables or views mentioned in the FROM clause, as
long as those rows meet any WHERE or HAVING search conditions. All rows are
retrieved from the left table referenced with a left outer join, and all
rows from the right table referenced in a right outer join. All rows from
both tables are returned in a full outer join

Microsoft SQL ServerT 2000 uses these SQL-92 keywords for outer joins
specified in a FROM clause:

LEFT OUTER JOIN or LEFT JOIN

RIGHT OUTER JOIN or RIGHT JOIN

FULL OUTER JOIN or FULL JOIN
SQL Server supports both the SQL-92 outer join syntax and a legacy syntax
for specifying outer joins based on using the *= and =* operators in the
WHERE clause. The SQL-92 syntax is recommended because it is not subject to
the ambiguity that sometimes results from the legacy Transact-SQL outer
joins.

</Excerpt
Here's some examples:

SELECT *
FROM Table1
JOIN Table2 ON Col1 = Col2

Col1 Col2
---- ----
3 3

SELECT *
FROM Table1
LEFT JOIN Table2 ON Col1 = Col2

Col1 Col2
---- ----
1 NULL
3 3
5 NULL

SELECT *
FROM Table1
RIGHT JOIN Table2 ON Col1 = Col2

Col1 Col2
---- ----
NULL 2
3 3
NULL 4

SELECT *
FROM Table1
FULL JOIN Table2 ON Col1 = Col2

Col1 Col2
---- ----
NULL 2
3 3
NULL 4
5 NULL
1 NULL

SELECT *
FROM Table1
CROSS JOIN Table2

Col1 Col2
---- ----
1 2
3 2
5 2
1 3
3 3
5 3
1 4
3 4
5 4

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Andrey" <leyandrew@.yahoo.com> wrote in message
news:iGo6d.128183$MQ5.77602@.attbi_s52...
> Hugo Kornelis wrote:
>> On Tue, 28 Sep 2004 02:33:13 GMT, Andrey wrote:
>>
>>
>>>knock-knock!
>>>
>>>What is OUTER for here?
>>
>> (snip)
>>
>>>And i'm not sure you can use OUTER & LEFT together - never headr of that
>>>and found just an oracle example, nothing in t-sql.
>>
>>
>> Hi Andrey,
>>
>> The syntax for a left outer join is:
>> <table-source> LEFT [OUTER] JOIN <table-source> ON <condition>
>>
>> In other words: "OUTER" is an optional keyword (this is true for right
>> outer joins and full outer joins as well). Though I usually don't include
>> the OUTER keyword in my own code, I often do include it in newsgroups
>> postings, as it gives some extra documentation about what I'm doing.
>>
>> Best, Hugo
> Brrr-rrr... Checked books online and it says that yes, OUTER is optional.
> But does it mean that it changes anything? As i understood - not really.
> Correct?
> Also i've seen there an example where just JOIN is used(w/o
> INNER/LEFT/RIGHT/FULL).
> But didn't really find any explanation of what's the difference? So can
> you please explain what just JOIN does? Seems to me it should be same as
> INNER JOIN... I'm confused...
>
> Thank you,
> Andrey|||On Wed, 29 Sep 2004 01:59:42 GMT, Andrey wrote:

>Brrr-rrr... Checked books online and it says that yes, OUTER is optional. But does it mean that it
>changes anything? As i understood - not really. Correct?

Hi Andrey,

The keyword OUTER is optional. It doesn't matter if you specify it or not,
so the only difference between "LEFT JOIN" and "LEFT OUTER JOIN" is five
letters and a blank.

>Also i've seen there an example where just JOIN is used(w/o INNER/LEFT/RIGHT/FULL).

Like OUTER, INNER is an optional keyword as well. If you see just JOIN,
it's an INNER JOIN. I never leave that keyword out, as I find it rather
confusing. The full list of join types, with optional keywords between
[brackets], is:

* [INNER] JOIN
* LEFT [OUTER] JOIN
* RIGHT [OUTER] JOIN
* FULL [OUTER] JOIN
* CROSS JOIN

>But didn't really find any explanation of what's the difference? So can you please explain what just
>JOIN does? Seems to me it should be same as INNER JOIN... I'm confused...

I think Dan's reply covered this already.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

OUTER JOIN plus split? - how to?

Hi,

I have two tables.. one has a list of the UK postcodes.. well..it's actually the districts..

so it's the first two three or four characters (basically all UK postcodes are one or two letters.. then one or two numbers.. then a gap.. and then some more info)

So my first table has everything "before the gap" in seperate records.

My second table is full of exact postcodes.. so everything before and after the gap.

What I need to do is.. for each "full" postcode I have... strip it down so I get it formatted to everything before the gap.. and then find out how many of those I have...

This is also complicated because many of the "full postcodes" don't have the gap.

so if I have:

er56 7tr
fr2 4tg
er567tr
po3 8ty

I would want results of:

er56 - 2
fr2 - 1
po3 - 1

One I have that, I can bring in the second table with all of the parcial postcodes to do an OUTER JOIN and get the postcodes where I DONT have any results.

I know that's pretty complicated, so I'm stuggling a little!

sO i need to get the first "X" amount of characters out of a db value (it could be two values.. could be up to four- first one or two will be char, second one or two will be numeric), then do a count.

Can anyone help?

Just to help clarify:

If I was doing this ASP I would write it as:

get first value from string
- add to output string

get second value from string
- add to output string

get third value
If numeric - add to string
If not - stop loop

get fourth value
If numeric - add to string

If not - stop loop

|||

ok thought of an easier way - but still need help.

In my SELECT clause, need to have a variable which is gathered by the length of the postcode string.. so:

1) - Remove any spaces in string
2) - Check length
3) - If length = 5 then postcode = first 2 chars in string
4) - If length = 6 then postcode = first 3 chars in string
5) If length > 6 then postcode = first 4 chars in string.

Any ideas?

|||I have solved this, but have another issue which has surfaced - other post...

OUTER JOIN on more than two tables

Two questions:
1) Can an OUTER JOIN, such as a LEFT OUTER JOIN be done on more than two
tables and, if so, what is the syntax for it ?
I want to do a LEFT OUTER JOIN so that all rows from my left table are
selected which meet my where conditions, and where matching rows from
more than one other table are selected based on a match between my left
table and the other tables.
2) In the above scenario, one of the matches occurs between a column
value in my non-left table and a column value in another one of my
non-left tables. How can this also be specified in a LEFT OUTER JOIN
with more than one table in my LEFT OUTER JOIN syntax ?
Example:
Table1: ColumnA, ColumnB, other columns etc.
Table2: ColumnC, ColumnD
Table3: ColumnE, ColumnF
Table4: ColumnG, ColumnH
I want to join these tables such that all rows and columns from Table1
are selected matching my where condition. ColumnC is also selected when
ColumnA matches ColumnD, else ColumnC is null. ColumnE is also selected
when ColumnA matches ColumnF, else ColumnE is null. Finally ColumnG is
also selected when Table3's ColumnE matches ColumnH, else ColumnG is null.For example:
SELECT *
FROM TableA AS A
LEFT JOIN TableB AS B
ON A.x = B.x
LEFT JOIN TableC AS C
ON B.x = C.x
If that doesn't answer the question then please post DDL and sample data:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Edward,see it this helps you
SELECT
ColumnA,
ColumnB,
CASE
WHEN EXISTS (
SELECT ColumnC FROM Table2 t2
WHERE t2.ColumnD = table1.ColumnA)
THEN 1
ELSE 0
END AS ColumnC,
CASE
WHEN EXISTS (
SELECT ColumnE FROM Table3 t3
WHERE t3.ColumnF = table1.ColumnA)
THEN 1
ELSE 0
END AS ColumnE,
CASE
WHEN EXISTS (
SELECT ColumnG FROM Table4 t4
WHERE t3.ColumnH = table1.ColumnA)
THEN 1
ELSE 0
END AS ColumnE
FROM Table1
"Edward Diener" <eddielee_no_spam_here@.tropicsoft.com> wrote in message
news:O656jo9UFHA.2036@.TK2MSFTNGP10.phx.gbl...
> Two questions:
> 1) Can an OUTER JOIN, such as a LEFT OUTER JOIN be done on more than two
> tables and, if so, what is the syntax for it ?
> I want to do a LEFT OUTER JOIN so that all rows from my left table are
> selected which meet my where conditions, and where matching rows from
> more than one other table are selected based on a match between my left
> table and the other tables.
> 2) In the above scenario, one of the matches occurs between a column
> value in my non-left table and a column value in another one of my
> non-left tables. How can this also be specified in a LEFT OUTER JOIN
> with more than one table in my LEFT OUTER JOIN syntax ?
> Example:
> Table1: ColumnA, ColumnB, other columns etc.
> Table2: ColumnC, ColumnD
> Table3: ColumnE, ColumnF
> Table4: ColumnG, ColumnH
> I want to join these tables such that all rows and columns from Table1
> are selected matching my where condition. ColumnC is also selected when
> ColumnA matches ColumnD, else ColumnC is null. ColumnE is also selected
> when ColumnA matches ColumnF, else ColumnE is null. Finally ColumnG is
> also selected when Table3's ColumnE matches ColumnH, else ColumnG is null.|||Edward,
Conceptually. each "Join" is a join between only two "Relations", or
"Resultsets". When you have more than two tables in a From Clause, and,
therefore, you have two or more joins. the second "Join" that takes place ca
n
be thought of as a Join between the intermediate resultset created by the
first join, and the third table. So the answer to your question depends on
what order, and what exactly, you wish to Join in this second Join...
Two possibilities exist:
You could Join Tables B to A, using Outer Join syntax, and then Join C to
that resultset, also using Outer Join SyntAX...
From TableA
Left Outer Join Table B On .....
Left Outer Join Table C On ......
Or 2) you might be wishing to Join the COmbined Inner Join of Tables B & C
to Table A. In this case you would be joining B & C FIrst, and then Joining
THAT resultset to TableA using Outer Join Syntax
From TableA
Left Outer Join (Table B Join Table C On ....)
On ....
This approach might be used to get ALL Customers, (even the ones with no
Invoices), plus the data from a Invoices and connected Invoice Details table
s
, but only include invoices that have details...
"Edward Diener" wrote:

> Two questions:
> 1) Can an OUTER JOIN, such as a LEFT OUTER JOIN be done on more than two
> tables and, if so, what is the syntax for it ?
> I want to do a LEFT OUTER JOIN so that all rows from my left table are
> selected which meet my where conditions, and where matching rows from
> more than one other table are selected based on a match between my left
> table and the other tables.
> 2) In the above scenario, one of the matches occurs between a column
> value in my non-left table and a column value in another one of my
> non-left tables. How can this also be specified in a LEFT OUTER JOIN
> with more than one table in my LEFT OUTER JOIN syntax ?
> Example:
> Table1: ColumnA, ColumnB, other columns etc.
> Table2: ColumnC, ColumnD
> Table3: ColumnE, ColumnF
> Table4: ColumnG, ColumnH
> I want to join these tables such that all rows and columns from Table1
> are selected matching my where condition. ColumnC is also selected when
> ColumnA matches ColumnD, else ColumnC is null. ColumnE is also selected
> when ColumnA matches ColumnF, else ColumnE is null. Finally ColumnG is
> also selected when Table3's ColumnE matches ColumnH, else ColumnG is null.
>sql

OUTER JOIN issue

Hi all,
I have two tables (for example, table1, table2) where table1 holds thesame data as table2 but also has other rows that are no contained intable2.
Now if I performed the following query...
SELECT table1.name
FROM table1
LEFT OUTER JOIN table2 ON (RTRIM(LOWER(table1..table_name)) = RTRIM(LOWER(table2.table_name)))
... I expect to get the rows that are not contained in both tables. Butfor some reason I don't get this. I get all the rows from tabel1.
Is my thinking of what theLEFT OUTER JOIN query wrong, or is the query wrong?
To get around my problem, I had to do the following
SELECT table1.name
FROM table1
WHERE table1.table_name not IN (SELECT table_name FROM table2)
I would have preferred to have solved this with theLEFT OUTER JOIN though
Thanks
Tryst
I think you are a little confused about outer joins...
Left/Right Outer joins return all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions
Say I have
Table1
Id Name Age
-----
1 a 20
2 b 30
3 c 22
Table2
Id Sex
---
1 M
2 F
Say I want id,name,age,sex(if available) then I would use left join
Select id,name,age,sex from table1 left join table2 on table1.id = table2.id
This will return
Id name age sex
------
1 a 20 M
2 b 30 F
3 c 22 <null>
|||You need to add a WHERE condition to return the results you want from the LEFT OUTER JOIN:

SELECT table1.name
FROM table1
LEFT OUTER JOIN table2 ON (RTRIM(LOWER(table1..table_name)) = RTRIM(LOWER(table2.table_name)))
WHERE table2.table_name IS NULL

What would probably perform better for you is this:

SELECT table1.name
FROM table1
WHERE NOT EXISTS (SELECT NULL FROM table2 WHERE table1.table_name = table2)

Check both execution plans/execution time in Query Analyzer to see which one performs better.

|||Ok, thanks guys.
Why is the inner SELECT query selecting NULL from table2? Whats it use?
Tryst
|||

Tryst wrote:

Why is the inner SELECT query selecting NULL from table2? Whats it use?

The list of column names is immaterial for the EXISTS. What isimportant is the WHERE condition. I have a habit of using NULL,partly because I read somewhere that it uses less resources than a * ora specific column (I am not sure whether or not that is true).

Outer Join in AS2005 cube

I have three tables for the 'Store' dimension...

Store City State

When I include the state name from the State table in the dimension and process the cube, I am missing many stores. Those stores missing don't have a state code in the city table. So, because the cube is generating an inner join between the three tables, I am missing some stores. I need to model an 'outer join' in the relationship between City and State tables. How can I do this in AS 2005?

You can create a named query (in the DataSourceView editor) to include the 3 tables with the wanted outer/inner joins (the named query is the "SELECT ... FROM ..." statement). Then build the 'Store' dimension on it.

Adrian Dumitrascu

Outer Join help

Ok...I have 3 tables.

Entity
---
name
entity_key

Address
----
street
zip
mailing_flag
entity_key

Phone
---
phone_number
phone_type_key
entity_key

I want to see all of the Entity records with their corresponding Address and Phone records. (select e.name, a.street, a.zip, p.phone_number)But only show the Address record for that Entity if the mailing_flag is 'Y' and I only want to see the Top 1 Phone record where the phone_type_key = 'Home'. If the above criteria isn't met I just want to see nulls for the Address and Phone records.

My problem is getting ALL the Entity records to return. It only wants to give me the Entity records that have Address or Phone associated with them. That and somehow showing the Top 1 phone record for the Entity are my issues.

Any help would be much appreciated.....Thanks!This sounds suspiciously like an incomplete homework assignment. Are you holding out part of the specification?

-PatP|||Originally posted by Pat Phelan
This sounds suspiciously like an incomplete homework assignment. Are you holding out part of the specification?

-PatP

--Nope, that's all I need. Maybe an outer join won't work, I don't know, that's why I'm asking for help.

Steve|||Ok, then rolling them all together I get:SELECT e.*
FROM dbo.entity AS e
JOIN (SELECT TOP 1 *
FROM dbo.phone
WHERE 'Home' = phone_type_key) AS p
ON p.entity_key = e.entity_key)
JOIN (SELECT TOP 1 a.*
FROM dbo.Address
WHERE 'Y' = mailng_flag) AS a
ON (a.entity_key = e.entity_key)-PatP

Outer join for two tables

Hi guys,

Please Help!

I am using Excel's VBA in order to retrieve sql Data.

I have three tables of the "my Company" DataBase (sql server 2000):

Table1, Table2, Table3

I need to join:

Table1.FieldXX=Table2.FieldYY

Table1.FieldWW= Table3.FieldZZ

In both cases, I should retrieve all data from Table1, even if Table2 or Table3 don't have the correspondent entries.

I am trying to use the code below, but getting "sql syntax error":

FROM myCompany.dbo.Table1 Table1

LEFT OUTER JOIN myCompany.dbo.Table2 Table2 ON (Table1.FieldXX=Table2.FieldYY)

AND

LEFT OUTER JOIN myCompany.dbo.Table3 Table3 ON (Table1.FieldWW= Table3.FieldZZ)

I will appreciatte any help.

Thanks in advance,

Aldo.Hi Guys,

This is the code working properly:

FROM myCompany.dbo.Table1 Table1

LEFT OUTER JOIN myCompany.dbo.Table2 Table2 ON Table1.FieldXX= Table2 .FieldYY

LEFT OUTER JOIN myCompany.dbo.Table3 Table3 ON Table1.FieldWW= Table3.FieldZZ

Aldo.sql

Outer join difficulty

Can someone please help me with the following join ? I have two tables that I need to join. The select statement for the first part is as follows:-

SELECT * FROM dbo.Holdings
WHERE (Accno = '260869') AND (Rundate = '20031128') AND (Sharename LIKE 'P%')
ORDER BY Sharename

The table that is produced is as follows:-

Rundate Accno Sharename NotAtHome
20031128 260869 PANGBOURNE 123200
20031128 260869 PARAMOUNT 221100
20031128 260869 PRISAIB. 221100

The table I wish to join on, is as follows:-

SELECT * FROM dbo.Holdings
WHERE (Accno = '260869') AND (Rundate = '20031103') AND (Sharename LIKE 'P%')
ORDER BY Sharename

The result is as follows:-

Rundate Accno Sharename NotAtHome
20031103 260869 PANGBOURNE 123200

Note that Paramount and Prisaib are missing from the second table.

I need to create an output table showing all three records with the missing two having 0 under "NotAtHome".

I'd much appreciate any assistance with this..

thanks!(SELECT * FROM dbo.Holdings
WHERE (Accno = '260869') AND (Rundate = '20031128') AND (Sharename LIKE 'P%')
ORDER BY Sharename) q1
LEFT OUTER JOIN
(SELECT * FROM dbo.Holdings
WHERE (Accno = '260869') AND (Rundate = '20031103') AND (Sharename LIKE 'P%')
ORDER BY Sharename) q2
ON q1.AccNo = q2.AccNo;

Assuming AccNo is a key.

The default value for non-matching rows is Null, however if you do indeed want a numerical then,

I am not entirely sure of MSSQL syntax however I believe it's the following for Case statements.

select attributes, (case NotAtHome If Null Then 0 Else NotAtHome END) As "NotAtHome"
from
(
Above query
)|||you would not need ORDER BY in the subqueries, and in fact the query is a bit neater without subqueries

the NotAtHome column is weird, i left it as t1.NotAtHome as alkemac specified, but i think he/she might have meant t2.NotAtHome select t1.Rundate
, t1.Accno
, t1.Sharename
, case when t2.AccNo is null
then 0
else t1.NotAtHome
end as NotAtHome
from dbo.Holdings t1
left outer
join dbo.Holdings t2
on t1.AccNo = t2.AccNo
and t2.Rundate = '20031103'
and t2.Sharename like 'P%'
where t1.Accno = '260869'
and t1.Rundate = '20031128'
and t1.Sharename like 'P%'rudy
http://r937.com/