Library Database

K

kizilbas1

I have been trying to work on the following queries, but they give syntax
errors. So can someone please help me to sort them out;

1)

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
[/code]

2)

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
[/code]

3)

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
[/code]

Thanks
 
J

John Spencer

First: When you use an alias for a table, you refer to the table by the
alias.

Second: Assuming you are using JET SQL the syntax is Left Join not Left
OUTER Join

Third: Use brackets Not apostrophes to indicate names with spaces

Fourth: If you have more than one join you must parenthesize them.

Fifth: Access ISNull checks to see if a value is null and returns true
or false

None of that applies if you are creating a pass-through query to an SQL
server or if you are trying to write queries in T-SQL for SQL server.

1)

SELECT NZ(U.Name,'Unalloted') AS [Loan Status]
FROM (Book b
LEFT JOIN Loan l
ON l.[ISBN Number]=b.[ISBN Number])
LEFT JOIN User u
ON U.[User Number] = l.[User Number]
[/code]

2)

SELECT NZ(U.Name,'Unreserved') AS [Reservation Status]
FROM (Book b
LEFT JOIN Reservation r
ON R.ISBN Number=B.[ISBN Number])
LEFT JOIN User u
ON U.User Number = R.[User Number]
[/code]

3) Your turn to fix one.

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
[/code]


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
K

kizilbas1

John Spencer said:
First: When you use an alias for a table, you refer to the table by the
alias.

Second: Assuming you are using JET SQL the syntax is Left Join not Left
OUTER Join

Third: Use brackets Not apostrophes to indicate names with spaces

Fourth: If you have more than one join you must parenthesize them.

Fifth: Access ISNull checks to see if a value is null and returns true
or false

None of that applies if you are creating a pass-through query to an SQL
server or if you are trying to write queries in T-SQL for SQL server.

1)

SELECT NZ(U.Name,'Unalloted') AS [Loan Status]
FROM (Book b
LEFT JOIN Loan l
ON l.[ISBN Number]=b.[ISBN Number])
LEFT JOIN User u
ON U.[User Number] = l.[User Number]
[/code]

2)

SELECT NZ(U.Name,'Unreserved') AS [Reservation Status]
FROM (Book b
LEFT JOIN Reservation r
ON R.ISBN Number=B.[ISBN Number])
LEFT JOIN User u
ON U.User Number = R.[User Number]
[/code]

3) Your turn to fix one.

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
[/code]


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I have been trying to work on the following queries, but they give syntax
errors. So can someone please help me to sort them out;

1)

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
[/code]

2)

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
[/code]

3)

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
[/code]

Thanks
Click to expand...
Click to expand...

I have been trying to run these SQL queries on MS Access. But thanks anyway
your first two queries worked
 
K

kizilbas1

I could not handle the last one. I have tried to do it according to your
codes but it gives error. Could you please...?

John Spencer said:
First: When you use an alias for a table, you refer to the table by the
alias.

Second: Assuming you are using JET SQL the syntax is Left Join not Left
OUTER Join

Third: Use brackets Not apostrophes to indicate names with spaces

Fourth: If you have more than one join you must parenthesize them.

Fifth: Access ISNull checks to see if a value is null and returns true
or false

None of that applies if you are creating a pass-through query to an SQL
server or if you are trying to write queries in T-SQL for SQL server.

1)

SELECT NZ(U.Name,'Unalloted') AS [Loan Status]
FROM (Book b
LEFT JOIN Loan l
ON l.[ISBN Number]=b.[ISBN Number])
LEFT JOIN User u
ON U.[User Number] = l.[User Number]
[/code]

2)

SELECT NZ(U.Name,'Unreserved') AS [Reservation Status]
FROM (Book b
LEFT JOIN Reservation r
ON R.ISBN Number=B.[ISBN Number])
LEFT JOIN User u
ON U.User Number = R.[User Number]
[/code]

3) Your turn to fix one.

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
[/code]


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I have been trying to work on the following queries, but they give syntax
errors. So can someone please help me to sort them out;

1)

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
[/code]

2)

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
[/code]

3)

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
[/code]

Thanks
Click to expand...
Click to expand...
 
J

John Spencer

Persistence Pays. In other words, keep trying so you will learn.

SELECT Nz(u.Name,'') + ' ' +NZ(u.Surname,'') AS [user name],
u.[User number],
b.Title AS [book title],
b.[ISBN Number] AS [bar code]
FROM (Book b
INNER JOIN Loan l
ON b.I[SBN Number]=l.[ISBN Number])
INNER JOIN User u
ON l.[User Number] = u.[User Number]

If that fails, try to figure out why.

By the way, in addition to the previously mentioned fixes, I also put the On
Clause comparisons in the same order as the tables in the from clause.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

kizilbas1 said:
I could not handle the last one. I have tried to do it according to your
codes but it gives error. Could you please...?

John Spencer said:
First: When you use an alias for a table, you refer to the table by the
alias.

Second: Assuming you are using JET SQL the syntax is Left Join not Left
OUTER Join

Third: Use brackets Not apostrophes to indicate names with spaces

Fourth: If you have more than one join you must parenthesize them.

Fifth: Access ISNull checks to see if a value is null and returns true
or false

None of that applies if you are creating a pass-through query to an SQL
server or if you are trying to write queries in T-SQL for SQL server.

1)

SELECT NZ(U.Name,'Unalloted') AS [Loan Status]
FROM (Book b
LEFT JOIN Loan l
ON l.[ISBN Number]=b.[ISBN Number])
LEFT JOIN User u
ON U.[User Number] = l.[User Number]
[/code]

2)

SELECT NZ(U.Name,'Unreserved') AS [Reservation Status]
FROM (Book b
LEFT JOIN Reservation r
ON R.ISBN Number=B.[ISBN Number])
LEFT JOIN User u
ON U.User Number = R.[User Number]
[/code]

3) Your turn to fix one.

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
[/code]


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I have been trying to work on the following queries, but they give
syntax
errors. So can someone please help me to sort them out;

1)

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
[/code]

2)

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
[/code]

3)

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
[/code]

Thanks
Click to expand...
Click to expand...
Click to expand...
 
K

kizilbas1

John Spencer said:
Persistence Pays. In other words, keep trying so you will learn.

SELECT Nz(u.Name,'') + ' ' +NZ(u.Surname,'') AS [user name],
u.[User number],
b.Title AS [book title],
b.[ISBN Number] AS [bar code]
FROM (Book b
INNER JOIN Loan l
ON b.I[SBN Number]=l.[ISBN Number])
INNER JOIN User u
ON l.[User Number] = u.[User Number]

If that fails, try to figure out why.

By the way, in addition to the previously mentioned fixes, I also put the On
Clause comparisons in the same order as the tables in the from clause.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

kizilbas1 said:
I could not handle the last one. I have tried to do it according to your
codes but it gives error. Could you please...?

John Spencer said:
First: When you use an alias for a table, you refer to the table by the
alias.

Second: Assuming you are using JET SQL the syntax is Left Join not Left
OUTER Join

Third: Use brackets Not apostrophes to indicate names with spaces

Fourth: If you have more than one join you must parenthesize them.

Fifth: Access ISNull checks to see if a value is null and returns true
or false

None of that applies if you are creating a pass-through query to an SQL
server or if you are trying to write queries in T-SQL for SQL server.

1)

SELECT NZ(U.Name,'Unalloted') AS [Loan Status]
FROM (Book b
LEFT JOIN Loan l
ON l.[ISBN Number]=b.[ISBN Number])
LEFT JOIN User u
ON U.[User Number] = l.[User Number]
[/code]

2)

SELECT NZ(U.Name,'Unreserved') AS [Reservation Status]
FROM (Book b
LEFT JOIN Reservation r
ON R.ISBN Number=B.[ISBN Number])
LEFT JOIN User u
ON U.User Number = R.[User Number]
[/code]

3) Your turn to fix one.

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
[/code]


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


kizilbas1 wrote:
I have been trying to work on the following queries, but they give
syntax
errors. So can someone please help me to sort them out;

1)

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
[/code]

2)

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
[/code]

3)

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
[/code]

Thanks
Click to expand...
Click to expand...


As you guess, it gives error. All right thanks for everything you did for me, now i will start figuring out myself. I did not understand the bit that you said "I also put the On
Click to expand...
Clause comparisons in the same order as the tables in the from clause". is
it in this code or it must be made in previous codes
 
J

John Spencer

First, I had a typo in the query in the join clause

ON b.I[SBN Number]=l.[ISBN Number] <<


SELECT Nz(u.Name,'') + ' ' +NZ(u.Surname,'') AS [user name],
u.[User number],
b.Title AS [book title],
b.[ISBN Number] AS [bar code]
FROM (Book b
INNER JOIN Loan l
ON b.[ISBN Number]=l.[ISBN Number])
INNER JOIN User u
ON l.[User Number] = u.[User Number]


What I was referring to was that your original query had

FROM Book b INNER JOIN Loan l
ON Loan.ISBN Number=Book.ISBN Number

Note that the FROM clause is Book followed by Loan while the On clause is
Loan followed by Book. This reversed order will probably work, but it may
give you an error. Access will definitely complain if you try to switch to
the Design view (query grid) and say that it cannot represent the join
expression.

I would have set up as
FROM Book b
INNER JOIN Loan l
ON b.ISBN Number=l.ISBN Number



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

kizilbas1 said:
John Spencer said:
Persistence Pays. In other words, keep trying so you will learn.

SELECT Nz(u.Name,'') + ' ' +NZ(u.Surname,'') AS [user name],
u.[User number],
b.Title AS [book title],
b.[ISBN Number] AS [bar code]
FROM (Book b
INNER JOIN Loan l
ON b.I[SBN Number]=l.[ISBN Number])
INNER JOIN User u
ON l.[User Number] = u.[User Number]

If that fails, try to figure out why.

By the way, in addition to the previously mentioned fixes, I also put the
On
Clause comparisons in the same order as the tables in the from clause.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County


As you guess, it gives error. All right thanks for everything you did for
me, now i will start figuring out myself. I did not understand the bit
that you said "I also put the On
Click to expand...
Clause comparisons in the same order as the tables in the from clause". is
it in this code or it must be made in previous codes
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with Query/SQL 4
userform/combobox question 18
Exporting to Access Help needed 3
ListBox & Filter Question 1
Run-Time Error 91 Help needed 3
visual basic macro in excel 0
LOOKIN G FOR EMPLOYMENT LOOK HERE!!!!!!!!!! 1

Top