Query Problem in Access

hi friends i need help in this sql query

i have table like,

id fid
__ _____
autonumber text

and i am storing values like

id fid
___________________________________
1 1,2,3,4,5

2 11,12,13,14,15

now to find values i am using query

sql = SELECT * FROM test12 WHERE `fid` LIKE ('%1%')

only problem in this query is it is selecting 1 and 11 and i require
only 1 as i am giving one in %1%

now from this group some one give me the answer of this query

select *
from test
where fid = '1' -- singleton
or fid like '1,%' -- beginning of line
or fid like '%,1,%' -- middle of line
or fid like '%,1' -- end of line

now this query is running perfectly in other database except msaccess
2000. can anyone solve this problem. this query is not giving any
answer. it checks all those records which are singleton but not middle
of line records. and it seems to be problem in access only not in
mysql. it is working perfectly in mysql but not in access and as access
is my database in application i have to use access and i am really
irritate when i find in help that either i can use ' * ' or ' % ' in
expression on any one side like '%,1' or '%1,' but not like middle of
line that i am using '%,1,%'

here is example of my problem

sample table:=

id fid
___________________________________
1 1,2,3,4,5

2 11,12,13,14,15

query like

select *
from test
where fid = '1' -- singleton
or fid like '1,%' -- beginning of line
or fid like '%,1,%' -- middle of line
or fid like '%,1' -- end of line

will result id=1 perfectly but when i search

select *
from test
where fid = '2' -- singleton
or fid like '2,%' -- beginning of line
or fid like '%,2,%' -- middle of line
or fid like '%,2' -- end of line

it will not give me no output. plz help me i dont know what is the
problem if anyone can solve this i will be really thankful.On 19 Oct 2006 00:57:20 -0700, hardik wrote:

Quote:

Originally Posted by

>hi friends i need help in this sql query
>
>i have table like,
>
>id fid
>__ _____
>autonumber text
>
>and i am storing values like
>
>id fid
>___________________________________
>1 1,2,3,4,5
>
>2 11,12,13,14,15
>
>now to find values i am using query
>
>sql = SELECT * FROM test12 WHERE `fid` LIKE ('%1%')

(snip)

Hi hardik,

You should really change this design. The fid column violates the
principle of first normal form. That makes many queries needlessly
complex and slow. A proper design would split the comma-delimited list
in fid into seperate rows:

id fid
1 1
1 2
1 3
1 1
1 1
2 11
2 12
2 13
2 14
2 15

Then, you'd just use SELECT * FROM better_table WHERE fid = '1'

(snip)

Quote:

Originally Posted by

>now from this group some one give me the answer of this query
>
>select *
>from test
>where fid = '1' -- singleton
>or fid like '1,%' -- beginning of line
>or fid like '%,1,%' -- middle of line
>or fid like '%,1' -- end of line

Works, but there is a shorter kludge possible:
SELECT * FROM test WHERE ',' + fld + ',' LIKE '%,1,%'

Quote:

Originally Posted by

>now this query is running perfectly in other database except msaccess
>2000.

Access doesn't use the ANSI standard wildcards for LIKE searches. In
Access, you have t replace the '%' character with '*'.

But the best solution is: fix the design!!

--
Hugo Kornelis, SQL Server MVP

Query Problem

Hi everybody,

I have a table storing the test dates, student names and the grade of
the students. I am now going to find out those dates with student1 got
A and student2 got B and vice versa (i.e. student1 got B and student2
got A). How can I do that? I think it is quite complicate for me.
Please help. I'm using SQL server 2000.

FRANKLINPlease post your DDL + INSERT statements of your sample data. Here's an
untested solution:

select
TheDate
from
MyTable
where
Grade in ('A', 'B')
and Student in ('Student1', 'Student2')
group by
TheDate
having
(sum (case when Grade = 'A' and Student = 'Student1' then 1 else 0
end) = 1
and sum (case when Grade = 'B' and Student = 'Student2' then 1 else 0
end) = 1)
or (sum (case when Grade = 'A' and Student = 'Student2' then 1 else 0
end) = 1
and sum (case when Grade = 'B' and Student = 'Student1' then 1 else 0
end) = 1)

Assumes only one grade per student per date.

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"肥權" <lokalun@.gmail.com> wrote in message
news:1142790320.623759.278150@.g10g2000cwb.googlegr oups.com...
Hi everybody,

I have a table storing the test dates, student names and the grade of
the students. I am now going to find out those dates with student1 got
A and student2 got B and vice versa (i.e. student1 got B and student2
got A). How can I do that? I think it is quite complicate for me.
Please help. I'm using SQL server 2000.

FRANKLIN|||Another alternative, again untested:

select
TheDate
from
MyTable
where
Grade in ('A', 'B')
and Student in ('Student1', 'Student2')
group by
TheDate
having
sum (case
when Grade = 'A' and Student = 'Student1' then 1
when Grade = 'B' and Student = 'Student2' then 1
else 0 end) = 2
or sum (case
when Grade = 'A' and Student = 'Student2' then 1
when Grade = 'B' and Student = 'Student1' then 1
else 0 end) = 2

Same assumption as before.

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:najTf.1845$ji6.54932@.news20.bellglobal.com...
Please post your DDL + INSERT statements of your sample data. Here's an
untested solution:

select
TheDate
from
MyTable
where
Grade in ('A', 'B')
and Student in ('Student1', 'Student2')
group by
TheDate
having
(sum (case when Grade = 'A' and Student = 'Student1' then 1 else 0
end) = 1
and sum (case when Grade = 'B' and Student = 'Student2' then 1 else 0
end) = 1)
or (sum (case when Grade = 'A' and Student = 'Student2' then 1 else 0
end) = 1
and sum (case when Grade = 'B' and Student = 'Student1' then 1 else 0
end) = 1)

Assumes only one grade per student per date.

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"肥權" <lokalun@.gmail.com> wrote in message
news:1142790320.623759.278150@.g10g2000cwb.googlegr oups.com...
Hi everybody,

I have a table storing the test dates, student names and the grade of
the students. I am now going to find out those dates with student1 got
A and student2 got B and vice versa (i.e. student1 got B and student2
got A). How can I do that? I think it is quite complicate for me.
Please help. I'm using SQL server 2000.

FRANKLIN|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. Here is my guess

CREATE TABLE Gradebook
(student_name CHAR(15) NOT NULL,
test_date DATETIME NOT NULL,
grade CHAR(1) NOT NULL,
PRIMARY KEY (student_name, test_date));

>> I am now going to find out those dates with student1 got A and student2 got B and vice versa (i.e. student1 got B and student2 got A).<<

untested

WITH X(student_name, grade, test_date)
SELECT student_name, grade, test_date
FROM Gradebook
WHERE student_name IN ('student1', 'student2')
AND grade IN ('A', 'B')
SELECT
FROM X AS X1, X AS X2
WHERE X1.test_date = X2.test_date
AND X1.student_name <> X2.student_name
AND X1.grade <> X2.grade;|||FRANKLIN,

i repeat the sentence about the necessity to post ddl and sample data
and want to suggest the following solution of your problem:

CREATE TABLE Gradebook
(student_name CHAR(15) NOT NULL,
test_date DATETIME NOT NULL,
grade CHAR(1) NOT NULL,
PRIMARY KEY (student_name, test_date));

INSERT INTO Gradebook
SELECT 'Student1', '2006-01-01', 'A' UNION ALL
SELECT 'Student2', '2006-01-01', 'B' UNION ALL
SELECT 'Student3', '2006-01-01', 'A' UNION ALL
SELECT 'Student1', '2006-01-03', 'C' UNION ALL
SELECT 'Student2', '2006-01-03', 'A' UNION ALL
SELECT 'Student3', '2006-01-03', 'B' UNION ALL
SELECT 'Student1', '2006-01-07', 'A' UNION ALL
SELECT 'Student2', '2006-01-07', 'C' UNION ALL
SELECT 'Student3', '2006-01-07', 'A' UNION ALL
SELECT 'Student1', '2006-01-09', 'A' UNION ALL
SELECT 'Student2', '2006-01-09', 'B';

SET STATISTICS IO ON;

-- SELECT DISTINCT G1.test_date
-- FROM Gradebook AS G1, Gradebook AS G2
-- WHERE G1.test_date = G2.test_date
-- AND ( ( G1.student_name = 'Student1' AND G1.grade = 'A'
-- AND G2.student_name = 'Student2' AND G2.grade = 'B')
-- OR( G1.student_name = 'Student2' AND G1.grade = 'B'
-- AND G2.student_name = 'Student1' AND G2.grade = 'A'));

SELECT G1.test_date
FROM Gradebook AS G1
WHERE G1.student_name = 'Student1'
AND G1.grade IN('A', 'B')
AND EXISTS(SELECT *
FROM Gradebook AS G2
WHERE G2.student_name = 'Student2'
AND G2.test_date = G1.test_date
AND G2.grade = CASE G1.grade
WHEN 'A' THEN 'B'
WHEN 'B' THEN 'A' END);

SET STATISTICS IO OFF;

DROP TABLE Gradebook;

--
Andrey Odegov
avodeGOV@.yandex.ru
(remove GOV to respond)|||homework.