Taking a Access/SQL online class, need help

[NedFlanders]

I am taking a SQL class where we use Access 2007, and it is an online class.
The instructor is no help so I hope you guys are. The question I have goes like this.....

"Find the book title for each book written by author number 18. Use the IN operator in your query."

Now the book title is in one table, and the author number is in another table. The two tables have no rows or columns in common so I am guessing you use a subquery. This is what I tried...

SELECT TITLE
FROM BOOK
WHERE AUTHOR_NUM IN
(SELECT AUTHOR_NUM
FROM AUTHOR
WHERE AUTHOR_NUM='18'

I keep getting a box pop up saying ENTER PARAMETER VALUE and AUTHOR_NUM below it where you would type something in.

 

[james_lankford]

I am taking a SQL class where we use Access 2007, and it is an online class.
The instructor is no help so I hope you guys are. The question I have goes like this.....

"Find the book title for each book written by author number 18. Use the IN operator in your query."

Now the book title is in one table, and the author number is in another table. The two tables have no rows or columns in common so I am guessing you use a subquery. This is what I tried...

SELECT TITLE
FROM BOOK
WHERE AUTHOR_NUM IN
(SELECT AUTHOR_NUM
FROM AUTHOR
WHERE AUTHOR_NUM='18'

I keep getting a box pop up saying ENTER PARAMETER VALUE and AUTHOR_NUM below it where you would type something in.

I can't answer that specific question, but what do you think this would return ?

(SELECT AUTHOR_NUM
FROM AUTHOR
WHERE AUTHOR_NUM='18')

if you guess correctly, it makes your homework a lot easier

 

[xenou]

Can this be true:

The two tables have no rows or columns in common

Surely Books and Authors must be related somehow...Book ID? ISBN Number? Product ID? Author ID? Author Name?

Also:
Beware of text vs. number datatypes. Only the former requires quotes (although in some cases the database engine is kind enough to convert for you anyway).

This is a text criterion (AuthorNum's are text):

Where AuthorNum = '18'

This is a numeric criterion (AuthorNum's are Integers):

Where AuthorNum = 18

 

[NedFlanders]

I can't answer that specific question, but what do you think this would return ?

(SELECT AUTHOR_NUM
FROM AUTHOR
WHERE AUTHOR_NUM='18')

if you guess correctly, it makes your homework a lot easier

That should return any AUTHOR_NUM that is represented by the number 18.

Cant you join two tables even if they do not have anything in common by using a subquery?

 

[james_lankford]

That should return any AUTHOR_NUM that is represented by the number 18.

let's say I told you to look up the forum_name of all forum members who had the forum name NedFlanders

would you do this ?

select forum_name 
from forum
where forum_name = 'NedFlanders'

or is there something strange what I'm asking ?
if you did use that query, what forum_name would you get as a result ?

and if you can't get it this time I'll tell you next post

 

[NedFlanders]

let's say I told you to look up the forum_name of all forum members who had the forum name NedFlanders

would you do this ?

select forum_name 
from forum
where forum_name = 'NedFlanders'

or is there something strange what I'm asking ?
if you did use that query, what forum_name would you get as a result ?

and if you can't get it this time I'll tell you next post

Should it be SELECT FORUM_MEMBERS instead of FORUM_NAME?

 

[james_lankford]

ah, the point I'm trying to make is that if you already know the person's name is NedFlanders, then there is no reason to look it up

and its the same with AUTHOR_NUM;
(SELECT AUTHOR_NUM
FROM AUTHOR
WHERE AUTHOR_NUM='18')

this query says,

I need to look up the AUTHOR_NUM

for which author ?

for the one that has an AUTHOR_NUM of 18

for which one?

for the one that has an AUTHOR_NUM of 18

and what are you looking for ?

the AUTHOR_NUM

the one with the AUTHOR_NUM that's 18 ?

yes

so you see, if you already know the AUTHOR_NUM is 18, then there is no reason to look it up

so instead of this
SELECT TITLE
FROM BOOK
WHERE AUTHOR_NUM IN
(SELECT AUTHOR_NUM
FROM AUTHOR
WHERE AUTHOR_NUM='18')

you could do this

SELECT TITLE
FROM BOOK
WHERE AUTHOR_NUM IN
( 18 )

because you already know the AUTHOR_NUM is 18, so there is no reason to look up the AUTHOR_NUM

 

[xenou]

this query says,

Quote:
I need to look up the AUTHOR_NUM

for which author ?

for the one that has an AUTHOR_NUM of 18

for which one?

for the one that has an AUTHOR_NUM of 18

and what are you looking for ?

the AUTHOR_NUM

the one with the AUTHOR_NUM that's 18 ?

yes

James,
Who's on first...?

Ed,
The tables are probably related in some fashion - otherwise it's a strange assignment. If author number is in the books table, that's a common field (key) that creates a relationship. If your still puzzled, post for us:
1) a list of the field names and their data types in the book table
2) a list of the field names and their data types in the author table
3) and for the record, no one would likely use IN for only one criterion although it works. You'd normally just use the equals operator (author number = 18 rather than author number IN (18) )

Alex