CODEWITHSUNDEEP
sql-serversql-server-2008
Jad
Jad

Reputation: 43

Query junction table-many to many relationship

I'm trying to run a query for a many-many relationship, I created a junction table to do that. Now I'm having issues getting no results when I'm expecting to return 3 rows for each of indexes 122,123,124, can anyone point out my errors, thank you 

SELECT  *  FROM [Moldingdata].[dbo].[mach_part_junction] ORDER BY [machinename] ASC

SELECT  MachineList.machinename ,JDEPARTIMGLU.Jde_part_num
 from mach_part_junction 
 INNER JOIN MachineList on mach_part_junction.machinename = machinelist.Machine_ID
 INNER JOIN JDEPARTIMGLU on mach_part_junction.machinename = machinelist.Machine_ID
 WHERE mach_part_junction.machinename= 'MM01'

Results:

machinename ndx_jde_part_img
----------- ----------------
MM01        122
MM01        123
MM01        124
MM04        122
MM15        124
MM17        122
MM32        122
MM32        123

(8 row(s) affected)


machinename Jde_part_num
----------- --------------------

(0 row(s) affected)

Upvotes: 0

Views: 83

Answers (2)

Lewis Worley
Lewis Worley

Reputation: 279

When I troubleshoot queries like this I generally do the following:

SELECT * 
FROM mach_part_junction
INNER JOIN MachineList on mach_part_junction.machinename = machinelist.Machine_ID

Then I add the next JOIN:

 SELECT * 
    FROM mach_part_junction
    INNER JOIN MachineList on mach_part_junction.machinename = machinelist.Machine_ID
INNER JOIN JDEPARTIMGLU on mach_part_junction.machinename = machinelist.Machine_ID

then the WHERE (if the tables aren't too big)

That way you can see what's going on.

Upvotes: 0

user6684101
user6684101

Reputation:

The join conditions on your second and third table are the same.

It looks like the third table's join condition is the one that needs to be changed.

Upvotes: 1

Related Questions