query to get the data of three tables?

Hi:
I have three tables in my db. One is clients, other calls, and other visits.
I want to get all the calls and visits of all clients in my db every row in each table separate in one row in the results table.
How can I do it?
Use the SQL JOIN functionality:
http://www.w3schools.com/sql/sql_join.asp
|||You can do this a couple of ways,
Let's say the tables look like this:
table calls
clientid,
calldate
table visits
clientid,
visitdate
table client
clientid,
clientname
You can get all of the data in one row by joining the three tables together, or you can get all of the calls data and all of the visits data (in separate rows) by using a union query.
First inner joining:
select c.ClientName, call.CallDate,visit.VisitDate from calls call inner join clients c on c.clientid = call.clientid inner join visits visit on c.clientid = visit.clientid
now for the union
select c.clientname, 'call' as type, call.calldate from clients c inner join calls call on c.clientid = call.clientid
union
select c.clientname 'visit' as type, visit.visitdate from clients c inner join visits visit on c.clientid = visit.clientid
Is this what you were looking for?
Hope this helps,
|||thanks so much.
The second one is exactly what i was looking for.
Thanks again

Query to count physician encounters

I need to count the total number of patient visits accumulated by each
provider in a medical practice between selected dates. I am using the table
of charges. Each visit may include several individual charges during the
visit, but I want to count the visit only once. Therefore I used this
statement (the date range is not actually hard coded):
SELECT DISTINCT ProviderCode, PatUniqueID, service_date
FROM Charges
WHERE service_date Between '01/01/1995' And '12/31/2005' ;
What I get is:
Provider Code PatUniqueID service_date
-- -- --
RMB 25AAAAAA 01/01/2005
RMB 983AAAAA 02/01/2005
etc.
However, what I readlly want is a count. How many of these lines occur for
provider RMB and each provider. The desired table would be:
Provider Code Encounter Count
-- --
RMB 25
JDR 51
etc.
I am hoping to do this in a single SQL query statements, possibly with
imbedded SELECT statements. Can anyone help me to write this statement,
please.Richard,
If the query you show returns exactly the rows you want to count
(by Provider Code), you can build from this query:
select ProviderCode, count(*) as EncounterCount from (
SELECT DISTINCT ProviderCode, PatUniqueID, service_date
FROM Charges
WHERE service_date Between '01/01/1995' And '12/31/2005'
) as RowsToCount
group by ProviderCode
Steve Kass
Drew University
richardb wrote:

>I need to count the total number of patient visits accumulated by each
>provider in a medical practice between selected dates. I am using the table
>of charges. Each visit may include several individual charges during the
>visit, but I want to count the visit only once. Therefore I used this
>statement (the date range is not actually hard coded):
>SELECT DISTINCT ProviderCode, PatUniqueID, service_date
>FROM Charges
>WHERE service_date Between '01/01/1995' And '12/31/2005' ;
>What I get is:
>Provider Code PatUniqueID service_date
>-- -- --
>RMB 25AAAAAA 01/01/2005
>RMB 983AAAAA 02/01/2005
>etc.
>However, what I readlly want is a count. How many of these lines occur for
>provider RMB and each provider. The desired table would be:
>Provider Code Encounter Count
>-- --
>RMB 25
>JDR 51
>etc.
>I am hoping to do this in a single SQL query statements, possibly with
>imbedded SELECT statements. Can anyone help me to write this statement,
>please.
>
>|||Right! Got it and thanks...
"Steve Kass" wrote:

> Richard,
> If the query you show returns exactly the rows you want to count
> (by Provider Code), you can build from this query:
> select ProviderCode, count(*) as EncounterCount from (
> SELECT DISTINCT ProviderCode, PatUniqueID, service_date
> FROM Charges
> WHERE service_date Between '01/01/1995' And '12/31/2005'
> ) as RowsToCount
> group by ProviderCode
> Steve Kass
> Drew University
> richardb wrote:
>
>