|
| |
|
|
bookshop101
Posts: 21
Joined: 5/28/2004
Status: offline
 |
1 db 3 tables return all in 1 request - 6/2/2004 11:55:36
My db (of books) has 3 tables,
1. title, author, isbn
2. stock levels, isbn,
3. review of the book, isbn
can I have a single search the displays the information from all 3 tables together
would i use the isbn value as a searching reference to query from one table to the other?
I have never used MSAccess before and can't fathom my way around it, I was hoping this couold be achieved w/ ASP & SQL
thanks
G
|
|
|
|
BeTheBall
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: 1 db 3 tables return all in 1 request - 6/2/2004 12:48:19
It can be achieved with ASP or in Access. Not sure you can do this in the database results wizard, but it's worth a try. In step 2 of the wizard choose "Custom Query". Then enter your SQL as this:
SELECT * FROM Table1 INNER JOIN Table 2 On Table1.isbn = Table2.isbn INNER JOIN Table3 ON Table2.isbn = Table3.isbn WHERE [specify your criteria]
If you are just retrieving all records, you can delete everything from WHERE on.
Of course you need to change the occurences of Table1, Table2 and Table3 to your actual table names.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
bookshop101
Posts: 21
Joined: 5/28/2004
Status: offline
|
RE: 1 db 3 tables return all in 1 request - 6/2/2004 13:44:52
hhhhmmm thanks
but it doesn't want to work,
here is my custom sql query from step 2 of drw
SELECT * FROM TITLES INNER JOIN Reviews On TITLES.ISBN = Reviews.ISBN INNER JOIN Stock ON Reviews.ISBN = Stock.ISBN WHERE (Author LIKE '::Author::%' OR Title LIKE '::Title::%' OR ISBN = '::ISBN::' OR Author LIKE '%::Keyword::%' OR Class LIKE '::Class::%' OR Title LIKE '%::Keyword::%') ORDER BY Author ASC,Title ASC
my 3 tables are TITLES Reviews and Stock
each contain an ISBN field.
from WHERE onwards everything is o.k. as it was searching fine, but DRW cannot verify query.
G
| |
|
|
|
BeTheBall
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: 1 db 3 tables return all in 1 request - 6/2/2004 14:27:12
What if you put the first join in ()? Something like:
SELECT * FROM (TITLES INNER JOIN Reviews On TITLES.ISBN = Reviews.ISBN) INNER JOIN Stock ON Reviews.ISBN = Stock.ISBN WHERE (Author LIKE '::Author::%' OR Title LIKE '::Title::%' OR ISBN = '::ISBN::' OR Author LIKE '%::Keyword::%' OR Class LIKE '::Class::%' OR Title LIKE '%::Keyword::%') ORDER BY Author ASC,Title ASC
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
bookshop101
Posts: 21
Joined: 5/28/2004
Status: offline
|
RE: 1 db 3 tables return all in 1 request - 6/2/2004 15:05:49
I tried 90's aroung the first point and all variations i could think of, but nothing is working,
I think i will have to do it within Access
| |
|
|
|
bookshop101
Posts: 21
Joined: 5/28/2004
Status: offline
|
RE: 1 db 3 tables return all in 1 request - 6/2/2004 17:33:36
O.k I have a different problem which sounds almost exactly the same.
I still need to check 3 tables in a single db, but they do not have to corrolate with each other. (if anything elimininating repetions whould be handy. but that is another problem).
So can i get the DRW to check all 3 tables in a db and return all results that match the criteria?
is it as simple as adding each db name in sql
ie.
SELECT * FROM Table1 Table2 Table3 WHERE....etc
| |
|
|
|
BeTheBall
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: 1 db 3 tables return all in 1 request - 6/2/2004 17:57:26
Since isbn is in all three tables, you may have to be more specific. In other words, the use of * to retrieve all fields may not work. This should work:
SELECT Titles.isbn, Titles.author, Titles.title, stock.levels, reviews.review FROM Titles, Stock, Reviews WHERE isbn='::isbn::'
Adjust the above to reflect your correct table and field names.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
bookshop101
Posts: 21
Joined: 5/28/2004
Status: offline
|
RE: 1 db 3 tables return all in 1 request - 6/2/2004 18:03:35
I see, thanks I will give it a shot
g
| |
|
|
|
bookshop101
Posts: 21
Joined: 5/28/2004
Status: offline
|
RE: 1 db 3 tables return all in 1 request - 6/4/2004 10:36:17
Duane,
I tried what you suggested and reveiced the following error
The specified field 'ISBN' could refer to more than one table listed in the FROM clause of your SQL statement.
my sql
SELECT TITLES.ISBN, TITLES.Author, TITLES.Title, STOCK.aOO, STOCK.aOH FROM TITLES, STOCK WHERE (Author LIKE '::Author::%' OR Title LIKE '::Title::%' OR ISBN = '::ISBN::' OR Author LIKE '%::Keyword::%' OR Class LIKE '::Class::%' OR Title LIKE '%::Keyword::%') ORDER BY Author ASC,Title ASC
should I have the ISBN clause outside the ()'s?
(oh and i got rid of reviews table)
< Message edited by bookshop101 -- 6/4/2004 10:40:25 >
| |
|
|
|
BeTheBall
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: 1 db 3 tables return all in 1 request - 6/4/2004 10:46:42
Because ISBN exists in more than one table you need to change:
OR ISBN
to
OR TITLES.ISBN
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
bookshop101
Posts: 21
Joined: 5/28/2004
Status: offline
|
RE: 1 db 3 tables return all in 1 request - 6/4/2004 11:10:00
Thanks Duane, that seems to have done the trick, but now it is awfully slow
http://www.oxfordbookshop.com/search.htm
oh i see, i just ran a search
it returned 3000 pages of results, they are all the same title, author, isbn, class, but it has many different verions of what its stock should be
i think it is returning ALL the results in the STOCK table and not just the specific one corresponding to the ISBN.
:(
< Message edited by bookshop101 -- 6/4/2004 11:12:05 >
| |
|
|
|
bookshop101
Posts: 21
Joined: 5/28/2004
Status: offline
|
RE: 1 db 3 tables return all in 1 request - 6/4/2004 11:15:10
aha!
should i have
STOCK.ISBN = '::ISBN::' OR TITLES.ISBN='::ISBN::'
or perhaps
STOCK.ISBN = '::ISBN::' AND TITLES.ISBN='::ISBN::'
< Message edited by bookshop101 -- 6/4/2004 11:18:13 >
| |
|
|
|
BeTheBall
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: 1 db 3 tables return all in 1 request - 6/4/2004 11:19:54
Did you include default values for your form fields? However, even with those, title searches are going to be unbearably slow. I tried searching on Othello and got more than 5000 results and it took quite a while.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
bookshop101
Posts: 21
Joined: 5/28/2004
Status: offline
|
RE: 1 db 3 tables return all in 1 request - 6/4/2004 11:25:44
I have defaults in all fields, but can't understand why it is returning soo many reults, it seems like it is returning all records in STOCK table
s-sql="SELECT TITLES.Author, TITLES.Title, TITLES.ISBN, TITLES.Class, TITLES.Price, STOCK.aOO, STOCK.aOH FROM TITLES, STOCK WHERE (Author LIKE '::Author::%' OR Title LIKE '::Title::%' OR STOCK.ISBN = '::ISBN::' AND TITLES.ISBN = '::ISBN::' OR Author LIKE '%::Keyword::%' OR Class LIKE '::Class::%' OR Title LIKE '%::Keyword::%') ORDER BY Author ASC,Title ASC"
DefaultFields="Author=11111111&Title=11111111&ISBN=1111111&Keyword=11111111&Class=11111111&Keyword=11111111"
(nb: I don't know how keyword ended up there twice!!)
This whole thing was supposed to be a time 'saver', but it doesn't feel like it
G
< Message edited by bookshop101 -- 6/4/2004 11:27:15 >
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
