CODEWITHSUNDEEP
sqlsql-servert-sql
Mirza
Mirza

Reputation: 21

An expression of non boolean type specified in a context where a condition is expected - on INNER JOIN

SELECT 
    study.Name, study.Type, study.Weight, study.Height, study.Age, measurement.*, ssf.*
from 
    Admin.Table_Study as study
INNER JOIN 
    (Admin.Table_Data as measurement
INNER JOIN
    Admin.Table_ComboBox as ssf)
ON
    study.UID = ssf.UID
where
    measurement.IsMeasurement = 1

I am trying to get the Data from multiple tables using INNER JOIN. I tried multiple solutions but couldn't get to the root cause.

P.S: Admin.Table_Study and Admin.Table_ComboBox has a PK FK relation over UID.

Upvotes: 1

Views: 1100

Answers (5)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

Admin.Table_Data dont have any connection with any of the other table thats why i skipped ON. What should i do in that case?

It sounds like what you want is a cross join. That is also known as a cartesian join (but that's not what SQL chose to name it) where you join every row from the left table to every row from the right table:

SELECT 
    study.Name, study.Type, study.Weight, study.Height, study.Age, measurement.*, ssf.*
from 
    Admin.Table_Study as study
CROSS JOIN 
    Admin.Table_Data as measurement
INNER JOIN
    Admin.Table_ComboBox as ssf
ON
    study.UID = ssf.UID
where
    measurement.IsMeasurement = 1

Because it's all <=> all, CROSS JOIN doesn't have an ON clause.

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

In SQL Server, the INNER JOIN has no (..) parenthesis & it has one ON clause :

For instance you have two INNER JOINs, but only one ON clause, so remaining JOINs expecting ON clause : 

SELECT study.Name, study.Type, study.Weight, study.Height, study.Age, measurement.*, ssf.*
FROM [Admin].Table_Study AS study INNER JOIN 
     [Admin].Table_Data AS measurement
     ON measurement.col = study.col INNER JOIN
     [Admin].Table_ComboBox AS ssf
     ON study.UID = ssf.UID;

EDIT : If [Admin].Table_Data has no connection with other tables you can use UNION ALL :

SELECT study.Name, study.Type, study.Weight, study.Height, study.Age
FROM [Admin].Table_Study AS study INNER JOIN
     [Admin].Table_ComboBox AS ssf
     ON study.UID = ssf.UID;
UNION ALL
SELECT NULL, NULL,  NULL, NULL,  NULL, COL1, COL2
FROM [Admin].Table_Data AS td

Upvotes: 1

Premjit Chowdhury
Premjit Chowdhury

Reputation: 76

Please check this expression. in Sql-server no need of giving () in joins.

SELECT 
    study.Name, study.Type, study.Weight, study.Height, study.Age, measurement.*, ssf.*
from 
    Admin.Table_Study as study
INNER JOIN 
    Admin.Table_Data as measurement
ON 
    study.ID = measurement.ID   --******this is what you missed
INNER JOIN
    Admin.Table_ComboBox as ssf
ON
    study.UID = ssf.UID
where
    measurement.IsMeasurement = 1

Upvotes: 0

Kinnison84
Kinnison84

Reputation: 176

In your query the way to join study and SSF is defined. But how to join measurement and ssf is not defined. Also, you do not define how to join the measurement.

You could greatly simplify this query if you write it like below, but you will need to specify the join conditions that are missing:

SELECT 
    study.Name, study.Type, study.Weight, study.Height, study.Age, measurement.*, ssf.*
from 
    Admin.Table_Study as study

INNER JOIN 
    Admin.Table_Data as measurement
ON *********************    

INNER JOIN
    Admin.Table_ComboBox as ssf
ON
    study.UID = ssf.UID

where
    measurement.IsMeasurement = 1

Upvotes: 1

Ilyes
Ilyes

Reputation: 14928

An INNER JOIN need an ON, need a condition to join

SELECT 
    study.Name, study.Type, study.Weight, study.Height, study.Age, measurement.*, ssf.*
from 
    Admin.Table_Study as study INNER JOIN Admin.Table_Data as measurement
      ON study.ID = measurement.ID -- that what  you miss
    INNER JOIN Admin.Table_ComboBox as ssf
      ON study.UID = ssf.UID
where
    measurement.IsMeasurement = 1

Upvotes: 1

Related Questions