Solved

MsSQL tables join

Posted on 2011-09-29
5
212 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
ID: 36817523
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
ID: 36817553
Could you please write it ou completely? I never used union before. Thanks,
0
 
LVL 1

Author Comment

by:cnshealthcare
ID: 36817572
acctually I got it! thanks!
0
 
LVL 18

Expert Comment

by:lludden
ID: 36817588
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
ID: 36891342
COOL! Learn something new every day!
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to manage encyps queries mssql when sending conditioning values 10 37
replication - alerts? 4 30
Strange msg in the SSMS pane 13 47
Generate Weekly Schedule 15 13
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…

932 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