Joins Question

I have 3 tables. Table1(time,readingA) Table2(time,readingB)
table3(time,readingC)
Now the time can be same and it can be different. Now i want to know
how do i join so that i get the data: time,readingA,readingB,readingC
If the time is same then it is fine, but if the time is not same in
two tables for eg : if table1 has a record for time 12:30 and Table2
and table3 does not have that time then it should show data from
table1 and the readingB and readingC will be blank.
I hope my question is clear.
Thanks for helpHi
If you are not interested in the time then you may not want to truncate
everything to midnight when they are inserted (which if you don't have a tim
e
portion on your date/time will happen anyhow). The isssue then is what will
happen if there are multiple records for each day? If the time is require fo
r
some other reason you can also use the convert function to compare the date
part of the datetime
SELECT CONVERT(char(8),T1.time,112) AS Time, T1.readingA, T2.readingB,
T3.readingC
FROM Table1 T1
JOIN Table2 T2 ON CONVERT(char(8),T1.time,112) = CONVERT(char(8),T2.time,112
)
JOIN Table3 T3 ON CONVERT(char(8),T1.time,112) = CONVERT(char(8),T3.time,112
)
John
"Pradeep" wrote:

> I have 3 tables. Table1(time,readingA) Table2(time,readingB)
> table3(time,readingC)
> Now the time can be same and it can be different. Now i want to know
> how do i join so that i get the data: time,readingA,readingB,readingC
> If the time is same then it is fine, but if the time is not same in
> two tables for eg : if table1 has a record for time 12:30 and Table2
> and table3 does not have that time then it should show data from
> table1 and the readingB and readingC will be blank.
>
> I hope my question is clear.
> Thanks for help
>|||Hi,
if I understand correctly your question, you need to use left join.
Something like
SELECT T1.time AS Time, T1.readingA, T2.readingB,
T3.readingC
FROM Table1 T1
LEFT JOIN Table2 T2
ON T2.time=T2.time
LEFT JOIN Table3 T3
ON T1.time= T3.time
"Pradeep" <agarwalp@.eeism.com> wrote in message
news:364c5b9b.0502012334.5b8f2955@.posting.google.com...
>I have 3 tables. Table1(time,readingA) Table2(time,readingB)
> table3(time,readingC)
> Now the time can be same and it can be different. Now i want to know
> how do i join so that i get the data: time,readingA,readingB,readingC
> If the time is same then it is fine, but if the time is not same in
> two tables for eg : if table1 has a record for time 12:30 and Table2
> and table3 does not have that time then it should show data from
> table1 and the readingB and readingC will be blank.
>
> I hope my question is clear.
> Thanks for help|||It looks like I may have got this mixed up! As Ana says use left JOIN
although you may not want your times to 3/100 of a second, in which
case you will still need to truncate them
SELECT T1.Time, T1.readingA, T2=AD.readingB,
T3.readingC
FROM Table1 T1
LEFT JOIN Table2 T2 ON T1.time =3D T2.time
LEFT JOIN Table3 T2 ON T1.time =3D T3.time
John

Joining one database table with other database table in stroed procedures.

Hi All,
I need to perform a join on table1 of database1 with table2 or database2, in a stored procedure and return to my web application.. For this I'm providing execute permission on stored procedure, in database1 and "SELECT" permission on database2 table 2, to my database webuser.
Is there any way ( Another stored procedure in database2), i can get join on two tables without SELECT permission right on table2 ( or table1).
Purely using stored procedures. If so how?Normally you only need to grant execute permission on a stored procedure not select permission on the underlying tables.

Past that I would setup a sp to on database1 to return the data and make sure the webuser has an a security path from database1 to database2. How do yo uhave security setup for this user on databse1 and database2?|||Hi Paul,
Thanks, What is meant by security path? I didnot get ur point.
User have only public permission on both databases. Is it not enough? I do have execute permission on sp1 (Stored Procedure) in database1.
but in sp1 I have a select statement which is a join on database2 table-'table2'. When I tried to execute sp1 from web application only with execute permission on sp1-
I got an error as Webuser does not have 'SELECT' permission on table2 of database2. So I provided it and sp1 worked fine. But I don't want to use a SELECT permission- What is alternate, for the join.
sp1 has simple
SELECT *
FROM database1.dbo.table1 t1
INNER JOIN database2.dbo.table2 t2
ON (t1.col1 = t2.col1)

Originally posted by Paul Young
Normally you only need to grant execute permission on a stored procedure not select permission on the underlying tables.

Past that I would setup a sp to on database1 to return the data and make sure the webuser has an a security path from database1 to database2. How do yo uhave security setup for this user on databse1 and database2?|||by security path I was refering to how a user gets authenticated on server2 when making a connection from server1.

You might try using OPENQUERY to call a stored procedure on server2 and use the results to join to a table on server1. I haven't had the need for this in the past so I am working on theory here. Check BOL for usage on OPENQUERY, they have some good examples.|||When calling a stored proc from database 1, you need execute privs on the proc. You do not need to give the user privs on the table itself as long as it is in database 1, too. If database 2 is owned by the same user that is the owner of database 1, you do not need explicit permissions on the object in table 2 accessed by the proc. However, if the database owners are different for the two databases, the user calling the proc must have explicit permissions in the second database.

If need be, you can change the database owner by:

EXEC sp_changedbowner 'username'

Execute this in the database you want to change.|||Well Both databases are on same server. Is OPENQUERY solve in this case too?|||Originally posted by soumyag
Well Both databases are on same server.

It doesn't matter what server they are on, the owner of the databases is what matters. You can have multiple databases on the same server with different owners. In query analyzer, run:

sp_helpdb

It will list the owner for each of the databases. If the owner is different, run sp_changedbowner to set them the same.|||Hi,
Both are on same server, but owned by different users. And I don't have any right to change the dbowners But how it will help in writing a join on tables. What is other alternative way to solve this problem.
Thanks.

Originally posted by bglass

It doesn't matter what server they are on, the owner of the databases is what matters. You can have multiple databases on the same server with different owners. In query analyzer, run:

sp_helpdb

It will list the owner for each of the databases. If the owner is different, run sp_changedbowner to set them the same.

Joining and grouping using SQL

I have two tables... Table1 and table2 and I need to reconcile them
with each other.

Table1 has the fields Product number, invoice number, price, vat
amount and total.

Table2 has the same data but in a slightly different format...

It has Product Number, invoice number, Price and type.
Type will say Vat or sale and amount will be the vat amount or sale
amount

What is on one row in Table1, will be spread accross 2 rows in Table2.

It means that Invoice number is not unique in Table2.

How do I either group the data in Table2, so I can join it with Table1
or make Table2 the same format as Table1.

If there is something else you can think of to help me, by all means
suggest away.

Regards,
Ciarn[posted and mailed, please reply in news]

Ciar?n (chudson007@.hotmail.com) writes:
> Table1 has the fields Product number, invoice number, price, vat
> amount and total.
> Table2 has the same data but in a slightly different format...
> It has Product Number, invoice number, Price and type.
> Type will say Vat or sale and amount will be the vat amount or sale
> amount
> What is on one row in Table1, will be spread accross 2 rows in Table2.
> It means that Invoice number is not unique in Table2.
> How do I either group the data in Table2, so I can join it with Table1
> or make Table2 the same format as Table1.

SELECT ...
FROM Table1 t1
JOIN (SELECT ProductNumber, InvoiceNumber, Price = SUM(Price)
FROM Table2
GROUP BY ProductNumber, InvoiceNumber) AS t2
ON t1.ProductNumber = t2.ProductNumber
AND t1.InvoiceNumber = t2.InvoiceNumber

This may not be exactly what you need; your request is a bit vague. If
you want more help, I suggest that you include:

o CREATE TABLE statement for your table.
o INSERT statements with sample data.
o The desired result, given the sample data.

This make it easy to cut and paste and compose a tested solution.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I'd suggest looking at the design of your tables first.

chudson007@.hotmail.com (Ciar?n) wrote in message news:<7f9b6870.0411040804.3e32e925@.posting.google.com>...
> I have two tables... Table1 and table2 and I need to reconcile them
> with each other.
> Table1 has the fields Product number, invoice number, price, vat
> amount and total.
> Table2 has the same data but in a slightly different format...
> It has Product Number, invoice number, Price and type.
> Type will say Vat or sale and amount will be the vat amount or sale
> amount
> What is on one row in Table1, will be spread accross 2 rows in Table2.
> It means that Invoice number is not unique in Table2.
> How do I either group the data in Table2, so I can join it with Table1
> or make Table2 the same format as Table1.
> If there is something else you can think of to help me, by all means
> suggest away.
> Regards,
> Ciarn

join...

I have a left join between table1 and table2:

select table1.*, table2.contact, table2.telephone from table1
LEFT JOIN table2 On table1.CUSTOMER = table2.customer

... problem is table2 may occasionally have more than 1 entry for a customer (where the repcode is different in each record for the customer)... Instead of the join returning the transaction twice, I want it to only show 1 of the contact details (doesnt matter which record)... How could i do this?

Thanks in advancedoesn't matter which one? oh boy, let's pick the one with the lowest telephone number!!select table1.*
, table2.contact
, table2.telephone
from table1
LEFT
JOIN table2 as T2
On T2.customer = table1.CUSTOMER
and T2.telephone =
( select min(telephone)
from table2
where customer = table1.CUSTOMER )

join two tables returning unlike data

How do you return unlike data between two identical tables.
IE : Table1 , column1,column2,colum3,column4
Table2 , column1,column2,colum3,column4
I need the rows that are not in Table2.
Thanks for the Help
pixel696SELECT a.* FROM myTableA a LEFT JOIN myTableB b
WHERE a.myKey = b.myKey WHERE b.myKey IS NULL

is one way...|||brett, i think you put one too many where clauses. the first one should be replaced with on.

Join table and function

Hi .
I want to join a table function and a table.Is it possible?How?

table (Id,Title)
function (table1.Id) : returns (Id,Describ1,Describ2)

Resault should be: (Table1.Id,Title,Describ1,Describ2)

Use the following query..

Select Table.Id, Table.Title, Fun.Id, Fun.Descib1, Fun.Descib2

From Table

Join function(someid) as Fun on Fun.ID = Table.ID

|||

For SQL Server 2005, use the cross apply operator as below.
For SQL Server 2000, there's no easy way (can be done using a cursor)

create table mytable(Id int,Title varchar(10))

go

create function dbo.myfunction(@.Id int)
returns @.retTab table(Id int ,Describ1 varchar(10),Describ2 varchar(10))
as
begin
insert into @.retTab(Id,Describ1,Describ2)
select @.Id,'Describ1','Describ2'
return
end

go

select mytable.Id, mytable.Title, Fn.Describ1, Fn.Describ2
from mytable
cross apply dbo.myfunction(Id) as Fn

Join Small Table to Big Table or Vice Versa, does it matter?

If I join Table1 to Table2 with a WHERE condition, is
it the same if I would join Table2 to Table1 considering
that the size of the tables are different.

Let's assume Table2 is much bigger than Table1.

I've never used MERGE, HASH JOINs etc, do any of
these help in this scenario?

Thank youserge (sergea@.nospam.ehmail.com) writes:
> If I join Table1 to Table2 with a WHERE condition, is
> it the same if I would join Table2 to Table1 considering
> that the size of the tables are different.
> Let's assume Table2 is much bigger than Table1.

For an inner join the order does not matter.

> I've never used MERGE, HASH JOINs etc, do any of
> these help in this scenario?

These are optimizer hints, and you should use them if you can get
good performance in any other way.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> If I join Table1 to Table2 with a WHERE condition, is
> it the same if I would join Table2 to Table1 considering
> that the size of the tables are different.

Yes.

> I've never used MERGE, HASH JOINs etc, do any of
> these help in this scenario?

No.

--
David Portas
SQL Server MVP
--

"serge" <sergea@.nospam.ehmail.com> wrote in message
news:7_%te.82758$Jk6.1151808@.wagner.videotron.net. ..
> If I join Table1 to Table2 with a WHERE condition, is
> it the same if I would join Table2 to Table1 considering
> that the size of the tables are different.
> Let's assume Table2 is much bigger than Table1.
> I've never used MERGE, HASH JOINs etc, do any of
> these help in this scenario?
>
> Thank you|||On Tue, 21 Jun 2005 17:52:02 -0400, serge wrote:

> If I join Table1 to Table2 with a WHERE condition, is
> it the same if I would join Table2 to Table1 considering
> that the size of the tables are different.
> Let's assume Table2 is much bigger than Table1.

It doesn't matter because the query optimizer will go through thousands of
optimization iterations and choose the best plan it found so far in the
time frame it knows it should not spend any further.

> I've never used MERGE, HASH JOINs etc, do any of
> these help in this scenario?

no.. The query optimizer is smart enough to choose the best plan.

Tony
--
http://www.dotnet-hosting.com
Free web hosting with ASP.NET & SQL Server

Join query

I have two tables Table1 and Table2

Table1 columns:
CustomerNum varchar(12)
InvDate smalldatetime

Table2 columns
CustomerNum varchar(12)
MaintDate smalldatetime
Dis float

Table1 Data (sample):
CustomerNum InvDate
995169 2/4/2002
995169 11/4/2002
995169 1/1/2003
995169 3/4/2003
995169 10/4/2003
995169 1/4/2005
995169 5/4/2005
995169 11/15/2005

Table2 Data (sample):
CustomerNum MaintDate Dis
995169 10/3/2001 1.07
995169 10/3/2002 1.1
995169 5/16/2003 1.7
995169 9/30/2003 2.9
995169 1/1/2005 2.8
995169 3/31/2005 2.95
995169 10/31/2005 2.85

I want to display customerNum, InvDate from Table1 along with applicable Dis from Table2. Dis for a customer changes form time to time. We need to pick the right one based on Table1.InvDate and Table2.MaintDate. For example, the applicable Dis for customner 995169 invoice date 2/4/2002 is 1.07
After joining the tables, I want the data to be displayed as under:

CustomerNum InvDate Dis
995169 2/4/2002 1.07
995169 11/4/2002 1.1
995169 1/1/2003 1.1
995169 3/4/2003 1.7
995169 10/4/2003 2.9
995169 1/4/2005 2.8
995169 5/4/2005 2.95
995169 11/15/2005 2.85

Any help in constructing sql qury will be appreciated. Thanks in advance.

do you want the closest invdate from table 1 that is before maintdate for each customer. it is not clear how you are wanting the data joined, please explain further.|||What I want is get the applicable Dis from Table2 for the customer's invoice date. So after joining the table2 to table1, I should display table1.customernum, table1.invdate, table2.Dis.|||

Lots of subquery hell unless you add a todate on to your second table.

You can then join where invdate between table2.fromdate and table2.todate

|||What you want is described in my request http://sqljunkies.com/WebLog/simons/archive/2006/02/06/Upper_join_request.aspx|||can you not use the maintdate as the to date?|||

try:

SELECT sub.CustomerNum, sub.invdate, table2.Dis
FROM table2
INNER JOIN
(
SELECT t1.CustomerNum, MAX(t1.invdate) invdate, t2.maintdate
FROM table2 t2
JOIN table1 t1
ON t1.InvDate < t2.maintdate
AND t1.CustomerNum = t2.CustomerNum
GROUP BY t1.CustomerNum, t2.maintdate
) AS sub
ON table2.CustomerNum = sub.CustomerNum
AND table2.maintdate = sub.maintdate

|||

You can do below in SQL Server 2000 onwards:

select t1.CustomerNum, t1.InvDate

, (select top 1 t2.Dis from table2 as t2

where t2.CustomerNum = t1.CustomerNum and t2.MaintDate <= t1.InvDate

order by t2.MaintDate desc) as Dis

from table1 as t1

Join Problem

Hello, I have 3 tables in my datasource and I am trying to Join them with the
following
{ oj TABLE2 RIGHT OUTER JOIN
TABLE3 TABLE1 ON TABLE2.ACCTNO = TABLE1.ACCTNO }
At least this is what the designer translates it to. I basically want
Table1 as the primary table (all records) and table 2 and table3 are just
little look up tables for parameters.
When I do this I get an ODBC error,
'Token was not valid: Vaid Tokens: on left join inner joins exception.'
At this point if there is no matching key field in table3 it is not showing
me that record.. And I need to see it. HelpI am accessing an AS400 and it looks like an ODBC problem so I will create
the base query and get rid of the SQL joins.
"SLB" wrote:
> Hello, I have 3 tables in my datasource and I am trying to Join them with the
> following
> { oj TABLE2 RIGHT OUTER JOIN
> TABLE3 TABLE1 ON TABLE2.ACCTNO = TABLE1.ACCTNO }
> At least this is what the designer translates it to. I basically want
> Table1 as the primary table (all records) and table 2 and table3 are just
> little look up tables for parameters.
> When I do this I get an ODBC error,
> 'Token was not valid: Vaid Tokens: on left join inner joins exception.'
> At this point if there is no matching key field in table3 it is not showing
> me that record.. And I need to see it. Help
>|||try creating a view and selecting from that view. I do that on SRS somtimes
when .net tries to rewrite my query to often. That way you can do what you
want on the database side and it seems to run faster also.
SLB wrote:
>I am accessing an AS400 and it looks like an ODBC problem so I will create
>the base query and get rid of the SQL joins.
>> Hello, I have 3 tables in my datasource and I am trying to Join them with the
>> following
>[quoted text clipped - 10 lines]
>> At this point if there is no matching key field in table3 it is not showing
>> me that record.. And I need to see it. Help
--
Gene Hunter
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1

Join multiple tables

Hi
I'm new to SQL and I have the following problem.
I've 3 tables: table1 (with columns a1, b1, c1), table2 (a2, b2, c2), table3
(a3, b3, c3). I have to build joined table with columns like this:
col1 (a1 or a2 or a3), col2 (b1 or b2 or b3), col3 (c1), col4(c2), col5(c3).
a1=a2=a3, b1=b2=b3
How to build query for this?
Thanks in advance.Try,
SELECT T1.a1, T1.a2, T1.c1, T2.c2, T3.c3
FROM T1
JOIN T2 ON T1.a1 = T2.a2 AND T1.b1 = T2.b2
JOIN T3 ON T1.a1 = T3.a3 AND T1.b1 = T3.b3
BG, SQL Server MVP
www.SolidQualityLearning.com
"GrzesB" <GrzesB@.discussions.microsoft.com> wrote in message
news:866BC885-A828-4758-AA23-DE5DC84C6DE0@.microsoft.com...
> Hi
> I'm new to SQL and I have the following problem.
> I've 3 tables: table1 (with columns a1, b1, c1), table2 (a2, b2, c2),
> table3
> (a3, b3, c3). I have to build joined table with columns like this:
> col1 (a1 or a2 or a3), col2 (b1 or b2 or b3), col3 (c1), col4(c2),
> col5(c3).
> a1=a2=a3, b1=b2=b3
> How to build query for this?
> Thanks in advance.
>|||It will help us to understand better your request if you post DDL, sample
data and expected result.
Please provide DDL and sample data.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"GrzesB" wrote:

> Hi
> I'm new to SQL and I have the following problem.
> I've 3 tables: table1 (with columns a1, b1, c1), table2 (a2, b2, c2), tabl
e3
> (a3, b3, c3). I have to build joined table with columns like this:
> col1 (a1 or a2 or a3), col2 (b1 or b2 or b3), col3 (c1), col4(c2), col5(c3
).
> a1=a2=a3, b1=b2=b3
> How to build query for this?
> Thanks in advance.
>