joining tables, then joining to another database 1

[boopers]

I need help in combining two sql queries I have written. One combines data from two tables in one database, one combines data from two databases on the same server.

select *
from visit
join visitapptlist
on visit.visit_id = visitapptlist.visit_id

Select *
from psmprod.dbo.casemain, prod.dbo.appt
where (psmprod.dbo.casemain.appt_id=prod.dbo.visitapptlist.appt_id)

I can run these independently and get what I need. What is the proper format or syntax to combining them?

 

[topher0303]

The first one is the proper method. The second is the old way.

What are do you mean by "combining"?

Chris.

 

[MeanGreen]

Try this:

select Column1, Column2, Column3
from visit
join visitapptlist
on visit.visit_id = visitapptlist.visit_id
UNION
Select Column1, Column2, Column3
from psmprod.dbo.casemain, prod.dbo.appt
where (psmprod.dbo.casemain.appt_id=prod.dbo.visitapptlist.appt_id)

Hope this helps.

 

[Kurniadi]

Hi boopers...

I don't know, your mean "Combining".
But I think, you can to joining for All Tables (visit,visitapptlist,casemain) with JOIN on one SQL query syntaxt.

Thank's.
Kurniadi

 

[boopers]

toper0303-
when you say the first one is the new method, are you talking about the syntax on my join?
I wrote the second one in the suggested format when combining data from two databases that are on the same server.

Meangreen-the UNION operator doesn't work, unless you have the same type of columns (# and type of columns) across both queries.

Everyone-
What I mean by combining, is that I have to join the first two table in order to get both the data I need from the first table, join the second to get a field that will be in common with data in the other database. The only reason I am doing the first join is to get a common field. The data i need in this instance is from the 'visit' table and the 'casemain' table.

My last thought is to create a new table with the joint database data, then join the rest of the tables. trying to avoid that though.

 

[SQLSister]

Why not just put both joins in your query? You aren't limited to one. You could try something like:
select *
from visit
join visitapptlist
on visit.visit_id = visitapptlist.visit_id
Join psmprod.dbo.casemain
on smprod.dbo.casemain.appt_id=visitapptlist.appt_id

By the way it is better to specify which columns you actually need rather than using *.

 

[boopers]

SQLSister-Thank you!!! That worked perfectly.
I wasn't sure if I could use multiple joins if I was also crossing databases. Not something I usually have to do.

Thanks everyone! I just joined this forum, it's great!!

 

[topher0303]

Boopers,
Yes, when I spoke of new method vs. old method, it was the syntax. The suggested method is to use the JOIN keyword in your FROM clause. Where as, the old method joined the columns in the WHERE clause. The where clause should be used to filter rather than join.

Chris.

 

[Kurniadi]

Hi boopers...
To do the crossing database, it's no problem. Don't forget, you must write database name. Like this :

SELECT database1.table.* from database1.table INNER JOIN
database2.table ON database1.table.field =
database2.table.field

Thank's.

Kurniadi