select Statement w/i inner joins

I am having a select problem, below is what I am after.

TableA
Number
1
2
3
4
5

TableB
Number Amount
2 100
3 50
4 175


TableC
Number Amount
2 180
5 189


Results looking for

Number TableB Amount TableC Amount
2 100 180
3 50
4 175
5 189


I would like to select information from tableB and TableC where the number
exists with in TableA

Something along the lines of

SELECT TableA.Number, TableB.Amount, TableC.Amount
FROM (TableA INNER JOIN TableB ON TableA.Number = TableB.Number)
INNER JOIN TableC ON TableA.Number = TableC.Number

TableC has 168 records where the number exists with in tableA So I know I
should at least get a result with 168 records or
more. With the above select statement I get a result of 145.

Any ideas on what I am doing wrong?

Thanks
Toni

ToniS wrote:
I am having a select problem, below is what I am after.

TableA
Number
1
2
3
4
5

TableB
Number Amount
2 100
3 50
4 175


TableC
Number Amount
2 180
5 189


Results looking for

Number TableB Amount TableC Amount
2 100 180
3 50
4 175
5 189


I would like to select information from tableB and TableC where the
number exists with in TableA

Something along the lines of

SELECT TableA.Number, TableB.Amount, TableC.Amount
FROM (TableA INNER JOIN TableB ON TableA.Number = TableB.Number)
INNER JOIN TableC ON TableA.Number = TableC.Number

TableC has 168 records where the number exists with in tableA So I
know I should at least get a result with 168 records or
more. With the above select statement I get a result of 145.

Any ideas on what I am doing wrong?

Yes. You need to change INNER JOIN to LEFT JOIN in both places

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Thanks Bob for answering so quickly, I forgot to mention if there is no data
in TableB and TableC I do not want to select the reocrd is TableA

In my below example number 1 does not have any data in TableA or TableB
therefor I do not want it in my results

Thanks

Tsharp


"Bob Barrows [MVP]" wrote:

ToniS wrote:
I am having a select problem, below is what I am after.

TableA
Number
1
2
3
4
5

TableB
Number Amount
2 100
3 50
4 175


TableC
Number Amount
2 180
5 189


Results looking for

Number TableB Amount TableC Amount
2 100 180
3 50
4 175
5 189


I would like to select information from tableB and TableC where the
number exists with in TableA

Something along the lines of

SELECT TableA.Number, TableB.Amount, TableC.Amount
FROM (TableA INNER JOIN TableB ON TableA.Number = TableB.Number)
INNER JOIN TableC ON TableA.Number = TableC.Number

TableC has 168 records where the number exists with in tableA So I
know I should at least get a result with 168 records or
more. With the above select statement I get a result of 145.

Any ideas on what I am doing wrong?

Yes. You need to change INNER JOIN to LEFT JOIN in both places

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

You will need to add criteria like:

WHERE TableB.Amount is not null and TableC.amount is not null

This will filter out results where they are both null

ToniS wrote:
Thanks Bob for answering so quickly, I forgot to mention if there is
no data in TableB and TableC I do not want to select the reocrd is
TableA

In my below example number 1 does not have any data in TableA or
TableB therefor I do not want it in my results

Thanks

Tsharp


"Bob Barrows [MVP]" wrote:

ToniS wrote:
I am having a select problem, below is what I am after.

TableA
Number
1
2
3
4
5

TableB
Number Amount
2 100
3 50
4 175


TableC
Number Amount
2 180
5 189


Results looking for

Number TableB Amount TableC Amount
2 100 180
3 50
4 175
5 189


I would like to select information from tableB and TableC where the
number exists with in TableA

Something along the lines of

SELECT TableA.Number, TableB.Amount, TableC.Amount
FROM (TableA INNER JOIN TableB ON TableA.Number = TableB.Number)
INNER JOIN TableC ON TableA.Number = TableC.Number

TableC has 168 records where the number exists with in tableA So I
know I should at least get a result with 168 records or
more. With the above select statement I get a result of 145.

Any ideas on what I am doing wrong?

Yes. You need to change INNER JOIN to LEFT JOIN in both places

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get
a quicker response by posting to the newsgroup.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

as soon as I did that I ended up with 145 records when I should have 182...
I ended up adding tableB amount 0 or tableC.amount 0 and that seemed to
work, thanks for the idea of adding a where clause...

"ToniS" wrote:

I am having a select problem, below is what I am after.

TableA
Number
1
2
3
4
5

TableB
Number Amount
2 100
3 50
4 175


TableC
Number Amount
2 180
5 189


Results looking for

Number TableB Amount TableC Amount
2 100 180
3 50
4 175
5 189


I would like to select information from tableB and TableC where the number
exists with in TableA

Something along the lines of

SELECT TableA.Number, TableB.Amount, TableC.Amount
FROM (TableA INNER JOIN TableB ON TableA.Number = TableB.Number)
INNER JOIN TableC ON TableA.Number = TableC.Number

TableC has 168 records where the number exists with in tableA So I know I
should at least get a result with 168 records or
more. With the above select statement I get a result of 145.

Any ideas on what I am doing wrong?

Thanks
Toni