SELECT 2 tables in WHERE clause of a single query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SELECT 2 tables in WHERE clause of a single query

Author

Topic

monaya
Yak Posting Veteran

58 Posts

Posted - 2009-06-02 : 11:39:58

In a library application, we have table1 where we see which have been CHECKEDOUT but NOT CHECKEDIN. There, we get a count on how many have been checkout already.

Bookrentals: id, bookid, checkedout, checkedin
SELECT COUNT()
FROM bookrentals
WHERE checkout = 1 AND checkedin = 0

We have table2 that says how many books a member is allowed out.

Accounts: accountid, bookamount
SELECT bookamount
FROM accounts

The difference is how many left they're allowed out.

In a single query we need to subtract the difference of those two tables from another count on table 1 where CHECKEDOUT = 0 and CHECKEDIN = 0

SELECT br.checkedout, br.checkedin, a.accountid
FROM bookrentals AS r, accounts AS a
WHERE br.checkedout = 0 AND br.checkedin = 0 AND br.accountid = a.accountid AND .....?

I'm guessing I need 2 select statements in the WHERE clause?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-02 : 11:46:07

how are new books which are added distinguished? what will be their checkout,checkin value?

monaya
Yak Posting Veteran

58 Posts

Posted - 2009-06-02 : 11:53:21

I edited my post for more clarification I think.

NEW books desired are CHECKEDOUT = 0 and CHECKEDIN = 0 FROM table 1.

Books members have out already are CHECKEDOUT = 1 and CHECKEDIN = 0 FROM table 1 also.

Alotted amount are stored in a column BOOKAMOUNT of accounts table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-02 : 12:15:50

Assuming id is primary key of bookrentals


;With Books_CTE (id,bookid,accountid,checkedout,checkin, bookamount) AS
(
SELECT br.id,br.bookid,br.accountid,br.checkedout,
br.checkin,a.bookamount
FROM Bookrentals br
INNER JOIN Accounts a
ON a.accountid=br.accountid
WHERE br.checkedin=0
)

SELECT c1.*
FROM Books_CTE c1
CROSS APPLY (SELECT COUNT(*) AS allotedcount
             FROM Books_CTE 
             WHERE accountid=c1.accountid
             AND id< c1.id) c2
WHERE allotedcount< c1.bookamount

monaya
Yak Posting Veteran

58 Posts

Posted - 2009-06-02 : 12:40:20

Hmm. Here is the SQL I have now. I'm joining a couple of more tables. Sorry for the difference I was trying to summarize.

SELECT br.bookid, br.rentalid, b.booktitle, a.fname, act.bookamount
FROM bookrentals br, books b, accounts a, accounttypes act
WHERE br.checkedout = 0 AND br.checkedin = 0 AND br.bookid = b.bookid AND br.accountid = a.accountid AND a.accountid = act.accountid
ORDER BY br.rentalid ASC

act.bookamount has the max a member is allowed. I need to query the br table to get how many a member already has out.

This brings up all desired books by a member, but I need to limit the RESULTS to display only books leftover per account which is the difference between:

Something like where act.bookamount minus WHERE br.checkedout = 1 AND br.checkedin = 0

Hope that makes sense.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-02 : 12:43:58

you can just add the extra joins in myquery to give you only left over per account

monaya
Yak Posting Veteran

58 Posts

Posted - 2009-06-02 : 12:46:24

The query shouldn't just display the count but the values of the columns as well. I guess your query is a little confusing for me. Ithas some stuff I didnt see before.

This SQL is for mysql too.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-02 : 12:49:03

[code]
SELECT br.bookid, br.rentalid, b.booktitle, a.fname, act.bookamount
FROM bookrentals br, books b, accounts a, accounttypes act
WHERE br.checkedout = 0 AND br.checkedin = 0 AND br.bookid = b.bookid AND br.accountid = a.accountid AND a.accountid = act.accountid
AND (SELECT COUNT(*) FROM bookrentals WHERE id <br.id AND accountid=br.accountid) < act.bookamount
ORDER BY br.rentalid ASC
[/code]

monaya
Yak Posting Veteran

58 Posts

Posted - 2009-06-02 : 13:08:42

Hmm, that looks closer but its bringing up ALL the desired books of one account not the alotted amount according to the difference. Worse, it's bringing up ALL desired books for one account not all accounts.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-02 : 13:15:45

is id pk of rental table?

monaya
Yak Posting Veteran

58 Posts

Posted - 2009-06-02 : 13:24:16

quote:
Originally posted by visakh16

is id pk of rental table?

Yes. Update: Actually the results are giving me what I originally received. Now I'm getting all the desired books withOUT acknowledgment of the difference for how many more are allowed.

Why are you checking if id is less than the same id id < br.id

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-02 : 13:26:48

i'm looking for count of books which happen to allot to accountid before the currently looking book and checking if its less than max amount allowed

monaya
Yak Posting Veteran

58 Posts

Posted - 2009-06-02 : 13:36:24

I cant get ti to work for some reason it's not acknolwedging the difference.

I have an account in the db that is allowed 2, has one 1 out already, has 2 more desired books in a checkout and both 2 are showing up. He only has 1 leftover in truth. the query isnt catching this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-02 : 13:38:23

and does the id always follow order in which books are alloted?

monaya
Yak Posting Veteran

58 Posts

Posted - 2009-06-02 : 13:50:51

I think you're referring to the rentalid or the id in the accounttypes table? I'm not sure what id stands for in your query?

The rentalid is just a reference for the rental record. What tells me the max books that are allowed is accounttypes.bookamount.

What tells me what's allowed is the difference between rentals that have (checkedout = 1 and checkedout = 0) and the accounttypes.bookamount.

monaya
Yak Posting Veteran

58 Posts

Posted - 2009-06-02 : 14:56:25

Something doesnt seem to be right with the query because I replaced the subquery with a manual number to debug and it still doesnt work

SELECT br.bookid, br.rentalid, b.booktitle, a.fname, act.bookamount
FROM bookrentals br, books b, accounts a, accounttypes act
WHERE br.checkedout = 0 AND br.checkedin = 0 AND br.bookid = b.bookid AND br.accountid = a.accountid AND a.accountid = act.accountid
AND (SELECT COUNT(*) FROM bookrentals  WHERE id <br.id AND  accountid=br.accountid) < act.bookamount
ORDER BY br.rentalid ASC

replaced with

SELECT br.bookid, br.rentalid, b.booktitle, a.fname, act.bookamount
FROM bookrentals br, books b, accounts a, accounttypes act
WHERE br.checkedout = 0 AND br.checkedin = 0 AND br.bookid = b.bookid AND br.accountid = a.accountid AND a.accountid = act.accountid
AND 1 < act.bookamount
ORDER BY br.rentalid ASC

Same issue. Keep in mind that multiple rentals from different accounts are stored in the bookrentals table not just one account obviously

monaya
Yak Posting Veteran

58 Posts

Posted - 2009-06-02 : 16:00:57

Is it possible to perform that function using the HAVING statement instead.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-03 : 13:32:01

quote:
Originally posted by monaya

I think you're referring to the rentalid or the id in the accounttypes table? I'm not sure what id stands for in your query?

The rentalid is just a reference for the rental record. What tells me the max books that are allowed is accounttypes.bookamount.

What tells me what's allowed is the difference between rentals that have (checkedout = 1 and checkedout = 0) and the accounttypes.bookamount.

i was refering to id filed in bookrentals as per your first post

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources