query logic for selecting records from 3 tables

gbnorton
gbnorton used Ask the Experts™
on
I have tables A, B, and C each having the field PartNumber.

tables B and C also have the field DieSize.

I have a report that will use this query to display the record results on a label.

In English, this is the query:

Find the match in TableA.PartNumber and (TableB.PartNumber or TableC.PartNumber)
Return the DieSize.

There should be only one match.

Display PartNumber and DieSize on the report.

Thanks,
Brooks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi Brooks,

How is the relationship of your Tables?
Is table A linked to table B and C?

You can create a Query, link the tables together (A one-to-many to B and C) and show the Table B.DieSize and Table C.DieSize and see which one returns.

HTH,
Daniel
Top Expert 2011

Commented:
- try this:

SELECT TableA.PartNumber, TableB.DieSize, TableC.DieSize
FROM TableA, TableB, TableC
WHERE TableA.PartNumber = TableB.PartNumber
AND TableB.PartNumber = TableC.PartNumber

Author

Commented:
danishani:
My query looks like this;
Select TableA.PartNumber, TableB.Die_Size, TableC.DieSize
From (TableA INNER JOIN TableB ON TableA.PartNumber= TableB.PartNumber) INNER JOIN TableC ON TableC.PartNumber
WHERE TableA.PartNumber = "12345";

It returns no records.  Can you paste your sql statement for your suggestion?
Thanks,
Brooks

OP_Zaharin:
It looks like your suggestion requires the partnumber to be in both TableB and TableC?  The partnumber should exist in either one or the other.
Thanks,
Brooks
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Top Expert 2011

Commented:
- if partnumber exist either in B or C then:

SELECT TableA.PartNumber, TableB.DieSize, TableC.DieSize
FROM TableA, TableB, TableC
WHERE TableA.PartNumber = TableB.PartNumber
OR TableA.PartNumber = TableC.PartNumber
WHERE TableA.PartNumber = '12345';

OR

SELECT TableA.PartNumber, TableB.Die_Size, TableC.DieSize
FROM TableA
INNER JOIN TableB ON TableA.PartNumber = TableB.PartNumber
INNER JOIN TableC ON TableA.PartNumber = TableC.PartNumber
WHERE TableA.PartNumber = '12345';
Well its not possible with Inner Join, try Left Join:

Select TableA.PartNumber, TableB.Die_Size, TableC.DieSize
From (TableA LEFT JOIN TableB ON TableA.PartNumber= TableB.PartNumber) LEFT JOIN TableC ON TableC.PartNumber
WHERE TableA.PartNumber = "12345";

HTH,
Daniel

Author

Commented:
That worked!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial