Error with SQL query in C#: Using SELECT JOIN WHERE OR

Question

SQL is still relatively new to me but I have generated this from research I have done.

This is my sql command:

"SELECT Array_ID 
 FROM Array_Location JOIN 
      Obj_Type.Type_ID ON Array_Location.Obj_Type 
 WHERE (Obj_Type.Object = 'CIRCLE') 
    OR (Obj_Type.Object = 'POLYGON');"

This is my error

An expression of non-boolean type specified in a context where a condition is expected, near 'WHERE'

This is a syntax problem but i'm not sure on the solution

The two tables I have are:

CREATE TABLE [dbo].[Obj_Type] (
[Type_ID] INT  NOT NULL,
[Object]  TEXT NOT NULL,
CONSTRAINT [PK_OBJ_TYPE] PRIMARY KEY CLUSTERED ([Type_ID] ASC)
);

CREATE TABLE [dbo].[Array_Location] (
[Array_ID]   INT NOT NULL,
[Obj_Type]   INT NOT NULL,
[Element_ID] INT NOT NULL,
CONSTRAINT [Array_Location_fk0] FOREIGN KEY ([Obj_Type]) REFERENCES [dbo].[Obj_Type] ([Type_ID]) ON UPDATE CASCADE
);

Thanks

Typo sorry. I do have that included in my code. So still same error — SammyG
– SammyG, Commented Jun 9, 2015 at 9:23
Update your question to reflect the exact code you have used — ThePravinDeshmukh
– ThePravinDeshmukh, Commented Jun 9, 2015 at 9:34

Raging Bull · Accepted Answer · 2015-06-09 09:38:42Z

2

Reason:

You have syntax error in JOIN:

Array_Location JOIN Obj_Type.Type_ID ON Array_Location.Obj_Type

Solution:

SELECT Array_ID 
FROM Array_Location JOIN 
     Obj_Type ON Obj_Type.Type_ID = Array_Location.Obj_Type 
WHERE (CONVERT(nvarchar(50),Obj_Type.Object) = N'CIRCLE') 
   OR (CONVERT(nvarchar(50),Obj_Type.Object) = N'POLYGON');

edited Jun 9, 2015 at 9:38

answered Jun 9, 2015 at 9:23

Raging Bull

18.8k13 gold badges52 silver badges56 bronze badges

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

2 Comments

ThePravinDeshmukh Over a year ago

Good luck with comparing Text and varchar

Raging Bull Over a year ago

@PravinDeshmukh: Ah! you are right! I was no idea about the datatype before OP edited his question. Thanks for pointing it out

ThePravinDeshmukh · Accepted Answer · 2015-06-09 09:33:15Z

1

Here is what you want, You also need conversion to cast TEXT to varchar or nvarchar

SELECT Array_ID 
FROM Array_Location JOIN 
     Obj_Type ON Obj_Type.Type_ID = Array_Location.Obj_Type 
WHERE (Convert(nvarchar(max),Obj_Type.Object) = N'CIRCLE') OR (Convert(nvarchar(max),Obj_Type.Object) = N'POLYGON');

answered Jun 9, 2015 at 9:33

ThePravinDeshmukh

1,92313 silver badges23 bronze badges

Comments

Salah Akbari · Accepted Answer · 2018-10-27 18:27:34Z

1

Not only you have a syntax error in your join, but also I think you are executing two scripts for creating tables together in SQL Server? If you run them together, you will usually get this error:

Foreign key Array_Location_fk0 references invalid table dbo.Obj_Type

Before you correct your join query, you must correct create tables script in this way:

CREATE TABLE [dbo].[Obj_Type] (
[Type_ID] INT  NOT NULL,
[Object]  TEXT NOT NULL,
CONSTRAINT [PK_OBJ_TYPE] PRIMARY KEY CLUSTERED ([Type_ID] ASC)
);

CREATE TABLE [dbo].[Array_Location] (
[Array_ID]   INT NOT NULL,
[Obj_Type]   INT NOT NULL,
[Element_ID] INT NOT NULL,
CONSTRAINT [Array_Location_fk0] FOREIGN KEY ([Obj_Type]) REFERENCES [dbo].[Obj_Type] ([Type_ID]) ON UPDATE CASCADE
);

edited Oct 27, 2018 at 18:27

answered Jun 9, 2015 at 9:53

Salah Akbari

40k10 gold badges85 silver badges115 bronze badges

1 Comment

SammyG Over a year ago

I had just copied it from Visual Studios. But I did have the created properly. Good spot though thanks. I have amended it in the question.

Dika Arta Karunia · Accepted Answer · 2015-06-09 09:20:23Z

0

"SELECT Array_ID FROM Array_Location JOIN Obj_Type.Type_ID ON Array_Location.Obj_Type WHERE (Obj_Type.Object = 'CIRCLE') OR (Obj_Type.Object = 'POLYGON');

you miss parenthesis (')') in end of statement in where

answered Jun 9, 2015 at 9:20

Dika Arta Karunia

4841 gold badge4 silver badges20 bronze badges

Comments

aditya uppuluri · Accepted Answer · 2015-06-09 09:27:12Z

0

You are missing ending close bracket for Obj_Type.Object = 'POLYGON'.SQL Server WHERE clause always expects the condition to be evaluated as boolean value (true/false). Please change the query to the following:

SELECT Array_ID FROM Array_Location JOIN Obj_Type.Type_ID ON Array_Location.Obj_Type WHERE (Obj_Type.Object = 'CIRCLE') OR (Obj_Type.Object = 'POLYGON');

answered Jun 9, 2015 at 9:27

aditya uppuluri

1432 silver badges10 bronze badges

Comments

Michael Yuwono · Accepted Answer · 2015-06-09 09:31:27Z

0

You may miss to address the array_ID table name and also wrong syntax in JOIN. Try this.

 SELECT Array_Location.Array_ID 
 FROM Array_Location 
     INNER JOIN Obj_Type 
     ON Array_Location.Type_ID = Obj_Type.Type_ID 
 WHERE (Obj_Type.Object = 'CIRCLE') OR (Obj_Type.Object = 'POLYGON')

edited Jun 9, 2015 at 9:31

answered Jun 9, 2015 at 9:23

Michael Yuwono

2,6272 gold badges17 silver badges34 bronze badges

Collectives™ on Stack Overflow

Error with SQL query in C#: Using SELECT JOIN WHERE OR

6 Answers 6

2 Comments

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

2 Comments

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related