problem with join

[Rowan]

Hello,

I am having a problem with a join. Either I am just not seeing the
obvious, it isn't possible, or I need to use a different approach. I
have an application with a vsflexgrid that needs to display the
following:

filenumber, BL, Container_BL, BL_HBL, HBL, Container_HBL

The tables look like:

CREATE TABLE tblFILE (
FileNumber int not null Primary Key,
status char(1) not null
)

CREATE table tblBL (
bl_Identity bigint not null primary key identity,
bl varchar(20) not null,
FileNumber BIGint not null,

CONSTRAINT FK_tblFILE_tblB L FOREIGN KEY (FileNumber) REFERENCES
tblFILE(FileNum ber)
)

CREATE TABLE tblCONTAINER (
ContainerID bigint not null primary key identity,
ContainerNumber varchar(20) not null
)


CREATE table tblCONTAINER_BL (
ContainerID bigint not null ,
BL_Identity bigint not null ,

CONSTRAINT FK_tblCONTAINER FOREIGN KEY (ContainerID) REFERENCES
tblCONTAINER(Co ntainerID),

CONSTRAINT FK_tblBL FOREIGN KEY (BL_Identity) REFERENCES
tblBL(BL_Identi ty),
constraint PK_tblBL_tblCON TAINER primary key (ContainerID,
BL_Identity)
)


CREATE TABLE tblHBL (
hbl_Identity bigint not null primary key identity,
hbl varchar(20) not null,
FileNumber BIGint not null,
bl_identity bigint,

CONSTRAINT FK_tblFILE_tblH BL FOREIGN KEY (FileNumber) REFERENCES
tblFILE(FileNum ber)

CONSTRAINT FK_tblBL FOREIGN KEY (bl_identity) REFERENCES
tblBL(bl_identi ty)

)


CREATE table tblCONTAINER_HB L (
ContainerID bigint not null ,
hbl_Identity bigint not null ,

CONSTRAINT FK_tblCONTAINER FOREIGN KEY (ContainerID) REFERENCES
tblCONTAINER(Co ntainerID),

CONSTRAINT FK_tblHBL FOREIGN KEY (hbl_Identity) REFERENCES
tblHBL(hbl_Iden tity),
constraint PK_tblHBL_tblCO NTAINER primary key (ContainerID,
hbl_Identity)
)

To explain this a little bit...a file has relations with zero to many
BLs, a BL has zero to many containers. A file also has zero to many
HBLs, an HBL has zero to many Containers. Also, a BL has zero to many
HBLs. An HBL will eventually always have a BL but because of the
business process, many months may go by without knowing what the BL is
so the stable relationship for the HBL is with the file. But when the
HBL has a relationship with a BL, it needs to be clear.

So my problem, I tried to make a view that shows each relationship but
I can't get it to show correctly.

CREATE VIEW fileselecthbl_b l_view as (
Select f1.FileNumber, f1.Status, f1.CustomerID, bl.BL_Identity, bl.BL,
hbl.HBL_Identit y, hbl.HBL,
chbl.ContainerI D, c1.ContainerNum ber from tblFile f1 left OUTER JOIN
tblHBL hbl
ON (f1.FileNumber = hbl.FileNumber) full OUTER JOIN tblBL bl ON
(bl.BL_Identity = hbl.BL_ID)
LEFT OUTER JOIN tblCONTAINER_HB L chbl ON (hbl.HBL_Identi ty =
chbl.HBL_ID) left outer join
tblCONTAINER c1 ON (chbl.Container ID = c1.ContainerIde ntity) )

But this doesn't give me what I need. This gives me 40 rows with a
filenumber and six with null in the filenumber. What I need is for the
hbl AND the bl to be joined to the initial tblFILE.


---This is vb code showing where it will be used.
With vsfgOpenedFiles
.TextMatrix(0, 0) = "File Number"
.TextMatrix(0, 1) = "File Status"
.TextMatrix(0, 2) = "BL"
.TextMatrix(0, 3) = "Container by BL"
.TextMatrix(0, 4) = "HBL"
.TextMatrix(0, 5) = "Container by HBL"
.AutoSize 0, 5
End With
----

I need to have rows that show FileNumber, Status, Bl, Container by BL,
HBL if it has a relationship with BL, Container by HBL.

If a BL does not have a relationship with an HBL then the row needs to
show FileNumber, Status, BL, Container by BL, Null, Null

If an HBL does not have a relationship with BL then it needs to be a
line with FileNumber, Status, Null, Null, HBL, Container by HBL.

Jeez, I am sorry this is so long. I don't know how else to explain my
problem. I am going to stop. I would appreciate anybody's ideas.

--rowan

[Erland Sommarskog]

Re: problem with join

Rowan (phantomtoe@yah oo.com) writes:[color=blue]
> I am having a problem with a join. Either I am just not seeing the
> obvious, it isn't possible, or I need to use a different approach. I
> have an application with a vsflexgrid that needs to display the
> following:[/color]

Thanks for the tables and the description. Alas, you did not include
any sample data in form of INSERT statements and the expected results
from the sample. Therefore I find it difficult to understand what you
are looking for.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Rowan]

Re: problem with join

Sorry, I didn't include any sample data. I think this should do it.

Insert tblFILE (FileNumber, Status) Values(10111, 'O')
Insert tblFILE (FileNumber, Status) Values(10222, 'O')
Insert tblFile (FileNumber, Status) values(10333, 'X')
Insert tblFILe (FileNumber, Status) Values(10444, 'O')

Insert tblBL (bl_Identity, bl, FileNumber) values(200, 'bl1', 10222)
Insert tblBL (bl_Identity, bl, FileNumber) values(210, 'bl2', 10111)
Insert tblBL (bl_Identity, bl, FileNumber) values(220, 'bl2', 10111)

Insert tblCONTAINER (ContainerID, ContainerNumber ) Values(30,
C10000000)
Insert tblCONTAINER (ContainerID, ContainerNumber ) Values(31,
C11111111)
Insert tblCONTAINER (ContainerID, ContainerNumber ) Values(32,
C12222222)
Insert tblCONTAINER (ContainerID, ContainerNumber ) Values(33,
C13333333)
Insert tblCONTAINER (ContainerID, ContainerNumber ) Values(34,
C14444444)
Insert tblCONTAINER (ContainerID, ContainerNumber ) Values(35,
C15555555)
Insert tblCONTAINER (ContainerID, ContainerNumber ) Values(36,
C16666666)

Insert tblCONTAINER_BL (ContainerID, BL_Identity) Values(30, 200)
Insert tblCONTAINER_BL (ContainerID, BL_Identity) values(31, 200)
Insert tblCONTAINER_BL (ContainerID, BL_Identity) values(32, 220)

Insert tblHBL (hbl_Identity, hbl, FileNumber, bl_Identity) values(400,
'hbl1', 10222, NULL)
Insert tblHBL (hbl_Identity, hbl, FileNumber, bl_Identity) values(410,
'hbl2', 10111, 210)
Insert tblHBL (hbl_Identity, hbl, FileNumber, bl_Identity) values(420,
'hbl3', 10444, NULL)
Insert tblHBL (hbl_Identity, hbl, FileNumber, bl_Identity) values(430,
'hbl4', 10111, 210)

Insert tblCONTAINER_HB L (ContainerID, hbl_Identity) values(33, 400)
Insert tblCONTAINER_HB L (ContainerID, hbl_Identity) values(34, 400)
Insert tblCONTAINER_HB L (ContainerID, hbl_Identity) values(35, 400)
Insert tblCONTAINER_HB L (ContainerID, hbl_Identity) values(36, 430)

[Erland Sommarskog]

Re: problem with join

Rowan (phantomtoe@yah oo.com) writes:[color=blue]
> Sorry, I didn't include any sample data. I think this should do it.[/color]

Thsnks for the sample data, but I still don't know what the desired output
is. The output I got from the query you posted (where I had to change
some names to make it compile, and remove CustomerID) was:

10111 O 210 bl2 410 hbl2 NULL NULL
10111 O 210 bl2 430 hbl4 36 C16666666
10222 O NULL NULL 400 hbl1 33 C13333333
10222 O NULL NULL 400 hbl1 34 C14444444
10222 O NULL NULL 400 hbl1 35 C15555555
10333 X NULL NULL NULL NULL NULL NULL
10444 O NULL NULL 420 hbl3 NULL NULL
NULL NULL 200 bl1 NULL NULL NULL NULL
NULL NULL 220 bl2 NULL NULL NULL NULL


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Rowan]

Re: problem with join

Oops I left something out. For the last two lines I need more than
the filenumber. I also need the BL Container information.

NULL NULL 200 bl1 NULL NULL NULL NULL
NULL NULL 220 bl2 NULL NULL NULL NULL


So, what I would need for this line is:

10222 O 200 bl1 NULL NULL 30 C10000000
10222 O 200 bl1 NULL NULL 31 C11111111
10111 O 220 bl3 NULL NULL 32 C12222222

Do I need to create two views?

[Erland Sommarskog]

Re: problem with join

Rowan (phantomtoe@yah oo.com) writes:[color=blue]
> Oops I left something out. For the last two lines I need more than
> the filenumber. I also need the BL Container information.
>
> NULL NULL 200 bl1 NULL NULL NULL NULL
> NULL NULL 220 bl2 NULL NULL NULL NULL
>
>
> So, what I would need for this line is:
>
> 10222 O 200 bl1 NULL NULL 30 C10000000
> 10222 O 200 bl1 NULL NULL 31 C11111111
> 10111 O 220 bl3 NULL NULL 32 C12222222
>
> Do I need to create two views?[/color]

Thanks for the data! I believe I now get hinch about your data model.
Here is a query that appears to correspond to your initial narrative,
and indeed gives the above rows:

SELECT FileNumber = coalesce(f1.Fil eNumber, f2.FileNumber),
Status = coalesce(f1.sta tus, f2.status),
bl.bl_Identity, bl.bl, hbl.hbl_Identit y, hbl.hbl,
ContainerID = coalesce(cbl.Co ntainerID, chbl.ContainerI D),
coalesce(c1.Con tainerNumber, c2.ContainerNum ber)
FROM tblFILE f1
LEFT JOIN (tblHBL hbl
JOIN tblCONTAINER_HB L ch ON hbl.hbl_Identit y = ch.hbl_Identity
JOIN tblCONTAINER c1 ON ch.ContainerID = c1.ContainerID)
ON f1.FileNumber = hbl.FileNumber
FULL JOIN (tblBL bl
JOIN tblCONTAINER_BL cbl ON bl.bl_Identity = cbl.BL_Identity
JOIN tblCONTAINER c2 ON cbl.ContainerID = c2.ContainerID
JOIN tblFILE f2 ON bl.FileNumber = f2.FileNumber)
ON bl.bl_Identity = hbl.bl_identity

The key here is that JOIN is an operator just like plus. The HBL should
be inner joined to the container table, because once you have an HBL,
you have the rest. So you join tblFILE with the tbale (HBL JOIN ch JOIN c1).
Same applies for the FULL JOIN stuff.

Not that the parantheses specifies *logical* evaluation order. The
optmizer may apply all sorts of shortcuts, as long as the result is
the the one specified by the expression.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Rowan]

Re: problem with join

Ohhhh, thank you! This is so helpful. Plus it gives me a model to
look at and understand for future reference. Thank you very much.


Erland Sommarskog <sommar@algonet .se> wrote in message news:<Xns93C4D4 310D753Yazorman @127.0.0.1>...[color=blue]
> Rowan (phantomtoe@yah oo.com) writes:[color=green]
> > Oops I left something out. For the last two lines I need more than
> > the filenumber. I also need the BL Container information.
> >
> > NULL NULL 200 bl1 NULL NULL NULL NULL
> > NULL NULL 220 bl2 NULL NULL NULL NULL
> >
> >
> > So, what I would need for this line is:
> >
> > 10222 O 200 bl1 NULL NULL 30 C10000000
> > 10222 O 200 bl1 NULL NULL 31 C11111111
> > 10111 O 220 bl3 NULL NULL 32 C12222222
> >
> > Do I need to create two views?[/color]
>
> Thanks for the data! I believe I now get hinch about your data model.
> Here is a query that appears to correspond to your initial narrative,
> and indeed gives the above rows:
>
> SELECT FileNumber = coalesce(f1.Fil eNumber, f2.FileNumber),
> Status = coalesce(f1.sta tus, f2.status),
> bl.bl_Identity, bl.bl, hbl.hbl_Identit y, hbl.hbl,
> ContainerID = coalesce(cbl.Co ntainerID, chbl.ContainerI D),
> coalesce(c1.Con tainerNumber, c2.ContainerNum ber)
> FROM tblFILE f1
> LEFT JOIN (tblHBL hbl
> JOIN tblCONTAINER_HB L ch ON hbl.hbl_Identit y = ch.hbl_Identity
> JOIN tblCONTAINER c1 ON ch.ContainerID = c1.ContainerID)
> ON f1.FileNumber = hbl.FileNumber
> FULL JOIN (tblBL bl
> JOIN tblCONTAINER_BL cbl ON bl.bl_Identity = cbl.BL_Identity
> JOIN tblCONTAINER c2 ON cbl.ContainerID = c2.ContainerID
> JOIN tblFILE f2 ON bl.FileNumber = f2.FileNumber)
> ON bl.bl_Identity = hbl.bl_identity
>
> The key here is that JOIN is an operator just like plus. The HBL should
> be inner joined to the container table, because once you have an HBL,
> you have the rest. So you join tblFILE with the tbale (HBL JOIN ch JOIN c1).
> Same applies for the FULL JOIN stuff.
>
> Not that the parantheses specifies *logical* evaluation order. The
> optmizer may apply all sorts of shortcuts, as long as the result is
> the the one specified by the expression.[/color]