Remove some lines from query (1 Viewer)

E

elly.khanlar

Registered User.

Local time

Today, 08:53

Joined

Oct 3, 2019

Messages

23

• Oct 21, 2019

• #1

Hi everyone, I have a make a union query which I am trying to union two tables together.
In one of the tables, I don't want two things to be included in two fields in Table A that contain certain criteria.

Here is my SQL which is not working and I get this error" Undefined function "Where" in expression.

here is my

SELECT field 1, field 2, field 3, field 4

From table A
WHERE(table A.FIELD 2])<>"Syrups")

OR

WHERE((table A.field 4])<>1)


UNION ALL SELECT *
FROM [table b)

 

June7

AWF VIP

Local time

Today, 04:53

Joined

Mar 9, 2014

Messages

5,466

• Oct 21, 2019

• #2

Remove all parentheses as they are not needed in this case. First version has odd number of parens - they must be in pairs. Names have space so need []. You have unpaired [ and ]. Advise not to use space nor punctuation/special characters in naming convention. This effort would be greatly simplified if you followed that guidance.

SELECT [field 1], [field 2], [field 3], [field 4] FROM [table A] WHERE [FIELD 2]<>"Syrups"
UNION ALL SELECT * FROM [table b];

Does tableB have only 4 fields all in same order as TableA?

 

E

elly.khanlar

Registered User.

Local time

Today, 08:53

Joined

Oct 3, 2019

Messages

23

• Oct 21, 2019

• #3

yes, table b has exactly 4 fields. how about same conditions?

basically, i don't want to include rows that has syrup in field 2 or field 4 has number 1 .

 

June7

AWF VIP

Local time

Today, 04:53

Joined

Mar 9, 2014

Messages

5,466

• Oct 21, 2019

• #4

Apply WHERE clause in both SELECT lines if you want to restrict records from both tables.

 

arnelgp

..forever waiting... waiting for jellybean!

Local time

Today, 20:53

Joined

May 7, 2009

Messages

19,233

• Oct 21, 2019

• #5

firstly on your second union query, Specify the fieldnames as you specify on the first table even if you have same number of fields.
you'll get unexpected result when the order of fields on the second table is not as on your first table:

SELECT [field 1], [field 2], [field 3], [field 4]
From [table A]
WHERE(([table A].[FIELD 2])<>"Syrups")
OR
WHERE(([table A].[field 4])<>1)
UNION ALL
SELECT [field 1], [field 2], [field 3], [field 4]
FROM [table b]

 

June7

AWF VIP

Local time

Today, 04:53

Joined

Mar 9, 2014

Messages

5,466

• Oct 21, 2019

• #6

Except don't repeat keyword WHERE.

However, I doubt that OR with <> will produce desired output. But try it.

Consider:

SELECT [field 1], [field 2], [field 3], [field 4] FROM [table A]
WHERE [FIELD 2]<>"Syrups" AND [Field 4] IN (SELECT [Field 4] FROM [table A] WHERE [Field 4]<>1)

 

Last edited: Oct 21, 2019