Many to one join

[Bobby100]

Hi, Im trying to write a query that will join one table to another table in a many to one relationship.

In TableA I have three columns... 'idnumber1', 'idnumber2', 'idnumber3'.



Some rows in tableA will have all three columns populated, others only one or two columns. For some reason some assets have been assigned multiple id numbers.

I want to join this table to a table that contains further information related to the idnumber.

TableB has an 'idnumber' column and a 'description' column.

So far my query looks like this...

SELECT TABLEA.ID1, TABLEB.DESC
FROM TABLEA LEFT JOIN TABLEB ON TABLEA.ID1 = TABLEB.IDNUMBER
LEFT JOIN TABLEA.1D2 = TABLEB.IDNUMBER;

Am I missing a comma or an 'AND' in between the two join statements?

I can't seem to get the syntax correct.

Any help would be appreciated,

Thanks.

[ItsMe]

Maybe you are looking for something like this ...

SELECT TABLEA.ID1, TABLEB.DESC
FROM TABLEA LEFT JOIN TABLEB ON (TABLEA.ID1 = TABLEB.IDNUMBER) AND
(TABLEA.1D2 = TABLEB.IDNUMBER);

[Bobby100]

That returns all the idnumbers from tableA but does not show any info from the 'desc' column in tableB. Should I replace the 'AND' with an 'OR'?

[ItsMe]

...Should I replace the 'AND' with an 'OR'?

Probably not. All I did was interpret the SQL you posted and implement the two JOINS you seemed to be attempting to create.

What it is you are trying to do with your data and what your data structure is like are still a mystery to me. It seems you do not have Normalized data and this is causing you grief when attempting to build queries. It is likely you will not find an answer to your question until after you normalize your data.

Perhaps reviewing reference material regarding entity relational diagrams would be a benefit.
https://www.youtube.com/watch?v=-fQ-bRllhXc

[Bobby100]

Yes it would be nice if the data was structured in a more normalized way... its legacy data however and comes with its issues...

Thanks for your help but the sql query you posted doesn't return any info from the second table.. replacing 'AND' with 'OR' does return data from the second table.

[ItsMe]

Well, I can only help with things I understand. I took another look to see if there might be another approach. Maybe you desire something along the lines of the following. Which I believe would the same as changing LEFT to RIGHT in my previous post.

SELECT TABLEB.IDNUMBER, TABLEB.DESC, TABLEA.ID1, TABLEA.ID2
FROM TABLEB LEFT JOIN TABLEA ON (TABLEB.IDNUMBER = TABLEA.ID2) AND
(TABLEB.IDNUMBER = TABLEA.ID1);

[John_G]

Try it without the joins. You then have a Cartesian product, with the total number of records = Records in TableA X Records in TableB

Now, any one of the three ID fields in your TableA could match the ID in tableB, so you have as criteria:

(TABLEB.IDNUMBER = TABLEA.ID1) OR (TABLEB.IDNUMBER = TABLEA.ID2) OR (TABLEB.IDNUMBER = TABLEA.ID3)

When you run it, this might result in duplicates, so you can avoid that with Select DISTINCT.

Your query might look something like this:

SELECT DISTINCT TABLEA.ID1, TABLEB.DESC
FROM TABLEA, TABLEB
WHERE TABLEA.ID1 = TABLEB.IDNUMBER OR TABLEA.ID2 = TABLEB.IDNUMBER OR TABLEA.ID3 = TABLEB.IDNUMBER

I ran a trial of it and it worked perfectly.

[Bulzie]

Also, why not use the Query Designer, pop in the 2 tables, drag the line between the 2 key fields, add output fields and see what you get. You can then open in SQL code to see how it writes the code.