CODEWITHSUNDEEP
sql
Jobs
Jobs

Reputation: 3377

multiple table join error, with expected

Error:

``with'' expected but identifier indv found: indv join com

Code:

select temp.CAND_NAME, count(*)
from 
(
indv join com
on indv.OTHER_ID = com.CMTE_ID

 join can

on indv.CMTE_ID = can.CAND_PCC) as temp

group by temp.CAND_ID
having temp.CAND_ID = "P00003392" or 
temp.CAND_ID = "P60006111" or 
temp.CAND_ID = "P60007168" or temp.CAND_ID = "P80001571"

What could be wrong?

What I'm doing:

I have three tables:

indv, com, and can.

I want to join all three, and query can.CAND_NAME as well as count(*)

How do I want to join?

2 things:

  1. indv.OTHER_ID = com._CMTE_ID
  2. indv.CMTE_ID = can.CAND_PCC

Upvotes: 0

Views: 64

Answers (3)

Buwaneka Sumanasekara
Buwaneka Sumanasekara

Reputation: 663

Check this out 

SELECT can.CAND_NAME,COUNT(*)
FROM indv inner join com
on indv.OTHER_ID=com._CMTE_ID
inner join can 
on indv.CMTE_ID=com.CAND_PCC
WHERE CAND_ID IN ('P00003392', 'P60006111', 'P60007168, 'P80001571')
group by can.CAND_NAME

User 'IN' instead of 'OR' . This should be work as you expected.

Upvotes: 1

Felix Pamittan
Felix Pamittan

Reputation: 31879

You could simplify this as:

SELECT cn.CAND_NAME, COUNT(*)
FROM indv i
INNER JOIN com cm
    ON i.OTHER_ID = cm.CMTE_ID
INNER JOIN can cn
    ON cn.i.CMTE_ID = cn.CAND_PCC
WHERE
    cn.CAND_ID IN ('P00003392', 'P60006111', 'P60007168', 'P80001571')
GROUP BY
    cn.CAND_ID, cn.CAND_NAME
  • The multiple OR conditions can be written as IN.
  • Use a meaningful alias to improve readability.
  • If you're using other rdbms except for MySQL, you need to put non-aggregate columns in the GROUP BY clause.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269693

The parentheses and alias are not correct. I would write this as:

select CAND_NAME, count(*)
from indv join
     com
     on indv.OTHER_ID = com.CMTE_ID join
     can
     on indv.CMTE_ID = can.CAND_PCC
where CAND_ID IN ('P00003392', 'P60006111', 'P60007168, 'P80001571')
group by CAND_ID;

Notes:

  • The parentheses with the table alias are not correct.
  • You should use appropriate table names for the columns. I don't know what they are.
  • It is better to filter before the group by using where, rather than afterwards using having.
  • It is better to use in rather than a bunch of or expressions.

Upvotes: 2

Related Questions