joins

Hi Friends
I need ur advise on this one.
when writing sql statements and whenever there is need to join 2 or more tables am used to the following syntax (using WHERE to join tables)

select a.name,b.code from table a,table b where a.code=b.code

as you all know we can also use INNER JOINS,OUTER JOINS etc., to join tables and all BOL help example contains this type of syntax.

my question is , am having difficulty using/writing this syntax in my queries .meaning uysing left joins,inner joins in the same queries.I appreciate if u guys can direct me to some good articles or that sort of thing to improve my understanding of this type of syntax.
Thanks

Hi,

IMHO, the information you need is in BOL. As you have mentioned, it contains samples perjorming JOIN querries. All you have to do is experiment with it to get the idea behind it. Use Query Analyzer to achieve this.

cheers,

Paul June A. Domag

|||

See if these help:

--inner join
select a.name,b.code
from table a inner join table b on a.code=b.code

--left join
select a.name,b.code
from table a left join table b on a.code=b.code

--right join
select a.name,b.code
from table a right join table b on a.code=b.code

--full join
select a.name,b.code
from table a full join table b on a.code=b.code

--cross join
select a.name,b.code
from table a cross join table b

|||See if this helps:
http://www.firstsql.com/tutor.htm and especially this http://www.firstsql.com/tutor3.htm#join
--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. (http://www.sqlpass.de)|||Thanks Frank.That helps mate |||

Hi Paul

Seems like you are very helpful.........

Please assist.

i have several document groups in a table which data i want to export. my statement is: select * from docs_indexed where di_doc_group = 71. this is working and i get the correct results. problem is, i have 350 doc_groups. how do i create a query where i can get results for all the doc_groups in one query?

Regards

Hannes

joins

Hi Friends
I need ur advise on this one.
when writing sql statements and whenever there is need to join 2 or more tables am used to the following syntax (using WHERE to join tables)

select a.name,b.code from table a,table b where a.code=b.code

as you all know we can also use INNER JOINS,OUTER JOINS etc., to join tables and all BOL help example contains this type of syntax.

my question is , am having difficulty using/writing this syntax in my queries .meaning uysing left joins,inner joins in the same queries.I appreciate if u guys can direct me to some good articles or that sort of thing to improve my understanding of this type of syntax.
Thanks

Hi,

IMHO, the information you need is in BOL. As you have mentioned, it contains samples perjorming JOIN querries. All you have to do is experiment with it to get the idea behind it. Use Query Analyzer to achieve this.

cheers,

Paul June A. Domag

|||

See if these help:

--inner join
select a.name,b.code
from table a inner join table b on a.code=b.code

--left join
select a.name,b.code
from table a left join table b on a.code=b.code

--right join
select a.name,b.code
from table a right join table b on a.code=b.code

--full join
select a.name,b.code
from table a full join table b on a.code=b.code

--cross join
select a.name,b.code
from table a cross join table b

|||See if this helps:
http://www.firstsql.com/tutor.htm and especially this http://www.firstsql.com/tutor3.htm#join
--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. (http://www.sqlpass.de)
|||Thanks Frank.That helps mate |||

Hi Paul

Seems like you are very helpful.........

Please assist.

i have several document groups in a table which data i want to export. my statement is: select * from docs_indexed where di_doc_group = 71. this is working and i get the correct results. problem is, i have 350 doc_groups. how do i create a query where i can get results for all the doc_groups in one query?

Regards

Hannes

Join statement

Hi.......

Its been awhile since I've touched SQL statements, so I need some help
with writing a JOIN statement to query 3 tables.

The dB has 3 tables with values
Applications
-Application_code(Primary key)
-Application_name

Applications_Installed
-Computer_name(Pri key)
-Application_code(Foreign key/sec key)

Workstation_info
-Computer_name(Pri key)
-Serial_number

What I want to do is query the tables for a particular Application code
and name from Applications, so that it returns the values of computer
names with the matching values from Applications_installed and
Workstation_info.

So I need to do a

Select * from applications where applications.application_code='XXX'
join (this is the part I'm stuck how do I tell it to match the
applications.application_code =
applications_installed.application_code) then match the computer names
from Applications_installed.computer_name with that of
workstation_info.computer_name

Not sure if I'm explaining this properly...

Can anyone help......Hey Zeno,

I think what you're looking for is:

SELECT columnlist
FROM Applications a JOIN Applications_Installed ai ON
a.Application_Code = ai.Application_Code
JOIN Workstation_Info wi ON ai.Computer_Name = wi.Computer_name
WHERE a.application_code = 'XXX'

May I also suggest that you pick up a reference guid on basic SQL;
newsgroups can be harsh teachers :) Years ago, I found Ben Forta's 10
Minute Guide very good at covering the basic concepts:

http://www.amazon.com/exec/obidos/t...524660?v=glance

HTH,
Stu|||Thanks Stu.......

I'll give it a try and let you know how things go.........

Cheers|||Thanks Stu.......

I'll give it a try and let you know how things go.........

Cheers|||use ACCESS DATA PROJECTS they allow you to drag and drop fields around
instead of getting frustrating writing queries.

TSQL is too hard to just dive into -- use ADP as a crutch to help you
to start getting things done and stop worrying about syntax.|||(aaron.kempf@.gmail.com) writes:
> use ACCESS DATA PROJECTS they allow you to drag and drop fields around
> instead of getting frustrating writing queries.
> TSQL is too hard to just dive into -- use ADP as a crutch to help you
> to start getting things done and stop worrying about syntax.

I would say that this is a poor advice in the long run. Maybe if you
only compose a query once or twice a year maybe. But else learning
SQL syntax pays off.

And using Access may not necessarily be helpful, since there are
significant differences between the SQL in Access and Transact-SQL.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Join Query

Hi,

Please help me in writing a join query.

I have three tables with three columns each. Now I want to retrieve data from all the three

tables as one.

Table 1: EmpId, Date, Points
Table 2: EmpId, Date, Points
Table 3: EmpId, Date, Points

These are related to three different divisions. So, on analysis we have get the no. of points

accumulated on a day. On any day the points can be in all three divisions or in any one or two

divisions. My database SQL Server 2000.

So, how to get this sort of output.

Empid Date Pts(DIv1) Pts(DIv2) Pts(DIv3)
V001 07-24-2004 Null Null 25
V002 07-24-2004 20 Null 25
V003 07-24-2004 Null 30 NUll
V001 07-23-2004 15 Null NUll
V002 07-23-2004 10 25 25
V001 07-22-2004 Null 10 25

I'm badly in need of help. Any sort of help is appreciated.

M.L.Srinivas<code>
SELECT T1.EmpId,T1.Date,T1.points as Div1pts,T2.points as Div2pts,T3.points as Div3pts FROM Table1 as T1
INNER JOIN Table2 as T2 on T1.EmpId=T2.EmpId and T1.Date=T2.Date
INNER JOIN Table3 as T3 on T2.EmpId=T3.EmpId and T2.Date=T3.Date
WHERE <i>condition</i>
</code>|||Hi,

Thanks for your reply..as you used inner join it works only for dates which are available in table1. What if the person scored only in second or third div. on a day and not in first div.

Please see my data sample carefully..i beleive we have to use full outer join...i was succesful for two tables..but how about three tables...

here is my query

select 'Tdate'= case
when a.date is null then b.date
else a.date
end,
a.points,b.points from table1 as a full outer join table2 as b
on a.empid=b.empid and a.date=b.date

Please guide me

M.L.Srinivas|||<code>
SELECT T.EmpId,T.Date,T1.points as Div1pts,T2.points as Div2pts,T3.points as Div3pts FROM
(SELECT EmpId,Date FROM Table1
UNION
SELECT EmpId,Date FROM Table2
UNION
SELECT EmpId,Date FROM Table3
) as T
LEFT JOIN Table1 as T1 ON T.EmpId=T1.EmpId and T.Date=T1.Date
LEFT JOIN Table2 as T2 ON T.EmpId=T2.EmpId and T.Date=T2.Date
LEFT JOIN Table3 as T3 ON T.EmpId=T3.EmpId and T.Date=T3.Date
WHERE condition
</code>