CODEWITHSUNDEEP
mysqlsql
ViRALiC
ViRALiC

Reputation: 1549

MySQL syntax error when joining

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!)

Upvotes: 0

Views: 110

Answers (1)

Jevgenijs Vaikulis
Jevgenijs Vaikulis

Reputation: 686

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;

Upvotes: 3

Related Questions