Help with joining records

[Rnt6872]

Table A Table B

BOL# B_BOL#

Chargeback#
Hi All,
I have been struggling with this for the past few months. I have two
tables that I'm inner joining on BOL#=B_BOL#. This works fine. Now for
the problem....When there are chargeback# fields associated with B_BOL#
they aren't being captured as additional records. None of my tables
have primary keys because at any given time any field can contain a
"null" value, so I am unable to assign a foreign key, I think. How can
I attach the subsequent records to the main record to bring back the
additonal data? Please help!!!

[Matik]

Not sure this will help, because I'm not sure, did I fully understood
your problem.
If what you mean is like fallow:

B_BOL# from Table B can be a key either with BOL# or/and Chargeback#,
then I will try as fallow:

select * from TableB B
left join TableA A1
on B.B_BOL# = A1.BOL#
left join TableA A2
on B.B_BOL# = A2.Chargeback#

If you do not want to have doble records, you can ommite it with using
is not null (if it is always like this, that the BOL# is null when
chargeback# isn't null ... and opposite).

Or You can use case statement, by removing null records.

In the future, will be better, if you put the script, or some example
data, if the script will be to complicated.

Best regards

Matik

Rnt6872 napisal(a):

Table A Table B

BOL# B_BOL#

Chargeback#
Hi All,
I have been struggling with this for the past few months. I have two
tables that I'm inner joining on BOL#=B_BOL#. This works fine. Now for
the problem....When there are chargeback# fields associated with B_BOL#
they aren't being captured as additional records. None of my tables
have primary keys because at any given time any field can contain a
"null" value, so I am unable to assign a foreign key, I think. How can
I attach the subsequent records to the main record to bring back the
additonal data? Please help!!!

[Erland Sommarskog]

Rnt6872 (r_******@msn.com) writes:

Table A Table B

BOL# B_BOL#

Chargeback#
Hi All,
I have been struggling with this for the past few months. I have two
tables that I'm inner joining on BOL#=B_BOL#. This works fine. Now for
the problem....When there are chargeback# fields associated with B_BOL#
they aren't being captured as additional records. None of my tables
have primary keys because at any given time any field can contain a
"null" value, so I am unable to assign a foreign key, I think. How can
I attach the subsequent records to the main record to bring back the
additonal data? Please help!!!

1) The standard recommendations for this type of questions, is that
you post a) CREATE TABLE statements for the tables involed. b) INSERT
statements with sample data. c) the desired result given the sample.
This sort of information helps tremendously to understand what you
want to achieve. Also it makes it very easy to develop a tested
solution.

2) If all your columns can be nullable at any time, you have a very
difficult data model to work with. Most people would probably say
that your data model is flat wrong. Maybe there is a good reason
to have all columns nullable, but it sounds very suspicious.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Michael Austin]

Erland Sommarskog wrote:

Rnt6872 (r_******@msn.com) writes:

>Table A Table B

BOL# B_BOL#

Chargeback#
Hi All,
I have been struggling with this for the past few months. I have two
tables that I'm inner joining on BOL#=B_BOL#. This works fine. Now for
the problem....When there are chargeback# fields associated with B_BOL#
they aren't being captured as additional records. None of my tables
have primary keys because at any given time any field can contain a
"null" value, so I am unable to assign a foreign key, I think. How can
I attach the subsequent records to the main record to bring back the
additonal data? Please help!!!

1) The standard recommendations for this type of questions, is that
you post a) CREATE TABLE statements for the tables involed. b) INSERT
statements with sample data. c) the desired result given the sample.
This sort of information helps tremendously to understand what you
want to achieve. Also it makes it very easy to develop a tested
solution.

2) If all your columns can be nullable at any time, you have a very
difficult data model to work with. Most people would probably say
that your data model is flat wrong. Maybe there is a good reason
to have all columns nullable, but it sounds very suspicious.

that was so diplomatic...

Given:
"None of my tables have primary keys because at any given time any field can
contain a null"

Then I would say that whoever designed this needed to pass a few more classes in
database design. Your data model needs professional help. Period.

--
Michael Austin.
Database Consultant

[othellomy]

I think you should provide some real examples from your data also, what
you are trying to accomplish by 'bring back additional data'.

Rnt6872 wrote:

Table A Table B

BOL# B_BOL#

Chargeback#
Hi All,
I have been struggling with this for the past few months. I have two
tables that I'm inner joining on BOL#=B_BOL#. This works fine. Now for
the problem....When there are chargeback# fields associated with B_BOL#
they aren't being captured as additional records. None of my tables
have primary keys because at any given time any field can contain a
"null" value, so I am unable to assign a foreign key, I think. How can
I attach the subsequent records to the main record to bring back the
additonal data? Please help!!!