Question Access Database - using pre-made queries

[kfirba]

Hello!

There are some queries that I can't make with the vb.net for some reason, but I can do them in access. The reason I can't do them it's because, I'm using 2 queries to eventually give me a single result. For instance, I got Costumers Table, Borrows Table and Book Table, and I wanna display the NAME of books that a person borrowed. In order to do that, I queried the Borrows table to give me all of the book ID's that the person has taken and i was quering the result of the previous query to gain the BOOK NAME according to the BOOK ID'S that i have got from the first query.

I hope you understood what I tried to say

Anyways, if someone here has ANY idea how I can do that in VB.NET I would be more than happy to learn about.
Well, because I have NO idea how to do that in VB.NET, I did that in Access. When I go to Server Explorer, I see a folder named Functions. The folder contains the 2 queries that I used to get the result I want. The first query name is Test, and the query that uses Test query is named Test2.
How do I use Test2 result? is it even possible?

Thanks in advance!

 

[jmcilhinney]

First of all, you might want to change one of your names there. A "costumer" would be someone who looks after costumes, e.g. a person who looks after the clothes the actors where in a movie. I think what you mean is a "customer", which is someone who buys some goods or services from a supplier. As your app is for a library, I would think that "member" would be more appropriate anyway.

As for the question, there's no need to perform multiple queries. You can do it all with a JOIN. Presumably your Borrows table has a foreign key form the Costumers table and another from the Book table. (As an aside, you really should be consistent with your naming. Either use all plural or all singular, not some of each.) You can query the database to get the name of the borrower and the name of the book they borrowed with something like this:

SELECT Costumers.Name AS CostumerName, Book.Title AS BookTitle
FROM Costumers
INNER JOIN Borrows ON Costumers.CostumerID = Borrows.CostumerID
INNER JOIN Book ON Book.BookID = Borrows.BookID

You can change the column list to select whatever columns you want from all three tables.

 

[kfirba]

Thanks alot!

About the names, thanks for your note, I will pay attention next time.

Thanks alot for the SQL code! I wonder how come I didn't know it was possible :O
if I wanna add a WHERE statement in your code, what should I use as a parameter? the Costumer ID? where do I put parameters there? in BookID? CostumerID? maybe both? Could you please provide me the SQL with a WHERE statement ? I want to get the books names by the given CostumerID.

Thanks in advance!

 

[jmcilhinney]

The fact that it uses joins doesn't make this query special. You add a WHERE clause to the end of this exactly as you would for any other. The only one thing to note is that you must qualify a column name with a table name if multiple included tables contain a column with that name. It's not a bad idea to qualify all columns regardless, for clarity.

 

[kfirba]

I see, thanks!

one more thing, because I don't like using a code without fully understanding it, I would like to ask you about the SQL code.
what this code does is taking a costumerID and giving a bookname and bookid as a result. The way it does it. First of all, the code extract the bookid from borrows and then book name from books.

will it matter if I swap the INNER JOIN?

 

[jmcilhinney]

When using all INNER JOINs, it doesn't matter which order the tables appear. You will get the same result regardless. If you use OUTER JOINs or mix INNER JOINs with OUTER JOINs then the order does matter.

 

[IanRyder]

Hi,

A quick tip for you. When creating the basic structure of your SQL queries, an easy way to do this is to use the Query Builder either within Access or the VS IDE. You can then quickly create and test complex SQL queries with easy to use and intuitive fields. When you then have your queries working the way you want, you can then just copy and paste the SQL code into your project as you need.

Hope that helps.

Cheers,

Ian