Query error when testing in Database SQL

Question

I have this query and appearently it's faulty? I'm trying to join fices with mems so I can have the ficeID along with all the results from mems(These queries work individually). What am I doing wrong?

  SELECT * 
    FROM mems 
   WHERE deleted <> -1 
ORDER BY sort_mems 
         LEFT JOIN SELECT ficeID 
                     FROM fices

Result:

 #1064 - 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 'LEFT JOIN SELECT ficeID FROM offices LIMIT 0, 30' at line 1

Join statement should always be before the where clause and after from — G one
– G one, Commented Feb 18, 2014 at 14:29

Alexander · Accepted Answer · 2014-02-18 14:53:08Z

1

You have JOIN clause after ORDER BY. You should place it in FROM.
I suggest you define condition of a LEFT JOIN

Also, I suggest you surround you temp tables with brackets:

  SELECT m.*, t1.officeID
    FROM members m
         LEFT JOIN offices t1
                   ON m.memberID = t1.memberID
   WHERE m.deleted <> -1 
ORDER BY m.sort_membername;

edited Feb 18, 2014 at 14:53

answered Feb 18, 2014 at 14:28

Alexander

3,1992 gold badges21 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

TopDecker Over a year ago

I get this: #1248 - Every derived table must have its own alias

TopDecker Over a year ago

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 'WHERE deleted <> -1 ORDER BY sort_mems LIMIT 0, 30' at line 1

Alexander Over a year ago

@TopDecker provide SHOW CREATE TABLE mems; and SHOW CREATE TABLE fices; outputs, and I'll give your the query you need

TopDecker Over a year ago

The outputs are huge though.

TopDecker Over a year ago

Hmm I get Column 'deleted' in where clause is ambiguous that means we're close I guess..

|

bhamby · Accepted Answer · 2014-02-18 14:34:59Z

1

Yes, you have the LEFT JOIN in the wrong spot (it should go after your FROM clause, and you also seem to be missing a join criteria (the ON part, this tells the database how these tables are related):

SELECT *
FROM mems m
LEFT JOIN fices f
  ON  m.??? = f.???
WHERE deleted <> -1 
ORDER BY sort_mems

edited Feb 18, 2014 at 14:34

answered Feb 18, 2014 at 14:28

bhamby

15.6k1 gold badge49 silver badges67 bronze badges

3 Comments

bhamby Over a year ago

Well, for one, I had a typo... one of those should have been an f. However, the ON clause is a statement that lets you define how the two tables are related. For example, if you had an employee table, and each employee belonged to a department in a company, and you wanted to get data from both tables, you might do something like... SELECT ... FROM employee e JOIN department d ON e.dept_id = d.id WHERE ...

TopDecker Over a year ago

Ohhh I get it now i'll try that and report in in a sec

TopDecker Over a year ago

I tried it and this was my result: Column 'deleted' in where clause is ambiguous

Collectives™ on Stack Overflow

Query error when testing in Database SQL

2 Answers 2

8 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related