Solved

MsSQL tables join

Posted on 2011-09-29
5
208 Views
Last Modified: 2012-05-12
Hello all,

I have 2 tables that keep track of appointments. I am writing a code for scheduling and I need to query both tables, at the same time, and order the results by appointment time. (appt_time in one, and visit_time in the other). I am thinking I need to create a view, but I am not sure about the join. It would be helpful if it could also give me which table the record came from. There is no relation between the records. Could someone assist me with this?

Thanks,

Tales
0
Comment
Question by:cnshealthcare
  • 3
  • 2
5 Comments
 
LVL 18

Accepted Solution

by:
lludden earned 500 total points
Comment Utility
You just need to match up the fields from both  tables and do a UNION

SELECT Appt_Date AS ApptDate, Appt_Time as ApptTime FROM A
UNION
SELECT Visit_Date. Visit_Time FROM B

You can put any where clauses you wish on each query, or even one on the query as a whole
0
 
LVL 1

Author Comment

by:cnshealthcare
Comment Utility
Could you please write it ou completely? I never used union before. Thanks,
0
 
LVL 1

Author Comment

by:cnshealthcare
Comment Utility
acctually I got it! thanks!
0
 
LVL 18

Expert Comment

by:lludden
Comment Utility
What all fields do you want with what criteria?

UNION just takes the results of two queries and returns them as a single recordset.  The query I gave you will work, and will show all of the times and dates from each table.

If you create a query showing the information you want from table A, and write a query that shows the information you want from table B, and both queries have the same number of columns (and really, the same types of data), then you can just put a UNION between them.

SELECT Appt_Date AS ApptDate, Appt_Time as ApptTime FROM A WHERE Appt_Date >= CAST(COVERT(varchar(10),getdate(),110) AS datetime)
UNION
SELECT Visit_Date. Visit_Time FROM B WHERE Visit_Date >= CAST(COVERT(varchar(10),getdate(),110) AS datetime)

this will return all appts that are dated for the current day or later.
0
 
LVL 1

Author Comment

by:cnshealthcare
Comment Utility
COOL! Learn something new every day!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now