INNER JOIN multiple tables

[kristinac]

I am trying to join 4 tables. I need almost all the information from all 4 tables. I have

Addresses
Streets
Segments
ESNs

Addresses, Streets, and Segments join with a StreetId, and the ESN table can join the Segments table with ESNId.

I have written so many SQL statements, yet none have worked. I keep getting an error saying I am missing an operator or that there is something wrong with my join statement.

Help?

 

[swampBoogie]

select <column-list>
from Addresses as A, Streets as st, Segments as s, ESNs as E
where a.StreetId = st.StreetId
  and st.StreetId = s.StreetId
  and s.ESNId = E.ESNId

 

[kristinac]

Thanks, but I neglected to mention that I have other WHERE clauses to add. That's why I'm trying to use an INNER JOIN. The statement I'm using at the moment is:

SELECT Addresses.*, Segments.*, ESNs.* FROM Addresses INNER JOIN (Segments INNER JOIN ESNs ON Segments.ESNId = ESNs.Id) ON Addresses.StreetId = Segments.StreetId WHERE Addresses.Address = ? AND Addresses.Address BETWEEN Segments.BAddress AND Segments.EAddress

It's working, however it only joins 3 of the tables. I need info from the 4th table too. But as soon as I try and join that one in there it doesn't work...

 

[Rmck87]

Does this statement right here ( the one you typed most recently ) work perfectly? With the question mark?




One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[Rmck87]

SELECT Addresses.*, Segments.*, ESNs.*, Streets.*
FROM Addresses INNER JOIN (Segments INNER JOIN ESNs ON Segments.ESNId = ESNs.EsnId) ON Addresses.StreetId = Segments.StreetId Streets INNER JOIN Segments as [seg] ON streets.streetid = segments.streetid
WHERE Addresses.Address = ? AND Addresses.Address BETWEEN Segments.BAddress AND Segments.EAddress


In your from clause you have ' Segments.ESNId = ESNs.id '
Shouldn't it be ' Segments.ESNId = ESNs.ESNId ' ??????

I am assuming that you forgot to put ESNId in the second part of the clause. I hope this works for you. You might not have been putting 'Segments' into another variable, so i put it into [seg] for you. Try it out.

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[Rmck87]

I'm sorry i just found a typo. There should be a comma after segments.streetid. So make sure that gets in there! Let me know how it works.

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[swampBoogie]

Thanks, but I neglected to mention that I have other WHERE clauses to add.

You don't need to use the inner join syntax because of that. Just add the conditions.

select <column-list>
from Addresses as A, Streets as st, Segments as s, ESNs as E
where a.StreetId = st.StreetId
  and st.StreetId = s.StreetId
  and s.ESNId = E.ESNId
  and Addresses.Address = ? 
  AND Addresses.Address BETWEEN Segments.BAddress 
  AND Segments.EAddress

The inner join syntax in Jet SQL is a disgrace as you must specify in which order the joins should be performed. Specifying the inner join in the where clause gets rid of that problem.

 

[kristinac]

Yes the statement with the ? works, (the ? becomes a value later in my code). And no, I meant to put Segments.ESNId = ESNS.Id. Those are the two fields that relate even tho the names are different.

Thank you so much for all the input. I'm going to try your statement in just a minute. I'll let you know how it works.

Where does that , go? Between Segments.StreetId and Streets INNER JOIN...?

Oh and swampBoogie I will try yours too. I tried to do it with just a long WHERE clause like that and my pgm didn't like it.

Thanks!

 

[kristinac]

Okay I've tried both of your suggestions and here's what I'm getting -

RMCK87 - When I use your SELECT stmt I get an error saying there is something wrong with my JOIN statement. It doesn't seem to like the "Streets INNER JOIN..." part.

swampBoogie - When I use your SELECT stmt I get a strange error - it says it doesn't recognize Addresses.*, or anything.*, "as a valid field name or expression". Which I don't get because that's what I've been using and it's worked just fine.

I thought I was just typing something wrong but I don't see any typos. I will keep trying!!

 

[Rmck87]

Yes. This is how the from clause should look with the commma in place....

FROM Addresses INNER JOIN (Segments INNER JOIN ESNs ON Segments.ESNId = ESNs.EsnId) ON Addresses.StreetId = Segments.StreetId , Streets INNER JOIN Segments as [seg] ON streets.streetid = segments.streetid


One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[swampBoogie]

As i have used aliases in the from list you should use those when qualifying columns instead of the table names.

 

[kristinac]

That statement still won't work Rmck87. It seems to be the "Streets INNER JOIN..." part.

Thanks swampBoogie - I'm still trying with your too. Now I'm getting a "no value given for one or more required parameters" error.

Still trying!!

 

[kristinac]

SELECT Addresses.*, Streets.Name, Segments.*, ESNs.* FROM Addresses, Streets, Segments, ESNs WHERE(Addresses.StreetId = Streets.Id)AND Streets.Id = Segments.StreetId AND Segments.ESNId = ESNs.Id AND Addresses.Address = ? AND Addresses.Address BETWEEN Segments.BAddress AND Segments.EAddress

Okay! The above statement seems to be working. Is there any chance I'm not getting the right info? The reason I ask is because I've done a statement with an INNER JOIN and then with just WHERE clauses earlier in this pgm and I received different results. But if I'm getting the right info then this is working!

Thank you so much for all your help!!

 

[Rmck87]

That looks right to me. The real question is.... does it look right to you? lol. I mean is it bringing back the right records that you need?

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[kristinac]

Well yes, LOL, it does seem to be getting the right records. I think it's working nicely! And it was nice to skip the INNER JOINs, they can be confusing.

 

[Rmck87]

They sure can be confusing.

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[Rmck87]

give us an update sometime, and let us know how we have helped you or if you still need help.

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[kristinac]

I figured out another statement that will join the tables...

SELECT ESNs.*, Segments.*, (Streets.Name & ' ' & Streets.Type) as Street, Streets.*, Addresses.* FROM ESNs, Segments, Streets, Addresses WHERE ESNs.Id = Segments.ESNId AND Segments.StreetId = Streets.Id AND Addresses.StreetId = Streets.Id AND Addresses.Address BETWEEN Segments.BAddress AND Segments.EAddress

I just ordered the tables all backwards from the way I was doing it. It seems to be giving me the proper information too.

Thanks again for all your help!!

 

[Rmck87]

Your welcome. And Happy Coding!!

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.