I have been trying to work on the following, but they give syntax errors. So can someones please help me to sort them out;
1)
Code:
SELECT ISNULL(User.Name,'Unalloted') AS 'Loan Status'FROM Book b
LEFT OUTER JOIN Loan l
ON Loan.ISBN Number=Book.ISBN Number
LEFT OUTER JOIN User u
ON User.User Number = Loan.User Number
2)
Code:
SELECT ISNULL(User.Name,'Unreserved') AS 'Reservation Status'FROM Book b
LEFT OUTER JOIN Reservation r
ON Reservation.ISBN Number=Book.ISBN Number
LEFT OUTER JOIN User u
ON User.User Number = Reservation.User Number
3)
Code:
SELECT ISNULL(User.Name,'') + ' ' +ISNULL(User.Surname,'') AS 'user name',
User.User number,
Book.Title AS 'book title',
Book.ISBN Number AS 'bar code'
FROM Book b
INNER JOIN Loan l
ON Loan.ISBN Number=Book.ISBN Number
INNER JOIN User u
ON User.User Number = Loan.User Number
Thanks
-->Thread moved to SQL Development forum.
Post the syntax errors that you get.
Was this answer helpful ?
Yes No
Quote:
| Originally Posted by jmurrayhead -->Thread moved to SQL Development forum.
Post the syntax errors that you get. |
The following errot I get from the 3 queries;
Syntax error(missing operator) in query expression æ… eservation.ISBN Number=Book.ISBN Number
LEFT OUTER JOIN User u
ON User.User Number = Reservation.User Number?
Thanks
Was this answer helpful ?
Yes No
I think your main problem is due to the fact your field names have spaces in them. I always avoid spaces in table and field names to prevent problems when working with the database through ASP.
Try enclosing field names with spaces in sinngle quotes
e.g.: 'Book.ISBN Number' or Book.'IBSN Number'
I always use the underscore character instead of spaces if a field name needs a space so i would have used IBSN_Number.
Was this answer helpful ?
Yes No
Quote:
| Originally Posted by icoombs I think your main problem is due to the fact your field names have spaces in them. I always avoid spaces in table and field names to prevent problems when working with the database through ASP.
Try enclosing field names with spaces in sinngle quotes
e.g.: 'Book.ISBN Number' or Book.'IBSN Number'
I always use the underscore character instead of spaces if a field name needs a space so i would have used IBSN_Number. |
I have tried what you said, but still did not work
Thanks
Was this answer helpful ?
Yes No
Try Book.[ISBN Number] and enclosing the table names with spaces in square brackets [book b].
you will need to change all field names with spaces in the query before it will work.
if you are using access database, try generating the query in access to see how it formats the sql.
Was this answer helpful ?
Yes No
Quote:
| Originally Posted by icoombs Try Book.[ISBN Number] and enclosing the table names with spaces in square brackets [book b].
you will need to change all field names with spaces in the query before it will work.
if you are using access database, try generating the query in access to see how it formats the sql. |
Still dooes not work and gives the same error, yes i am using access. I am wondering that the problem might be with the tables i have created. if it is so, would you want me to post you the access whole to look at it, but if you want to.
Thanks
Was this answer helpful ?
Yes No
I think your problem might be that you change the name of the User table to U then refer to it as User. Once you alias a table name, you have to continue to use that alias until the statement ends. Change to this:
Code:
LEFT OUTER JOIN User u
ON u.User Number = Reservation.User Number?
That should work
Was this answer helpful ?
Yes No
Quote:
| Originally Posted by Lauramc I think your problem might be that you change the name of the User table to U then refer to it as User. Once you alias a table name, you have to continue to use that alias until the statement ends. Change to this:
Code:
LEFT OUTER JOIN User u
ON u.User Number = Reservation.User Number?
That should work |
I am really gonna go mad, whatever I have tried, none of them worked.
Was this answer helpful ?
Yes No
Quote:
| Originally Posted by kizilbas1 I am really gonna go mad, whatever I have tried, none of them worked. |
Don't worry, it happens to all of us! In doing a check I realized that User is a reserved word and needs brackets around it. I checked this query and the syntax is right.
Try this:
Code:
SELECT ISNULL([User].Name,'Unreserved') AS 'Reservation Status'
FROM Book b
LEFT OUTER JOIN Reservation r
ON r.[ISBN Number]=b.[ISBN Number]
LEFT OUTER JOIN [User] u
ON u.[User Number] = r.[User Number]
P.S. If you get a different error, post it here. Otherwise let me know that you got the same error.
Was this answer helpful ?
Yes No
