Getting similar data from 2 tables for the same field

Question

I have 3 tables:

WireEnd1 (WireNo, Terminal1)
WireEnd2 (WireNo, Terminal2)
Terminals (Terminal, InternalPN)

The relations are between Terminal=Terminal1 and Terminal=Terminal2.

I'm trying to create a query which returns complete info:

SELECT 
    W1.WireNo, W1.Terminal1, T.InternalPN as InternalPN1, 
    W2.Terminal2, T.InternalPN as InternalPN2
from  
    WireEnd1 as W1 
inner join 
    Terminals as T on W1.Terminal1 = T.Terminal
inner join 
    WireEnd2 as W2 on W2.Terminal2 = T.Terminal;

Unfortunately it is not working. I've also tried some subqueries...not working.

Thank you for the replies.

Can you please post some sample data? Do you have a Terminal in all three tables? — Joseph B
– Joseph B, Commented Aug 13, 2014 at 19:50
define 'not working'....are you getting an error, or is it returning the results you don't want? Also, please let us know which database you are using. — Twelfth
– Twelfth, Commented Aug 13, 2014 at 19:51
You need to clarify your desired result, how is the WireEnd1` table related to the WireEnd2 table? — Lamak
– Lamak, Commented Aug 13, 2014 at 19:53
@Jospeh B A wire has 2 ends. Suppose they are different. Terminals table is the correspondence between external part number and internal part number. — FlawlessData
– FlawlessData, Commented Aug 13, 2014 at 20:02
@Twelfth In MS ACcess, it doesn't return anything for Terminal2 and InternalPN2 — FlawlessData
– FlawlessData, Commented Aug 13, 2014 at 20:03

Joseph B · Accepted Answer · 2014-08-13 20:32:35Z

2

Try this:

SELECT 
    W1.WireNo, W1.Terminal1, T1.InternalPN as InternalPN1, 
    W2.Terminal2, T2.InternalPN as InternalPN2
from  
    WireEnd1 as W1 
inner join 
    WireEnd2 as W2 on W2.WireNo = W1.WireNo
inner join 
    Terminals as T1 on W1.Terminal1 = T1.Terminal
inner join
    Terminals as T2 on W2.Terminal2 = T2.Terminal;

Basically, you join tables WireEnd1 and WireEnd2 to get the data for the same wire. Then, you join each table with a copy of the Terminals table to get the InternalPN data from the Terminals table. You cannot join both the tables to a single copy of it, because the same row will not be equal to both Terminal1 and Terminal2. Therefore, you had not been getting any output.

edited Aug 13, 2014 at 20:32

answered Aug 13, 2014 at 20:27

Joseph B

5,7291 gold badge17 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

cHao Over a year ago

A terminal very well might match both ends, if it has a wire looping back to itself. (Which would probably be an error, at least in cases outside of high-frequency RF circuits. But the database certainly isn't going to prevent it.)

Joseph B Over a year ago

@cHao That's correct. However, the fact that the OP did not get any data with the original query indicates that currently there is no such configuration.

Collectives™ on Stack Overflow

Getting similar data from 2 tables for the same field

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related