Select entries based on where the field starts?

Question

I have a database with a table with identifiers c1, c2, c3..etc ..

Instead of writing a query that has a bunch of OR , how can I change the query below with something that will catch all the records starting with a specific letter?

 SELECT Person.spineinjuryAdmit, tblComorbidity.comorbidityexplanation, Count(tblComorbidity.comorbidityexplanation) AS CountOfcomorbidityexplanation FROM tblKentuckyCounties INNER JOIN (tblComorbidity INNER JOIN (Person INNER JOIN tblComorbidityPerson ON Person.PersonID = tblComorbidityPerson.personID) ON tblComorbidity.ID = tblComorbidityPerson.comorbidityFK) ON tblKentuckyCounties.ID = Person.County GROUP BY Person.spineinjuryAdmit, tblComorbidity.comorbidityexplanation HAVING (((Person.spineinjuryAdmit)="c1" Or (Person.spineinjuryAdmit)="c2" Or (Person.spineinjuryAdmit)="c3")); 

Answer 1

  SELECT Person.spineinjuryAdmit, tblComorbidity.comorbidityexplanation, Count(tblComorbidity.comorbidityexplanation) AS CountOfcomorbidityexplanation FROM tblKentuckyCounties INNER JOIN (tblComorbidity INNER JOIN (Person INNER JOIN tblComorbidityPerson ON Person.PersonID = tblComorbidityPerson.personID) ON tblComorbidity.ID = tblComorbidityPerson.comorbidityFK) ON tblKentuckyCounties.ID = Person.County GROUP BY Person.spineinjuryAdmit, tblComorbidity.comorbidityexplanation HAVING (Person.spineinjuryAdmit LIKE "c*"); 

Answer 2

Have you tried using LIKE ? As an example:

 SELECT * FROM patients WHERE lastName LIKE 'm%'; 

This will return records where patients.lastName begins with 'm'. The symbol "%" may be "*" for access, I can’t remember. In some databases, you can also use "_", which will match a single character (or any number of underscores that you add).

Answer 3

You can use the WHERE clause to exclude rows that you do not need before executing GROUP BY.

 SELECT p.spineinjuryAdmit, c.comorbidityexplanation, Count(c.comorbidityexplanation) AS CountOfcomorbidityexplanation FROM tblKentuckyCounties AS k INNER JOIN (tblComorbidity AS c INNER JOIN (Person AS p INNER JOIN tblComorbidityPerson AS cp ON p.PersonID = cp.personID) ON c.ID = cp.comorbidityFK) ON k.ID = p.County WHERE p.spineinjuryAdmit ALike "c%" GROUP BY p.spineinjuryAdmit, c.comorbidityexplanation 

If your query is executed in SQL-89 mode, you can use this as a WHERE clause.

 WHERE p.spineinjuryAdmit Like "c*" 

In SQL-92 mode, you need a standard wild ANSI card.

 WHERE p.spineinjuryAdmit Like "c%" 

I used ALike to tell the database engine to expect wild ANSI cards.

SQL-89 mode is used by the DAO ... if you did not specify a database parameter to use SQL-92 mode ("SQL Server Compatible Syntax").

If you use a query with ADO, it will always use SQL-92 mode.

Answer 4

You have two options:

• Use the LIKE statement

• Use the IN operator

For instance:

 Person.spineinjuryAdmit LIKE "c*" Person.spineinjuryAdmit IN ("c1", "c2", "c3") 

For more information about LIKE, see http://office.microsoft.com/en-us/access-help/like-operator-HP001032253.aspx .

Fair warning: LIKE wildcards in Access are * and ? instead of % and _ (as is the case for most other versions of SQL).

Answer 5

You can change this to include a filter list in the WHERE . The following are patients whose last name begins with Smith . (i.e. Smith and Smithson , etc.), and those whose Admit begins with c .

 .... WHERE spineinjuryAdmit LIKE 'c*' AND Patient.FirstName LIKE 'Smith*' GROUP BY Person.spineinjuryAdmit, tblComorbidity.comorbidityexplanation; 

Answer 6

You can use regexp to request all lines starting with multiple characters.

 SELECT * FROM table WHERE column REGEXP '^[ c1, c2, c3]'; 

This query will return all rows where the column begins with 'c1' or 'c2' or 'c3'.