SQL Queries

Searching 2 databases and comparing data

Page 1 of 1

2 Replies - 1552 Views - Last Post: 09 October 2006 - 07:37 AM Rate Topic: -----

#1 Bort  Icon User is offline

  • Ill-informed Mongoloid
  • member icon

Reputation: 445
  • View blog
  • Posts: 3,084
  • Joined: 18-September 06

SQL Queries

Post icon  Posted 06 October 2006 - 02:10 AM

Hi,

I'm working on an electronic register which people use to sign in and out of a building. It uses 3 tables, one to store personal information (Name & Visitor Number), one to store what time they come in, and one to store what time they leave. Visitor Number is the primary key in the Visitor_Info table, and is used as a foreign key in the other 2 tables.

I've been asked to put in an'emergency print' function where, when you click the print button, it compares the data in the time in database and the time out database and prints a list of people still in the building.

So, what I'm trying to do is create a SQL query that will give me this information. Having never used SQL before, it's a little tricky. I've spent the last few days looking at tutorials on the net and learning about queries and this is what I've come up with:

Select Time_In_Table.Visitor_Number
Not Union
Select Time_Out_Table.Visitor_Number

If I'm right (and I'm probably not), this will give me the people who are in the time in table, but not in the time out table.

Can someone please help me by letting me know what I'm doing wrong in the statement above, and also, advise me as to where it goes since none of the tutorials I've found cover where to put the statement in my programming? At a guess, I'd say it goes in the print button click function. I'm using VB 2005 Express Edition.

Is This A Good Question/Topic? 0
  • +

Replies To: SQL Queries

#2 Trogdor  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 15
  • View blog
  • Posts: 627
  • Joined: 06-October 06

Re: SQL Queries

Posted 06 October 2006 - 03:51 PM

Hi.
First of all i want to point out that this might be one of the worst possible ways to design your database structure.
It will be a lot better to have the arrival datetime and leaving datetime in the same table, coupled to one visitor number.
But seeing that you allready created the tables, lets help with what you have.

One quite efficient way to do a query of this type would be the following:

SELECT yourdata FROM Time_In_Table
LEFT OUTER JOIN Time_Out_Table ON Time_In_Table.Visitor_Number = Time_Out_Table.Visitor_Number
WHERE Time_Out_Table.Visitor_Number IS NULL

What that basicaly is is the following:
Couple all matchable records from the time_out table on all reccords of the time_in table, then filter keeping only the reccords where the time_out part is missing.

A couple quick checks:
- You are sure that Visitor_Number is Unique? If not (if they are recycled, based on a pass number for example) this whole design is completely flawed.
- Make sure there are indexes defined on the Visitor_Numbers fields.
- Are you sure it is not better to put the in and out data in one table instead of 2 seperate ones?
The checking in and out are symetric events related to the Unique Visitor_Numbers afterall.
- Will the tables be cleaned up or end having milions of records after a while? that will completely kill performance. Nobody wants to wait minutes on a simple questions' answer, right?

This post has been edited by Trogdor: 06 October 2006 - 03:52 PM

Was This Post Helpful? 0
  • +
  • -

#3 Bort  Icon User is offline

  • Ill-informed Mongoloid
  • member icon

Reputation: 445
  • View blog
  • Posts: 3,084
  • Joined: 18-September 06

Re: SQL Queries

Posted 09 October 2006 - 07:37 AM

Thanks for the advice Trogdor. Here are the answers to your questions.


View PostTrogdor, on 6 Oct, 2006 - 03:51 PM, said:

- You are sure that Visitor_Number is Unique? If not (if they are recycled, based on a pass number for example) this whole design is completely flawed.


The Visitor Number will be unique to each person in the database, not a pass, but the actual person.

View PostTrogdor, on 6 Oct, 2006 - 03:51 PM, said:

- Make sure there are indexes defined on the Visitor_Numbers fields.


I'm sorry, I'm not quite sure if I follow you here. The Visitor_Number field is used as a primary key for table 1 (Visitor_Info), and as foreign keys for tables 2 & 3 (Time_In & Time_Out), so it will be easy for tables 2 & 3 to use the Visitor_Number as an index for peoples details. If this isn't what you meant, could you please explain it?

View PostTrogdor, on 6 Oct, 2006 - 03:51 PM, said:

- Are you sure it is not better to put the in and out data in one table instead of 2 seperate ones?
The checking in and out are symetric events related to the Unique Visitor_Numbers afterall.


Quite possibly, but I am very new to this whole thing, so I am trying to stick to what I know works (more or less). I'll probably change this in a later version, but for now I'll keep it the way it is.

View PostTrogdor, on 6 Oct, 2006 - 03:51 PM, said:

- Will the tables be cleaned up or end having milions of records after a while? that will completely kill performance. Nobody wants to wait minutes on a simple questions' answer, right?


To begin with, it won't be a problem. We get very few visitors. Even so, I'll want tp leave table1 collecting records (just so it has everyones details on file), but tables 2 & 3 will be cleared maybe once a month, once a week if things start getting busy. I might see if I can put another table or database in somewhere down the line, just to save the cleared data to so it's not clogging up tables 2 & 3.

This post has been edited by Bort: 09 October 2006 - 07:38 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1