How to INNER JOIN 4 tables?

Question

I had to create an addressbook application connecting a database to a java gui. I'm working on my search button right now and almost got it but cant figure out how to connect all four tables. I need to search by a last name and then I want it to show all related information from that person.

So far this is what I have and it seems to be working but when I try to INNER JOIN emailAddresses and phoneNumbers I get a syntax error.

resultSet =
    statement.executeQuery( "SELECT * FROM names INNER JOIN addresses ON names.personID = addresses.personID WHERE lastName LIKE '%" + last + "%' ");
            resultSet.next();
            jTextField1.setText(resultSet.getString("firstName"));
            jTextField2.setText(resultSet.getString("lastName"));
            jTextField3.setText(resultSet.getString("address1"));
            jTextField4.setText(resultSet.getString("address2"));
            jTextField5.setText(resultSet.getString("city"));
            jTextField6.setText(resultSet.getString("state"));
            jTextField7.setText(resultSet.getString("zipcode"));
            jTextField8.setText(resultSet.getString("phoneNumber"));
            jTextField9.setText(resultSet.getString("emailAddress"));

You should edit the question and provide sample data and desired results. I doubt that a `JOIN` is what you really want. — Gordon Linoff, Oct 19 '16 at 10:59
If just join multiple tables is your requirement then there exists lots of tutorials in google. — Esty, Oct 19 '16 at 11:04

score 7 · Accepted Answer · edited May 23 '17 at 10:32

7

You'll need to add brackets and use * rather than % as the wildcard.
I haven't tested, but this should work:

"SELECT     *
FROM    ((names INNER JOIN addresses ON names.personID = addresses.personID)
                INNER JOIN emailadresses ON names.personID = emailadresses.PersonID)
                INNER JOIN PhoneNumbers ON names.PersonID = PhoneNumbers.PersonID
WHERE   lastName LIKE '*" & last & "*'"

Saying it should work - remember to escape the apostrophe in any last name.
Escaping ' in Access SQL

edited May 23 '17 at 10:32

Community

1
1

answered Oct 19 '16 at 12:07

Darren Bartrup-Cook

18,362
1
23
45

Thanks! I feel like I'm almost there its just now I'm getting an INVALID CURSOR STATE error – Mrs.Brightside Oct 19 '16 at 12:21
I wasn't able to add the '&' I would get compiler error as bad operand type – Mrs.Brightside Oct 19 '16 at 12:21
That must be the Java - sorry, should have said I wrote it as a VBA text string. I guess in Java you use the `+` (you can in VBA but may end up with the two parts being summed rather than concatenated). – Darren Bartrup-Cook Oct 19 '16 at 12:29

score 3 · Answer 2 · answered Oct 19 '16 at 11:05

3

SELECT * FROM names A
INNER JOIN address B ON A.personID = B.personID
INNER JOIN emailAddress C ON A.personID = C.personID
INNER JOIN phoneNumbers D ON A.personID = D.personID

answered Oct 19 '16 at 11:05

Esty

1,882
3
17
36

1

Although this code may help to solve the problem, it doesn't explain _why_ and/or _how_ it answers the question. Providing this additional context would significantly improve its long-term educational value. Please [edit] your answer to add explanation, including what limitations and assumptions apply. – Toby Speight Oct 19 '16 at 16:13

score 2 · Answer 3 · answered Oct 19 '16 at 11:08

2

try this SQL Command, joining four tables is the same as joining one table or joining many more tables:

SELECT firstName, lastName, address1, address2, city, state, zipcode, phoneNumber, emailAddress FROM names INNER JOIN addresses ON names.personID = addresses.personID INNER JOIN emailadresses ON names.personID = emailadresses.PersonID INNER JOIN PhoneNumbers ON names.PersonID = PhoneNumbers.PersonID WHERE lastName LIKE '%parameter%'

answered Oct 19 '16 at 11:08

Zeina

1,573
2
24
34

I tried this but for some reason I keep getting a syntax error. its driving me crazy I don't know why its doing that. It just says ACCESS DATABSE (missing operator) syntax error - and lists my select statement- I have re checked it 5 times and everything looks fine – Mrs.Brightside Oct 19 '16 at 11:21
do you have a variable that is limited in number of characters? for example NVARCHAR(200) that need to be set to a larger value NVARCHAR(4000)? just guessing! – Zeina Oct 19 '16 at 12:04

How to INNER JOIN 4 tables?

3 Answers3