Inner joins

[chellert]

The 'fieldName' table has an ID and DivName for the Division List
The 'members' table has a Division1 and Division2 using the fieldName ID as entries.

My SQL Statement I need to list two columns one for Division1 and Divison2 but when I run this SQL statement, I get two lines for one member.


SELECT DivName, DivName FROM members INNER JOIN fieldName ON members.division1=fieldName.id OR members.division2=fieldName.id WHERE members.division1=3 OR members.division2=6

how to I run this SQL and one entry for each member?

[Old Pedant]

Use SELECT DISTINCT instead of just SELECT.

CAUTION: That only works if you aren't SELECTing fields from fieldname that would cause multiple distinct rows.

If you are SELECTing only fields from the members table it will work fine.

[chellert]

Unfortunately, I need to use the actual values from the fieldNames table to populate my query instead of using numbers that are stored in the members table.

fieldName table
ID, Name
1, Case Track
2, Business History
3, Accounting

members table
ID, Name, Primary, Secondary
1, Chris Ell, 2, 3
2, Bob Frank, 1, 2
3, John Smith, 3, 1

When I query and choose Primary=1 or Secondary=3, I want to see:

ID, Name, Primary, Secondary
2, Bob Frank, Case Track, Business History
1, Chris Ell, Business History, Accounting

What happens with either Select or Select Distinct, I get the following

ID, Name, Primary, Secondary
2, Bob Frank, Case Track, Case Track
2, Bob Frank, Business History, Business History
1, Chris Ell, Business History, Business History
1, Chris Ell, Accounting, Accounting

[Fou-Lu]

Join the fieldName table on twice instead:

SELECT m.id, m.name, fn1.name AS "primary", fn2.name AS secondary
FROM members m
INNER JOIN fieldName fn1 ON fn1.id = m.primary
INNER JOIN fieldName fn2 ON fn2.id = m.secondary
WHERE m.primary = 1 OR m.secondary=3

You've kinda used two different structures here, so I just went with what was in the last post.

[chellert]

Thank you that worked.