Solved

MsSQL tables join

Posted on 2011-09-29
5
219 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Union 2 queries to a cte (temp table perhaps) 9 49
Problem when I run a simple storeproc - help 4 29
PROPERCASE SCRIPT IN SQL 3 21
Using this function 4 38
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Detach & Attach 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.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

679 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