select Statement w/i inner joins

T

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
 
B

Bob Barrows [MVP]

ToniS said:
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
 
T

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
 
B

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
 
T

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top