Joining multiple tables

Hi,
I need write a script get data from 3 different. All 3 tables will have
emailAddr, modify_date and sum details. But information may be duplicated in
the 3 tables, having same emailAddr but different modify_date eg. My end
result(new table) will contain the entry with latest modify_date if there is
a duplication of emailAddr, else the entry that is only available in single
table will also be insert into the new table.
Thanx..Can you give the primary key foriegn key trlationships with the table
definition and few sample data and expected result?
--
"yingying" wrote:

> Hi,
> I need write a script get data from 3 different. All 3 tables will have
> emailAddr, modify_date and sum details. But information may be duplicated
in
> the 3 tables, having same emailAddr but different modify_date eg. My end
> result(new table) will contain the entry with latest modify_date if there
is
> a duplication of emailAddr, else the entry that is only available in singl
e
> table will also be insert into the new table.
> Thanx..|||The tables are not related to each other.. only field that is similiar is th
e
emailAddr.. this is the field i expected to check..
Eg.. (table structure)
tblA (a_Id, emailAddr, dateModify, fname, lname)
tblB (b_Id, emailAddr, dateModify, addr, contact)
tblC (c_Id, emailAddr, dateModify, work1, work2)
tblResult (r_Id, emailAddr, dateModify)
(data in db)
tblA
1, aa@.abc.com, 29/04/2006, aa, aa
2, bb@.abc.com, 28/04/2006, bb, bb
tblB
1, aa@.abc.com, 30/04/2006, bb, bb
2, cc@.abc.com, 01/05/2006, cc, cc
3, bb@.abc.com, 03/05/2006, bb, bb
tblC
1, aa@.abc.com, 03/05/2006, aa, aa
2, dd@.abc.com, 01/05/2006, dd, dd
(Expected result stored into tblResult)
tblResult
1, aa@.abc.com, 03/05/2006
2, bb@.abc.com, 03/05/2006
3, cc@.abc.com, 01/05/2006
4, dd@.abc.com, 01/05/2006
"Omnibuzz" wrote:
> Can you give the primary key foriegn key trlationships with the table
> definition and few sample data and expected result?
> --
>
>
> "yingying" wrote:
>|||try this and let me know if this was what you required
select emailAddr,max(datemodify) as datemodify
from
(
select emailAddr,datemodify from tblA
union all
select emailAddr,datemodify from tblB
union all
select emailAddr,datemodify from tblC
) as A
group by emailaddr|||of course r_id in the tbl_result can be an identity
--
"yingying" wrote:

> Hi,
> I need write a script get data from 3 different. All 3 tables will have
> emailAddr, modify_date and sum details. But information may be duplicated
in
> the 3 tables, having same emailAddr but different modify_date eg. My end
> result(new table) will contain the entry with latest modify_date if there
is
> a duplication of emailAddr, else the entry that is only available in singl
e
> table will also be insert into the new table.
> Thanx..|||Thanx.. I got the idea how it works..
Another question.. If i was to have same email addr in the same table how am
i goin to get the data with the latest date having the same email addr.
(table structure)
tblA (a_Id, emailAddr, dateModify, lname, fname)
(data in table)
tblA
1, aa@.abc.com, 03/05/2006, aa, aa
2, aa@.abc.com, 01/05/2006, bb, bb
3, aa@.abc.com, 29/04/2006, cc, cc
4, dd@.abc.com, 01/05/2006, dd, dd
5, dd@.abc.com, 02/05/2006, ee, ee
6, ff@.abc.com, 01/05/2006, ff, ff
(Expected result)
1, aa@.abc.com, 03/05/2006, aa, aa
5, dd@.abc.com, 02/05/2006, ee, ee
6, ff@.abc.com, 01/05/2006, ff, ff
"Omnibuzz" wrote:

> try this and let me know if this was what you required
> select emailAddr,max(datemodify) as datemodify
> from
> (
> select emailAddr,datemodify from tblA
> union all
> select emailAddr,datemodify from tblB
> union all
> select emailAddr,datemodify from tblC
> ) as A
> group by emailaddr
>|||The same query will work for your requirement.
--
"yingying" wrote:
> Thanx.. I got the idea how it works..
> Another question.. If i was to have same email addr in the same table how
am
> i goin to get the data with the latest date having the same email addr.
> (table structure)
> tblA (a_Id, emailAddr, dateModify, lname, fname)
> (data in table)
> tblA
> 1, aa@.abc.com, 03/05/2006, aa, aa
> 2, aa@.abc.com, 01/05/2006, bb, bb
> 3, aa@.abc.com, 29/04/2006, cc, cc
> 4, dd@.abc.com, 01/05/2006, dd, dd
> 5, dd@.abc.com, 02/05/2006, ee, ee
> 6, ff@.abc.com, 01/05/2006, ff, ff
> (Expected result)
> 1, aa@.abc.com, 03/05/2006, aa, aa
> 5, dd@.abc.com, 02/05/2006, ee, ee
> 6, ff@.abc.com, 01/05/2006, ff, ff
>
> "Omnibuzz" wrote:
>|||I used same query but it did not return the right values i need..
select emailAddr, max(datestamp) as datemodify, fname, lname into #temp
from
(
select emailAddr, datestamp, fname, lname from tblA
) As A
group by emailAddr, fname, lname
(data in tblA)
1, aa@.abc.com, 29/04/2006, aa, aa
2, bb@.abc.com, 03/05/2006, bb, bb
3, cc @.abc.com, 03/05/2006, cc, cc
4, aa@.abc.com, 03/05/2006, aa2, aa2
(Expected result)
2, bb@.abc.com, 03/05/2006, bb, bb
3, cc @.abc.com, 03/05/2006, cc, cc
4, aa@.abc.com, 03/05/2006, aa2, aa2
but wat i got was
2, bb@.abc.com, 03/05/2006, bb, bb
3, cc @.abc.com, 03/05/2006, cc, cc
1, aa@.abc.com, 29/04/2006, aa, aa
4, aa@.abc.com, 03/05/2006, aa2, aa2
If cases with entry of same email addr, i will need to get the entry with
the latest date. When i didn't include the fname and lname, the result was
ok.. but after i add those 2 fields, the result was not wat i need.
Isit that i need to do this in 2 different steps in order to get the
required data'
Another question, when i have fname, lname in the 'select' query and not
having them in the 'group by', it gave me an error..
-->>
'A.fname' is invalid in the select list because it is not contained in
either an aggregate function or the GROUP BY clause.
what does this mean?
Thanx..
"Omnibuzz" wrote:
> The same query will work for your requirement.
> --
>
>
> "yingying" wrote:
>|||If you want the additional details with the latest date, try
select
emailAddr, datestamp as datemodify, fname, lname
into #temp
from tblA
where datestamp = (
select max(datestamp) from tblA as A2
where A2.emailAddr = tblA.emailAddr
)
nearly equivalent variations (differing in the case
where there are ties for the datestamp value, or
nullable columns) include
...
from tblA
where datestamp = (
select top 1 datestamp from tblA as A2
where A2.emailAddr = tblA.emailAddr
order by datestamp desc
)
and
...
from tblA
where not exists (
select * from tblA as A2
where A2.emailAddr = tblA.emailAddr
and A2.datestamp > tblA.datestamp
)
or in SQL Server 2005,
with Ranked(emailAddr, datestamp, fname, lname, rk) as (
select
emailAddr, datestamp, fname, lname,
rank() over (partition by emailAddr order by datestamp desc)
from tblA
)
select emailAddr, datestamp as datemodify, fname, lname
into #temp
from Ranked
where rk = 1
Steve Kass
Drew University
yingying wrote:
>I used same query but it did not return the right values i need..
>select emailAddr, max(datestamp) as datemodify, fname, lname into #temp
>from
>(
>select emailAddr, datestamp, fname, lname from tblA
> ) As A
>group by emailAddr, fname, lname
>(data in tblA)
>1, aa@.abc.com, 29/04/2006, aa, aa
>2, bb@.abc.com, 03/05/2006, bb, bb
>3, cc @.abc.com, 03/05/2006, cc, cc
>4, aa@.abc.com, 03/05/2006, aa2, aa2
>(Expected result)
>2, bb@.abc.com, 03/05/2006, bb, bb
>3, cc @.abc.com, 03/05/2006, cc, cc
>4, aa@.abc.com, 03/05/2006, aa2, aa2
>but wat i got was
>2, bb@.abc.com, 03/05/2006, bb, bb
>3, cc @.abc.com, 03/05/2006, cc, cc
>1, aa@.abc.com, 29/04/2006, aa, aa
>4, aa@.abc.com, 03/05/2006, aa2, aa2
>If cases with entry of same email addr, i will need to get the entry with
>the latest date. When i didn't include the fname and lname, the result was
>ok.. but after i add those 2 fields, the result was not wat i need.
>Isit that i need to do this in 2 different steps in order to get the
>required data'
>Another question, when i have fname, lname in the 'select' query and not
>having them in the 'group by', it gave me an error..
>-->>
>'A.fname' is invalid in the select list because it is not contained in
>either an aggregate function or the GROUP BY clause.
>what does this mean?
>Thanx..
>"Omnibuzz" wrote:
>
>|||If you are selecting more columns than what you had specified, then you will
have to use a correlated sub-query.
--
"yingying" wrote:
> I used same query but it did not return the right values i need..
> select emailAddr, max(datestamp) as datemodify, fname, lname into #temp
> from
> (
> select emailAddr, datestamp, fname, lname from tblA
> ) As A
> group by emailAddr, fname, lname
> (data in tblA)
> 1, aa@.abc.com, 29/04/2006, aa, aa
> 2, bb@.abc.com, 03/05/2006, bb, bb
> 3, cc @.abc.com, 03/05/2006, cc, cc
> 4, aa@.abc.com, 03/05/2006, aa2, aa2
> (Expected result)
> 2, bb@.abc.com, 03/05/2006, bb, bb
> 3, cc @.abc.com, 03/05/2006, cc, cc
> 4, aa@.abc.com, 03/05/2006, aa2, aa2
> but wat i got was
> 2, bb@.abc.com, 03/05/2006, bb, bb
> 3, cc @.abc.com, 03/05/2006, cc, cc
> 1, aa@.abc.com, 29/04/2006, aa, aa
> 4, aa@.abc.com, 03/05/2006, aa2, aa2
> If cases with entry of same email addr, i will need to get the entry with
> the latest date. When i didn't include the fname and lname, the result was
> ok.. but after i add those 2 fields, the result was not wat i need.
> Isit that i need to do this in 2 different steps in order to get the
> required data'
> Another question, when i have fname, lname in the 'select' query and not
> having them in the 'group by', it gave me an error..
> -->>
> 'A.fname' is invalid in the select list because it is not contained in
> either an aggregate function or the GROUP BY clause.
> what does this mean?
> Thanx..
> "Omnibuzz" wrote:
>

join two different databases where they r in different servers

i am fresher i want to know write one query where i want to select two tables from two different databases that are in different servers.
eg : it is first server details
sqlserver1 -servername
EmployeDet -Database Name
Emp_Mas - table name
colums - empno, empname,.....
it is second server details
sqlserver2 -servername
IMS- Database Name
IMSTable - table name
Colums- empno,locid,extno......
by combining above two databases i want details please help me in this issue it is urgent
You can do that using linked servers. Books online has quite a bit of
information about creating and using a linked server.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Naga vinay kumar .K" <anonymous@.discussions.microsoft.com> wrote in message
news:8F753D93-EB6A-4436-977D-436D92EE99B8@.microsoft.com...
> i am fresher i want to know write one query where i want to select two
tables from two different databases that are in different servers.
> eg : it is first server details
> sqlserver1 -servername
> EmployeDet -Database Name
> Emp_Mas - table name
> colums - empno, empname,.....
> it is second server details
> sqlserver2 -servername
> IMS- Database Name
> IMSTable - table name
> Colums- empno,locid,extno......
> by combining above two databases i want details please help me in this
issue it is urgent
>
>

join two different databases where they r in different servers

i am fresher i want to know write one query where i want to select two table
s from two different databases that are in different servers.
eg : it is first server details
sqlserver1 -servername
EmployeDet -Database Name
Emp_Mas - table name
colums - empno, empname,.....
it is second server details
sqlserver2 -servername
IMS- Database Name
IMSTable - table name
Colums- empno,locid,extno......
by combining above two databases i want details please help me in this issue
it is urgentYou can do that using linked servers. Books online has quite a bit of
information about creating and using a linked server.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Naga vinay kumar .K" <anonymous@.discussions.microsoft.com> wrote in message
news:8F753D93-EB6A-4436-977D-436D92EE99B8@.microsoft.com...
> i am fresher i want to know write one query where i want to select two
tables from two different databases that are in different servers.
> eg : it is first server details
> sqlserver1 -servername
> EmployeDet -Database Name
> Emp_Mas - table name
> colums - empno, empname,.....
> it is second server details
> sqlserver2 -servername
> IMS- Database Name
> IMSTable - table name
> Colums- empno,locid,extno......
> by combining above two databases i want details please help me in this
issue it is urgent
>
>

join two different databases where they r in different servers

i am fresher i want to know write one query where i want to select two tables from two different databases that are in different servers
eg : it is first server detail
sqlserver1 -servernam
EmployeDet -Database Nam
Emp_Mas - table nam
colums - empno, empname,....
it is second server detail
sqlserver2 -servernam
IMS- Database Nam
IMSTable - table nam
Colums- empno,locid,extno.....
by combining above two databases i want details please help me in this issue it is urgenYou can do that using linked servers. Books online has quite a bit of
information about creating and using a linked server.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Naga vinay kumar .K" <anonymous@.discussions.microsoft.com> wrote in message
news:8F753D93-EB6A-4436-977D-436D92EE99B8@.microsoft.com...
> i am fresher i want to know write one query where i want to select two
tables from two different databases that are in different servers.
> eg : it is first server details
> sqlserver1 -servername
> EmployeDet -Database Name
> Emp_Mas - table name
> colums - empno, empname,.....
> it is second server details
> sqlserver2 -servername
> IMS- Database Name
> IMSTable - table name
> Colums- empno,locid,extno......
> by combining above two databases i want details please help me in this
issue it is urgent
>
>

Join tables and exclude records...

Hi

I am trying to write a SQL query to run against 2 Oracle tables - tblNames and tblAbsence.

Let's say the tables look like this:

tblNames:

Andrew
David
John
Michael

and tblAbsence:

Andrew 01/01/05 Sick
Andrew 01/02/05 Sick
David 01/07/05 Doctor's appointment

What I need to do is to create a report that lists all of the absences from tblAbsence, plus a row for anyone in the tblNames table with no date and "In Office" in the third column if they don't have an entry in tblAbsence.

To use the above values, what I should have in my resultset is:

Andrew 01/01/05 Sick
Andrew 01/02/05 Sick
David 01/07/05 Doctor's appointment
John Null In Office
Michael Null In Office

I'm not sure how to do this. I did try a UNION of two queries, but the nearest I have been able to get is to reproduce the entries in the tblAbsence table plus all of the records in tblNames. I need to exclude the names from tblNames if they have an entry in tblAbsence.

Any ideas?

Thanks

MichaelDifferent versions of Oracle have different levels of support for standard SQL, but the way that I'd do it would be:SELECT n.Name
, a.date, Coalesce(a.comment, 'In Office)
FROM tblNames AS n
LEFT OUTER JOIN tblAbsence AS a
ON (a.name = n.name)-PatP|||Another suggestion:SELECT n.name, a.dtm, NVL(a.status, 'In office') status
FROM tblNames n, tblAbsence a
WHERE n.name = a.name (+);|||Another suggestion:SELECT n.name, a.dtm, NVL(a.status, 'In office') status
FROM tblNames n, tblAbsence a
WHERE n.name = a.name (+);Good point, but if the poster is just starting out I think it would be better to start them with standard SQL and only use eingine specific features if they are required. The closer we can keep the new users to standards, the less likely they are to get hurt by the odd quirks that we've come to know and love!

-PatP|||I agree, Pat ... however, Robojan said it is about Oracle tables so I guess this Oracle specific code won't hurt much :)|||Thanks guys - this helped me to write the query. I simplified it greatly for this (removing joins, etc.), and using the joins correctly fixed it for me.

Thanks

Join Issues

I am trying to write a query to show me every thing from table 1 (table one is called equipment) and only the information from table 2 (called equip_out) related to the equipment item with some restrictions

equipment
equip_location = variable
deleted = 0

equip_out
equip_in = null

SELECT equipment.equip_name, equip_out.student_id, equip_out.time_out
FROM equipment
full outer JOIN equip_out ON equipment.equip_id=equip_out.equip_id
where equipment.deleted = 0

there is a column called time_in in equip_out that should only be joined with the equipment list if that field is null. the equipment list should include all fields where the column deleted is 0 (bc of logging issues i do not want anything removed ever) and equip_location = variable (variable is passed in when the query is built in vb)

this one has me stumped, if its not possible just let me know. i am going to keep working on it and will post a solution if i find one.

thanks in advancewell i have a solution, but it does not seem like it would be the best available one so if someone comes up with one better that would be great.

my solution is to use a view (stored procedure equiv in sql 2k5 express) to do the limiting on equip_out and then use that view in the join|||What? A view is the equivelent to a stored procedure in sql 2k5 express? When did that happen?|||select equipment.equip_name
, equip_out.student_id
, equip_out.time_out
from equipment
left outer
join equip_out
on equip_out.equip_id = equipment.equip_id
and equip_out.time_in is null
where equipment.deleted = 0