SQL query

[Guest]

I need to write an sql query that will check weather a string taken from a column of a table appears as a substring in another column of a table for that same entry. Exampl

Gate Time CurGat
105 8:00 10
104 7:00 104
102 5:00 102, 10
100 4:00 10

In the above case only entry 3,4 would be returned by the query. I know you can use LIKE keyword, but I think can't figure out how to use it with a column entry, I think it only works with an actual string.

 

[Brian Camire]

You might try a query whose SQL looks something like this:

SELECT
[Your Table].*
FROM
[Your Table]
WHERE
[Your Table].[CurGate] Like "*" & [Your Table].[Gate] & "*"

This may return what you consider to be "false positives". For example, if
for a given record Gate is "104" and CurGate is "2104", the query will
return this record. It may be that your data is such that this will not
happen (for example, if all Gate values are the same number of digits).

I need to write an sql query that will check weather a string taken from a

column of a table appears as a substring in another column of a table for
that same entry. Example

Gate Time CurGate
105 8:00 104
104 7:00 104A
102 5:00 102, 105
100 4:00 100

In the above case only entry 3,4 would be returned by the query. I know

you can use LIKE keyword, but I think can't figure out how to use it with a
column entry, I think it only works with an actual string.

 

[Guest]

Can you show me what the actual sql code would look like assuming the table name is table1. I mainly need to know the part after the LIKe keyword cause it seems to give me an error after this point.

 

[Brian Camire]

Just subsitute "Your Table" with "table1" as in:

SELECT
[table1].*
FROM
[table1]
WHERE
[table1].[CurGate] Like "*" & [table1].[Gate] & "*"

To avoid "false positives" you might expand the WHERE clause like this:

SELECT
[table1].*
FROM
[table1]
WHERE
[table1].[CurGate] = [table1].[Gate]
OR
[table1].[CurGate] Like [table1].[Gate] & ", *"
OR
[table1].[CurGate] Like "*, " & [table1].[Gate]
OR
[table1].[CurGate] Like "*, " & [table1].[Gate] & ", *"

This will return records where any of the following conditions are
satisified:

1. CurGate is equal to Gate.

2. CurGate begins with Gate, followed by a comma and space.

3. CurGate ends with Gate, preceeded by a comma and space.

4. CurGate contains Gate, preceeded and followed by a comma and space.

This will, for example, prevent the query from returning the second record
in your example (Gate = "104" and CurGate = "104A").

Can you show me what the actual sql code would look like assuming the

table name is table1. I mainly need to know the part after the LIKe keyword
cause it seems to give me an error after this point.

 

[Guest]

Sorry I'm still confused. So the sql code would look like

SELECT table1.* FRO
table
WHERE table1.CurGate = table1.Gat
OR table1.CurGate LIKE table1.Gate,
OR table1.CurGate LIKE *,table1.Gat
OR table1.CurGate LIKE *,table1.Gate,

does that sound right. Oh yeah will "%" work for the "*" or does it do something different?

 

[Brian Camire]

No, the SQL should like just like it appears in my previous post. Try
creating a new query in Access, switch to SQL view, and paste the SQL into
there, replacing whatever was there before.

If you're creating the SQL in code (and have a good reason to do it that way
instead of using a predefined query), the SQL still has to be the same, but
your syntax for building it may appear a little complicated because of the
embedded quotes and ampersands.

The % and * wildcard characters serve the same purpose. You use % when
you're executing the query via the Jet 4.0 OLEDB provider (e.g., via an ADO
Command or Connection object), and * when you're executing the query via DAO
(e.g., by running the query in Access directly).

Sorry I'm still confused. So the sql code would look like

SELECT table1.* FROM
table1
WHERE table1.CurGate = table1.Gate
OR table1.CurGate LIKE table1.Gate,*
OR table1.CurGate LIKE *,table1.Gate
OR table1.CurGate LIKE *,table1.Gate,*

does that sound right. Oh yeah will "%" work for the "*" or does it do

something different?