MySQL syntax error when joining

Question

I'm currently in the process of teaching myself to use MySQL. Problem is, self-teaching isn't always easy, and I often get stuck. Usually, I try to get myself unstuck, but today is no such day.

I'm using SQLFiddle (http://sqlfiddle.com/) to run my queries up against a database, until I find some more permanent software solution.

My problem is in my query.

(SELECT customer.customername, the_account.balance, branch.branch_name    
   FROM customer, has_account, the_account, branch   
  WHERE customer.ssn = has_account.assn
    AND has_account.ano = the_account.accountno
    AND the_account.branchid = branch.branchid) AS a
JOIN
(SELECT customer.customername, loan.amount, branch.branch_name 
   FROM customer, has_loan, loan, branch
  WHERE customer.ssn = has_loan.lssn
    AND has_loan.lno = loan.loanno
    AND loan.branchid = branch.branchid) AS b
ON has_account.assn = has_loan.lssn;

Now, I'm sure the syntax-error is painfully obvious to someone who knows SQL by heart, but I'm just stuck in a rut, and can't seem to dig myself out.

My understanding was that I could at least attempt to join any two tables together like this.

For a complete overview of my database, the query and such, go to http://sqlfiddle.com/#!2/1943b/85.

And please, be gentle. I know this is probably a rookie-mistake, but learning this stuff on your own isn't as easy as you'd think.

UPDATE1:

So after trying the suggested:

(SELECT has_account.assn as assn, customer.customername, the_account.balance, branch.branch_name    
   FROM customer, has_account, the_account, branch   
  WHERE customer.ssn = has_account.assn
    AND has_account.ano = the_account.accountno
    AND the_account.branchid = branch.branchid) AS a

JOIN

(SELECT has_loan.lssn as lssn, customer.customername, loan.amount, branch.branch_name 
   FROM customer, has_loan, loan, branch
  WHERE customer.ssn = has_loan.lssn
    AND has_loan.lno = loan.loanno
    AND loan.branchid = branch.branchid) AS b

ON b.lssn = a.assn;

Which looked good, and made all the sense in the world to me, SQLFiddle is complaining:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS a JOIN (SELECT has_loan.lssn as lssn, customer.customername, loan.amount, bra' at line 5: (SELECT has_account.assn as assn, customer.customername, the_account.balance, branch.branch_name FROM customer, has_account, the_account, branch WHERE customer.ssn = has_account.assn AND has_account.ano = the_account.accountno AND the_account.branchid = branch.branchid) AS a JOIN (SELECT has_loan.lssn as lssn, customer.customername, loan.amount, branch.branch_name FROM customer, has_loan, loan, branch WHERE customer.ssn = has_loan.lssn AND has_loan.lno = loan.loanno AND loan.branchid = branch.branchid) AS b ON b.lssn = a.assn

(Just in case someone wanted the error report in its entirety!)

MySQL doesn't support FULL OUTER JOIN. You have to emulate it with a LEFT JOIN Unioned with a RIGHT JOIN. — , Oct 20 '14 at 07:43
The JOIN-type isn't really what's bothering me. It's that neither of the tables (A and B) will join whatever I seem to do. — ViRALiC, Oct 20 '14 at 07:47
Try this (MySQL don't have full joins) http://stackoverflow.com/questions/4796872/full-outer-join-in-mysql — Stefan Horvat, Oct 20 '14 at 07:48
The `JOIN` is built on `has_account.assn = has_loan.lssn;`. However, you aliased the two queries as `A` and `B`. Try changing it to `a.assn = b.lssn;` — SchmitzIT, Oct 20 '14 at 07:48
@SchmitzIT I did try that, thank you. I actually thought that might be the problem too, but it still throws me the syntax-error: http://sqlfiddle.com/#!2/1943b/89 (I'd print out the entire error, but it would take more space than the commenting box will allow!) — ViRALiC, Oct 20 '14 at 07:53
You're also missing a SELECT statement on the outside of the two queries. I.e. `SELECT * FROM (a table) AS A JOIN (b table) AS b ON a.id = b.id` — SchmitzIT, Oct 20 '14 at 07:53

Jevgenijs Vaikulis · Accepted Answer · 2014-10-20T08:07:20.780

3

select * from
(SELECT has_account.assn as assn, customer.customername, the_account.balance, branch.branch_name    
   FROM customer, has_account, the_account, branch   
  WHERE customer.ssn = has_account.assn
    AND has_account.ano = the_account.accountno
    AND the_account.branchid = branch.branchid) AS a
JOIN
(SELECT has_loan.lssn as lssn, customer.customername, loan.amount, branch.branch_name 
   FROM customer, has_loan, loan, branch
  WHERE customer.ssn = has_loan.lssn
    AND has_loan.lno = loan.loanno
    AND loan.branchid = branch.branchid) AS b
ON b.lssn = a.assn;

Personally i dont like such joins, you can try it in simple way (im not sure joins conditions are correct, please check):

SELECT
  customer.customername,
  the_account.balance,
  branch.branch_name,
  loan.amount
FROM customer
JOIN has_account on has_account.assn = customer.ssn
JOIN the_account on the_account.accountno = has_account.ano
JOIN branch on branch.branchid = the_account.branchid
JOIN has_loan on has_loan.lssn = customer.ssn
JOIN loan on loan.loanno = has_loan.lno;

edited Oct 20 '14 at 08:07

answered Oct 20 '14 at 07:48

Jevgenijs Vaikulis

676
4
7

2

This looks like a good answer to the question, could you perhaps update your answer with an explanation though? The OP is clearly learning, just an answer won't help his studies too much... – HoneyBadger Oct 20 '14 at 07:50
Ah, I have to select everything FROM those two tables combined? Neat! Didn't know that. It works now. Thanks for the help! – ViRALiC Oct 20 '14 at 07:56
1

@ViRALiC - Well, not necessarily everything. Just the columns you actually need. But yes, you need to have an "outer" select as well to identify what exactly it is you want to pull. – SchmitzIT Oct 20 '14 at 08:00

MySQL syntax error when joining

1 Answers1