select Statement w/i inner joins

[ToniS]

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

 

[Bob Barrows [MVP]]

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

 

[ToniS]

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]]

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]

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...