SQL: combining results from join table into one field in a query

I haven't been doing any queries at work for several months because of a new project I was put on so I'm completely spacing on how to do the following:

I have one table named "eJournals", a second named "providerJoins", and a third named "providers".

The fields are as f开发者_Go百科ollows:

eJournals

1. eJournalID
2. Title

providerJoins

1. joinID
2. providerID
3. eJournalID

providers

1. providerID
2. providerName

My question is this, what needs to happen in the query to get something like this:

eJournalID      Title         providerName
1               blah          providerblah
2               blah2         providerblah2
3               blah3         providerblah3
etc....

I'm sure the solution will make me hit my head and say, "oh yeah, duh" but... like I said, I haven't been using sql in months.

You should be able to do something as simple as this:

SELECT J.eJournalID, J.Title, P.ProviderName FROM eJournals J
        JOIN ProviderJoins PJ ON PJ.eJournalID = J.eJournalID
        JOIN Providers P ON P.ProviderId = PJ.ProviderID

You have to have some field relationship between eJournals and providersJoins table to make this work - otherwise there is no way to say which providerName goes with which eJournal.

Given that the relationship is eJournalID, the below should work...

select e.eJournalID, e.Title, p.providerName
from eJournals e, providerJoins pj, providers p
where e.eJournalID = pj.eJournalID
and pj.providerID = p.providerID

I think you want something like this?

SELECT 
    e.eJournalID,
    e.Title,
    p.ProviderName
FROM
    eJournals e
    INNER JOIN providerJoins pj ON e.eJournalID = pj.eJournalId
    INNER JOIN providers p ON pj.providerId = p.providerId

SELECT e.eJournalID, e.Title, p.providerName
FROM eJournals e
INNER JOIN providerJoins j ON j.joinID = e.eJournalID
INNER JOIN providers p ON p.providerID = j.providerID

That ought to do it

Try:

SELECT DISTINCT e.eJournalID, e.Title, p.providerName
FROM eJournals AS e JOIN providerJoins AS pj ON e.eJournalID = pj.eJournalID
JOIN providers AS p ON pj.providerID = p.providerID

Note: "DISTINCT" should remove any duplicate rows in the results.