473,322 Members | 1,781 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

SQL query: how to retrieve data using two tables

Hello friends,
I am a new member to this community.

I have a basic problem.

I have two tables.

1) books
having fields id, name, author, publication

2)issued_books
having fields id, user_id, book_id.

books table has the record of all the books that are in the library

and issued_books table has the record of all the books that are issued.
The book_id in the issued_books table contain the id of the books table.

Now i want to show all the books that are not issued using these two tables

Can anyone tell the query that can retrieve the unissued books.

thanks
Jun 18 '07 #1
9 17559
nathj
938 Expert 512MB
Hello friends,
I am a new member to this community.

I have a basic problem.

I have two tables.

1) books
having fields id, name, author, publication

2)issued_books
having fields id, user_id, book_id.

books table has the record of all the books that are in the library

and issued_books table has the record of all the books that are issued.
The book_id in the issued_books table contain the id of the books table.

Now i want to show all the books that are not issued using these two tables

Can anyone tell the query that can retrieve the unissued books.

thanks
I'm fairly new to this too but I think the results could be obtained with a subquery. I can't quite remember the syntax off the top of my head but try something like:

select a.* from books where a.id not in(select b.book_id from issued_books b)

The idea is right but I'm not sure about the syntax, I'm new to MySQL and have been doing this sort of thing in VFP for years.

Let me know how you get on.

Cheers
Nathan
Jun 18 '07 #2
r035198x
13,262 8TB
Hello friends,
I am a new member to this community.

I have a basic problem.

I have two tables.

1) books
having fields id, name, author, publication

2)issued_books
having fields id, user_id, book_id.

books table has the record of all the books that are in the library

and issued_books table has the record of all the books that are issued.
The book_id in the issued_books table contain the id of the books table.

Now i want to show all the books that are not issued using these two tables

Can anyone tell the query that can retrieve the unissued books.

thanks
Expand|Select|Wrap|Line Numbers
  1. select * from books where id not in (select id from issued_books)
Jun 18 '07 #3
Thank you friends,

The sql query you suggested works fine.

Thank you very much.
Jun 18 '07 #4
Expand|Select|Wrap|Line Numbers
  1. select * from books where id not in (select id from issued_books)

I am new to this community also....

Lets see-- I dont know - how you are doing this. wt kind of software you are using.

Because i am using ASP.net Visual web developer and MYSQL - here i am using navicat to access MYSQL database.

Wt you do - go to MYSQL through Navicat and make tables you need.

Then go to Visual web developer and just add SQL DATA SOURCE choose those tables and columns you want to show on web and Select SQL data source 1and go to properties and go to select query -- after that new window will appear and go to query builder and right click add table. Now here it will show you one table already and then just add the another table you want the relation ship with --- choose appropriate columns and click ok.

AND YOU ARE DONE--- THATS IT.

Output: Whatever columns you have chosen will appear on you page. Its called retriving data from two or more tables on same page.

I hope that would help.........

I have question for you ':::::::::::::::::::::::::::::::::::::::::::::
Hi I am new at ASP and MYSQL.

I am Using Navicat to access MYSQL database.

My Problem is when i create the foreign key -it gives me this error.

1452-cannot add or update child row: a foreign key constraint fails....

Can any body help me - why am i getting this error - and how to fix it --

Thanks,
Harsh
Jun 18 '07 #5
r035198x
13,262 8TB
I am new to this community also....

Lets see-- I dont know - how you are doing this. wt kind of software you are using.

Because i am using ASP.net Visual web developer and MYSQL - here i am using navicat to access MYSQL database.

Wt you do - go to MYSQL through Navicat and make tables you need.

Then go to Visual web developer and just add SQL DATA SOURCE choose those tables and columns you want to show on web and Select SQL data source 1and go to properties and go to select query -- after that new window will appear and go to query builder and right click add table. Now here it will show you one table already and then just add the another table you want the relation ship with --- choose appropriate columns and click ok.

AND YOU ARE DONE--- THATS IT.

Output: Whatever columns you have chosen will appear on you page. Its called retriving data from two or more tables on same page.

I hope that would help.........

I have question for you ':::::::::::::::::::::::::::::::::::::::::::::
Hi I am new at ASP and MYSQL.

I am Using Navicat to access MYSQL database.

My Problem is when i create the foreign key -it gives me this error.

1452-cannot add or update child row: a foreign key constraint fails....

Can any body help me - why am i getting this error - and how to fix it --

Thanks,
Harsh
This would be hijacking is not considered good forum practice.
Jun 18 '07 #6
Expand|Select|Wrap|Line Numbers
  1. select * from books where id not in (select id from issued_books)

Hi, I'm just new in PHP and Mysql.
I used above statement in my project. I found following error message

Fatal error: Maximum execution time of 60 seconds exceeded in C:\Program Files\xampp\htdocs\iCRM\export_notyetcall.php on line74

I don't know what happened? I think, this statement is not ok for large database.

Do you have another way to acheive this.

Thanks......
Jun 20 '07 #7
r035198x
13,262 8TB
Hi, I'm just new in PHP and Mysql.
I used above statement in my project. I found following error message

Fatal error: Maximum execution time of 60 seconds exceeded in C:\Program Files\xampp\htdocs\iCRM\export_notyetcall.php on line74

I don't know what happened? I think, this statement is not ok for large database.

Do you have another way to acheive this.

Thanks......
Technically this design is not very good in the first place. The books table should have a field indicating whether the book is in or not, or maybe the id of the person who took it out.

Besides limiting the number of colums returned, I cannot see another way of optimizing that (it could be there but I don't know it).

Another thing, how big is your database? Can you run other commands fine besides this one?
Jun 20 '07 #8
i want to make same application as we are doing a forum
i hae two table 1 is for question & 2nd is their answer
now i have retrived data from table but i wanted to display them in one table which control i use

means i want to display my retrived data in table form in page i used grid view but it display only table

so which control i use?
Dec 25 '10 #9
AutumnsDecay
170 100+
archit mehta,

Please create a new thread for your question and I'm sure someone will try to help you.

Please don't post a question inside someone else's thread.

Regards.
Dec 26 '10 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

8
by: Rigga | last post by:
Hi, I am new to mysql and need help on how to join tables. I have a database which contains 4 tables, the main table contains information by date order and the other 3 contain data also in date...
3
by: dk | last post by:
Hi all, Would appreciate some advice on the following: I am trying to speed up an Access database connected to a SQL Server back-end. I know I can use a pass-through query to pass the sql...
2
by: Justin Koivisto | last post by:
In PHP, I do the following (pseudo code for clarity)... Query: SELECT distinct(web_contacts.zip) FROM web_contacts WHERE web_contacts.zip <> '' Query: SELECT DISTINCT(contacts.zip) FROM contacts...
8
by: Carl | last post by:
Hi, I hope someone can share some of their professional advice and help me out with my embarissing problem concerning an Access INSERT query. I have never attempted to create a table with...
1
by: Simon1234 | last post by:
Below you will see my code to read data from a database and store it into a dataset. The first, commented code uses an SQLDataReader and the second, uncommented code uses the Fill method of the...
8
by: Jim in Arizona | last post by:
I've been using an example out of a book to be able to edit the rows in a database. I am getting the following error: ========================================================...
10
by: Raj | last post by:
I have an MDC index on Big_A.Dt column. The following query always goes for a table scan. SELECT Key, Cd, Dt, SUM(Big_A ) FROM ( SELECT Big_A.Key , small_3.Cd,
0
by: The Frog | last post by:
Hello Everyone, I have been asked to try and create a single SQL query to retrieve product information from a database. The way that data is arranged is that in some tables there are user...
6
by: jsacrey | last post by:
Hey everybody, got a secnario for ya that I need a bit of help with. Access 97 using linked tables from an SQL Server 2000 machine. I've created a simple query using two tables joined by one...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.