|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
September 14th, 2003, 02:10 AM
|
|||
|
|||
|
Contain Command in Access SQL
I'm looking for the equivalent syntax of "Contain" in SQL for querying in Access SQL.. The code is given below for which i want an alternative in Access SQL
select * from student where (select Accno from Borrow where Borrow.Sno = Student.Sno contains (select Accno from Book where Title like "DBMS")) The tables are: Student(Sno, Name, College) Borrow(Sno, Accno, DOI) Book(Accno, Title, Cost)
|
|
#2
September 14th, 2003, 02:38 PM
|
||||
|
||||
|
try this:
Code:
select Student.Sno, Student.Name, Student.College
from Student
inner
join Borrow
on Student.SNo = Borrow.Sno
inner
join Book
on Borrow.Accno = Book.Accno
where Book.Title = 'DBMS'
rudy http://r937.com/
|
|
#3
September 15th, 2003, 02:22 AM
|
|||
|
|||
|
Contain Command in Access SQL
Hi... Rudy
Thanx for replying... but the result of the query that you submitted gives me the list of people who borrowed any book on DBMS whereas I was looking for a query that would display people who had borrowed all the books on DBMS... not any Thanx, Chondhe
|
|
#4
September 15th, 2003, 10:54 PM
|
||||
|
||||
|
ah, okay, i understand now
never saw "contains" before, but your explanation makes it clear sounds like a set operation, and who knows, it might be part of sql2003 or whatever the latest standard is, but i doubt access supports it anyhow, here's how i'd approach the problem -- Code:
select Student.Sno, Student.Name, Student.College
, count(Book.Title)
from Student
inner
join Borrow
on Student.SNo = Borrow.Sno
inner
join Book
on Borrow.Accno = Book.Accno
where Book.Title like '%DBMS%'
group
by Student.Sno, Student.Name, Student.College
having count(Book.Title)
= ( select count(*)
from Book
where Book.Title like '%DBMS%' )
i changed the WHERE back to LIKE since you are looking for several titles let me know if that works rudy
|
|
#5
September 22nd, 2003, 02:03 AM
|
|||
|
|||
|
Hi Rudy..
Thanx a lot mate for the query. It worked as I wanted it. Correct results. I had actually read that the contain command was part of the ANSI SQL standards of 92 and 99.. so i thought that it'd work here with access. Thanx once more.
|
|
| Viewing: Dev Shed Forums > Databases > Database Management > Contain Command in Access SQL |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
