Joins on same table

I'm having two general problems trying to do a JOIN. I have a table with
three fields {Code, Date, Amount}. Code+Date is a unique key. I'm trying
to get a rowset with 1) one row for each unique Code+Date pair, 2) and
with each row containing, {Code, Amount for Date-A and Amount for
Date-B}. Basically, I want to create two temp tables with the Amounts for
a specified Date and then Join them.

The problems are
1) I'm trying to do this in SQL-Server 7 with a single stantment, and
2) If a Code+Date pair doesn't have any Amounts, I'd still like a row
returned with NULLs.

Anybody have any wisdom on this??
ThanksThe following gets me what I want, using Temp tables. I'm just trying to
figure out how to combine the Selects into a single statment.

Thanks

----------------

--temp with each Code
Drop Table #T0;
Select Code
Into #T0
From tblSearch
Order by Code;

--temp with amounts for 1st date
Drop Table #T1;
Select Code, Date, Amount
Into #T1
From tblSearch
Where Date = 20031102
Order by Code, Date;

-- amounts for 2nd date
Drop Table #T2;
Select Code, Date, Amount
Into #T2
From tblSearch
Where Date = 20031103
Order by Code, Date;

--put everything together
Select Distinct #T0.Code, #T1.Date, #T1.Amount 'd1', #T2.Date, #T2.Amount
'd2' from #T0
Left Outer Join #T1
On #T0.Code = #T1.Code
Left Outer Join #T2
On #T0.Code = #T2.Code
Order By #T0.Code|||[posted and mailed, please reply in news]

Chris (chris@.hicom.net) writes:
> I'm having two general problems trying to do a JOIN. I have a table
> with three fields {Code, Date, Amount}. Code+Date is a unique key.
> I'm trying to get a rowset with 1) one row for each unique Code+Date
> pair, 2) and with each row containing, {Code, Amount for Date-A and
> Amount for Date-B}. Basically, I want to create two temp tables with
> the Amounts for a specified Date and then Join them.
> The problems are
> 1) I'm trying to do this in SQL-Server 7 with a single stantment, and
> 2) If a Code+Date pair doesn't have any Amounts, I'd still like a row
> returned with NULLs.

Just rewriting the temp-table thing you had with derived tables
gives:

SELECT DISTINCT #T0.Code, #T1.Date, #T1.Amount 'd1',
#T2.Date, #T2.Amount 'd2'
FROM tblSearch #T0
LEFT JOIN (SELECT Code, Date, Amount
FROM tblSearch
WHERE Date = '20031102') AS #T1
ON #T0.Code = #T1.Code
LEFT JOIN (SELECT Code, Date, Amount
FROM tblSearch
WHERE Date = '20031103') AS #T2
ON #T0.Code = #T2.Code
ORDER BY #T0.Code

But if I understand this correctly, it seems that you could get away with:

SELECT Code = coalesce(a.Code, b.Code), a.Date, d1 = a.Amount,
b.Date, d2 = b.Amount
FROM tblSearch a
FULL JOIN tblSearch b ON a.Code = b.Code
AND a.Date = b.Date
AND a.Date = '20031102'
AND b.Date = '20031103'

All this works on SQL7.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

You can replace temp tables with derived tables. Alternatively, as
the temp tables are selecting from the same table tblSearch, you can
also re-write the query using CASE. Note I'm using a mssqlserver
non-standard syntax. I just find it easier to read.

"Date=CASE when Date = 20031102 then Date else null end"
instead of
"CASE when Date = 20031102 then Date else null end as Date"

SELECT
Distinct
Code,
Date=CASE when Date = 20031102 then Date else null end,
Amount=CASE when Date = 20031102 then Amount else null end,
Date=CASE when Date = 20031103 then Date else null end,
Amount=CASE when Date = 20031103 then Amount else null end,
FROM tblSearch
ORDER BY code

> --temp with each Code
> Drop Table #T0;
> Select Code
> Into #T0
> From tblSearch
> Order by Code;
> --temp with amounts for 1st date
> Drop Table #T1;
> Select Code, Date, Amount
> Into #T1
> From tblSearch
> Where Date = 20031102
> Order by Code, Date;
> -- amounts for 2nd date
> Drop Table #T2;
> Select Code, Date, Amount
> Into #T2
> From tblSearch
> Where Date = 20031103
> Order by Code, Date;
> --put everything together
> Select Distinct #T0.Code, #T1.Date, #T1.Amount 'd1', #T2.Date, #T2.Amount
> 'd2' from #T0
> Left Outer Join #T1
> On #T0.Code = #T1.Code
> Left Outer Join #T2
> On #T0.Code = #T2.Code
> Order By #T0.Code|||The derived table approach gets me what I want -- one row per Code.

It seems that Coalesce doesn't help reduce the normal number of rows from
the Join.

Thanks very much for looking for a solution.|||The derived tables gets both Amounts into the same row, while the Case
still results in two (Distinct) rows.

I need to get a better SQL reference -- the book I'm using does not cover
derived tables.

Thanks very much.|||"Chris" <chris@.hicom.net> wrote in message news:<2bcNb.32425$G04.6661104@.news4.srv.hcvlny.cv.net>...
> The derived tables gets both Amounts into the same row, while the Case
> still results in two (Distinct) rows.
> I need to get a better SQL reference -- the book I'm using does not cover
> derived tables.
> Thanks very much.

Use GROUP if you want combine them into the same row. DISTINCT only
filters the rows.

SELECT
Code,
Date=max(CASE when Date = 20031102 then Date else null end),
Amount=max(CASE when Date = 20031102 then Amount else null end),
Date=max(CASE when Date = 20031103 then Date else null end),
Amount=max(CASE when Date = 20031103 then Amount else null end)
FROM tblSearch
GROUP BY code
ORDER BY code|||Chris (chris@.hicom.net) writes:
> It seems that Coalesce doesn't help reduce the normal number of rows
> from the Join.

That's right. The coalesce() function takes a list of values as parameters,
and return the first value in the list that is not NULL. Since the second
query included a full join, any of a.code and b.code could be NULL, so be
sure that we had a value here, I used coalesce(a.Code, b.Code).

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

-- Thanks|||louis nguyen (louisducnguyen@.hotmail.com) writes:
> SELECT
> Code,
> Date=max(CASE when Date = 20031102 then Date else null end),
> Amount=max(CASE when Date = 20031102 then Amount else null end),
> Date=max(CASE when Date = 20031103 then Date else null end),
> Amount=max(CASE when Date = 20031103 then Amount else null end)
> FROM tblSearch
> GROUP BY code
> ORDER BY code

Note that date literals requires quotes. 20031103 is a number, and
attempt to convert it to datetime results in overflow.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

JOINS and Integers vs. Indexed "Strings".

I was told that, when possible, use integer fields for the equality comparison in INNER JOINS. Today someone suggested that using character fields that are indexed should be just as efficient.What do you think?

TIA,

barkingdog

It is the size of the index key which is most important. The larger your index key is, the more pages should be processed (and probably read from the hard disk: this is the slowest operation) while executing query. And, of course, there are some overhead comparing to strings in terms of collation. So, integer field is more effective in most cases.

Joins

I have two tables, one called users and the other resources.
I'd like to select several fields from users and one from Resource (called ResourceID) based on certain criteria.

My sql statement looks like this:

gstrSQL = "Select UserNum, ResourceID, UserFName, UserLName, Password, SecurityLevel, email, Telephone, ext, UserID from Users U, Resource R where "

If UserNum <> "" Then
gstrSQL = gstrSQL & "UserID='" & UserNum & "' And u.userfname = r.fname and u.userlname = r.lname"
Else
gstrSQL = gstrSQL & "UserID='" & UserID & "' And u.userfname = r.fname and u.userlname = r.lname"
End If

Before adding "ResourceID" to the selection criteria, this statement was working just fine. It's probably a silly mistake but I can't figure it out.
Thanks.You didn't let us know the DB you're working on, nor the error itself ...

In Oracle you can NOT name your table "Resource" as it is reserved word.

However, if your DB allows it, try to rewrite the query in a way to put table aliases in front of table columns, i.e.

SELECT u.UserNum, r.ResourceID, u.UserFName, ...
FROM Users U, Resource R
WHERE ...

Perhaps your tables contain the same column names and, if you don't explicitly say the table you are selecting its value from, the column becomes ambigously defined.

joining two queries ?

How can I return the results of two different queries ?
So that the recordset returned has all the fields from query A, plus the
results from query B ?
Both return the same columns.
ALookup UNION in Books Online.
David Portas
SQL Server MVP
--

Joining two fields to single field

If I have a database table with the following columns:
ID
Other_ID
Description

And I want to join the two ID fields to one field in another table that contains the following fields:
ID
Name

How would i do that?

Here is some sample data and what I would like returned

TABLE1

ID Other_ID Description
row 1 1 2 Number1
row 2 3 1 Number2

TABLE2

ID Name
row 1 1 John
row 2 2 Bob
row 3 3 Bill

I want to query TABLE1, row 1 so that I pull back the Names for the values stored in the ID and Other_ID fields so that my results are like:
John Bob Number1

The only way around it now is that I store Other_Name in Table1.

Thanks.

try this

select (select name from Table2 t2 where t1.id=t2.id) ,
(select name from Table2 t2 where t1.Other_id=t2.id) ,Description
from Table1 t1|||

I'll take a stab at it:

SELECT T2.Name as Name1, T3.Name as Name2, Description FROM TABLE1 INNER JOIN TABLE2 AS T2 ON TABLE1.ID=T2.ID INNER JOIN TABLE2 AS T3 ON TABLE1.Other_ID=T3.ID

|||Thanks. This worked great.sql

Joining two fields in a query

I am trying to join two fields in a query in SQL 2000. For example.

Update myTable SET field_1 = @.field_1_value , field_2 = @.field_2_value, field_3 = @.field_1_value + ' x ' + field_2_value

Is this even possible.

I want the user to input values for fields 1 and 2, then in the background combine the two and insert that value in field 3.

Thanks in advance,

Scotty_C

the ' x ' should also be inserted between the values.

|||that'll work! there's just a typo for the @.field_2_value

Update myTable SET field_1 = @.field_1_value , field_2 = @.field_2_value, field_3 = @.field_1_value + ' x ' + @.field_2_value|||

I agree that there was a typo, however, the given SQL Statement was just fabricated for the forum as an example.

Thank you for you input.

When I attempt to execute the Statement I get the Error Message:

"Syntax Error Converting the varChar value ' x ' to a column of datatype int."

The actual SQL Statement being used is this:

UPDATE SheetSizes
SET Width = @.Width, Length = @.Length, Standard = @.Standard, Label = @.Width + ' x ' + @.Length
WHERE (SheetSizeID = @.SheetSizeID)

The datatype for the column "Length" is varChar(50)

|||excuse me, it has been a long day, the datatype for the column "Label" is varChar(50) and the dataype for the columns "Length" and "Width" is int.|||

Scotty_C wrote:

I agree that there was a typo, however, the given SQL Statement was just fabricated for the forum as an example.

Thank you for you input.

When I attempt to execute the Statement I get the Error Message:

"Syntax Error Converting the varChar value ' x ' to a column of datatype int."

The actual SQL Statement being used is this:

UPDATE SheetSizes
SET Width = @.Width, Length = @.Length, Standard = @.Standard, Label = @.Width + ' x ' + @.Length
WHERE (SheetSizeID = @.SheetSizeID)

The datatype for the column "Length" is varChar(50)

you have to use CAST or CONVERT before concatenating your values...
UPDATE SheetSizes
SET Width = @.Width, Length = @.Length, Standard = @.Standard, Label = CAST(@.Width AS varchar(10)) + ' x ' + CAST(@.Length AS varchar(10))
WHERE (SheetSizeID = @.SheetSizeID)

HTH,|||

Yes CryptoKnight,

That works very well, thank you!

Thanks,

Scotty_C

Joining to large tables to perfrom update

I have 2 large tables that are over 11 million records each. I need to join
them on 1 field and then update 4 fields. So my script is this
update a
set a.field1= b.field1,
a.field2= b.field2,
a.field3 = b.field3,
a.field4 = bfield4
from a inner join b
on a.field5= b.field5
This query is taking a long time to run and I am wondering if there are any
join hints or lock hints that I can put in there to make it more efficient.
Any help is appreciated.an index on b(field5, field1, field2, field3, field4) might help with
this particular update.
Considering the performance of the whole system, it might or might not
be worth keeping, depending on your priorities.|||You can use
update a
set a.field1= b.field1,
a.field2= b.field2,
a.field3 = b.field3,
a.field4 = bfield4
from a inner join b with (nolock)
on a.field5= b.field5
however, for 11 million rows, it will still take a lot of time.
I would create script that executes the update in batches (Example: 1
million per batch based on field5). In other words, I would create a
"control" table where I can store the field5, the bacth number and when was
updated. This way even if any of the batch updates do not complete (for any
reason), you can start where you left off rather than start all over again.
"Andy" wrote:

> I have 2 large tables that are over 11 million records each. I need to jo
in
> them on 1 field and then update 4 fields. So my script is this
> update a
> set a.field1= b.field1,
> a.field2= b.field2,
> a.field3 = b.field3,
> a.field4 = bfield4
> from a inner join b
> on a.field5= b.field5
> This query is taking a long time to run and I am wondering if there are an
y
> join hints or lock hints that I can put in there to make it more efficient
.
> Any help is appreciated.

Joining Tables

Hi,
I have a table with fields as partnerid, contractno.
The partnerid field has the Id number which can be a supplier or a customer.
I need to get the partner id(supplier) and the partner id (customers) of that particular supplier only. I tried with self join but the data is data is replicating.

Data in table
PId ContractNo
20045 1567
435 1567
123 1567
345 1678
1004 1678

I need to display the data in the following format.

PId(Supplier) PId(Customer)
20045 1567
20045 435
20045 123
345 1678
345 1004

But I'm getting the data replicated with all records joined every record.
Give the suggestion.Your data doesn't make sense in any way that would give you the query you want? How do you know which PId is a supplier or Customer? Without some kind of a key to indicate that, there's nothing you can do about your issue.|||Hai Madhavi,
Can you show us the query that you have written?

Madhivanan

Joining Tables

help please
I have two tables I want to join, Table 1 (T1) nd Table 2 (t2). Table 1 has
several fields A1,B1,C1....... etc and Table 2 has several Fields A2,B2,B3
etc
I want to join A1 to A2, which is simple enough, but then I want to join B1
to B2 based on the field value in B1 being in the field B2
eg Find 'Dog' (B1) in the 'Dog and the Cat' (B2) or find 'Hat' (B1) in 'Top
Hat' (B2)
Can any one advise how I can achieve this ?
Thanks JohnJohn
SELECT <> FROM Table1 JOIN Table2
ON Table1.A1=Table2.A2 AND Table1.B1=Table2.B2
Does it help you?
"John" <topguy75@.hotmail.com> wrote in message
news:43d09d82$0$23296$db0fefd9@.news.zen.co.uk...
> help please
> I have two tables I want to join, Table 1 (T1) nd Table 2 (t2). Table 1
> has several fields A1,B1,C1....... etc and Table 2 has several Fields
> A2,B2,B3 etc
> I want to join A1 to A2, which is simple enough, but then I want to join
> B1 to B2 based on the field value in B1 being in the field B2
> eg Find 'Dog' (B1) in the 'Dog and the Cat' (B2) or find 'Hat' (B1) in
> 'Top Hat' (B2)
> Can any one advise how I can achieve this ?
> Thanks John
>|||I think he wants to check for approximality not equality. B1 attribute
has to be appear "somewhere" in the B2 attribute. My only thought for
this as a setbased solution is to use CONTAINS.
-Jens Suessmeyer.|||You are probably right
SELECT <> FROM Table1 JOIN Table2
ON Table1.A1 LIKE '%' + Table2.A2 + '%'
AND Table1.B1 LIKE '%' + Table2.B2 + '%'
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1137747168.036246.222290@.o13g2000cwo.googlegroups.com...
>I think he wants to check for approximality not equality. B1 attribute
> has to be appear "somewhere" in the B2 attribute. My only thought for
> this as a setbased solution is to use CONTAINS.
> -Jens Suessmeyer.
>|||Assuming that the original poster wants equality on the A columns, this
should be
SELECT <> FROM Table1 JOIN Table2
ON Table1.A1 = Table2.A2
AND Table1.B1 LIKE '%' + Table2.B2 + '%'
-Jens Suessmeyer|||Thanks for you help both of you, worked a treat
John
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1137748855.153688.236890@.g49g2000cwa.googlegroups.com...
> Assuming that the original poster wants equality on the A columns, this
> should be
> SELECT <> FROM Table1 JOIN Table2
> ON Table1.A1 = Table2.A2
> AND Table1.B1 LIKE '%' + Table2.B2 + '%'
> -Jens Suessmeyer
>sql

Joining multiple tables in a view.

I have three tables

1^st table is Student

StudnetID (pk)

Other fields…

2^nd table is PhoneType

PhoneTypeID (pk)

PhoneType

3^rd table is StudentHasPhone

SHPID (pk)

StudnetID (fk)

PhoneTypeID (fk)

PhoneNumber

PhoneType is an auxiliary table that has 5 records in it Home phone, Cell phone, Work phone, Pager, and Fax. Is there a way to do a join or maybe make a view of a view that would allow me to ultimately end up with…

StudnetID: 1

Name: John

HomePhone: 123-456-7890

WorkPhone: 123-456-7890

CellPhone:

Pager: 123-456-7890

Fax:

Memo: This is one student record.

Some students will have no phone number, some will have all 5 most will have one or two. If possible I would like to do a setup like this in my database to keep from having to have null fields for 4 phone numbers that the majority of records won't have.

Thanks in advanced,

Nathan Rover

What you need is a View with UNION ALL but your tables must be UNION compatible which means same data type facing the same direction. Try the link below for sample code. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_30hj.asp

|||

You can join to the phone table multiple times, as follows:

SELECT StudentID, HP.PhoneNumber, WP.PhoneNumber, CP.PhoneNumber, Pg.PhoneNumber, Fx.PhoneNumber
FROM Student S
LEFT OUTER JOIN StudentHasPhone HP
ON S.StudentID = HP.StudentID
AND HP.PhoneTypeID = 1 --Home Phone
LEFT OUTER JOIN StudentHasPhone WP
ON S.StudentID = WP.StudentID
AND WP.PhoneTypeID = 2 --Work Phone
LEFT OUTER JOIN StudentHasPhone CP
ON S.StudentID = CP.StudentID
AND CP.PhoneTypeID = 3 --Cell Phone
LEFT OUTER JOIN StudentHasPhone Pg
ON S.StudentID = Pg.StudentID
AND Pg.PhoneTypeID = 4 --Pager
LEFT OUTER JOIN StudentHasPhone Fx
ON S.StudentID = Fx.StudentID
AND Fx.PhoneTypeID = 5 --Fax

BTW: StudentHasPhone is not a good table name. StudentPhone, or simply Phone, would be much better.

|||

Thanks, that was exactly what I was looking for… It worked perfect.

--Nathan

joining multiple tables

I have a quick question regarding the joining of multiple tables.

I have one main table that contains a TranID and some other fields. Then, I have about five tables that have detail information for each of the records in the main table. There is a one to one relationship between a detail record and the main record. The reason they are split between several detail tables is because the detail information is different based on TypeID.

My question is this: How can I join more than one of the detail tables?

This returns nothing....can someone explain why and hopefully provide a solution?

select * from MainTable m
Inner Join DetailTable1 d1 on m.TranID = d1.TranID
Inner Join DetailTable2 d2 on m.TranID = d2.TranID
Inner Join DetailTable3 d3 on m.TranID = d3.TranID
Inner Join DetailTable4 d4 on m.TranID = d4.TranID
Inner Join DetailTable5 d5 on m.TranID = d5.TranID

Thanks very much...If it returns nothing then one or more of the inner join conditions conditions are not met. That would be something like no d1TranID matching any m.TranID. Because they are all inner joins it only takes one bad join condition to break the whole thing.

To diagnose the problem you might try using Left Outer Joins and Right Outer Joins to see where matches are not being made between MainTable and each of the DetailTables. Something like:

Select m.TrandID,d1,TranID,d2,TranID,d3,TranID,d4,TranID,d5,TranID
from MainTable m
Left Outer Join DetailTable1 d1 on m.TranID = d1.TranID
Left Outer Join DetailTable2 d2 on m.TranID = d2.TranID
Left Outer Join DetailTable3 d3 on m.TranID = d3.TranID
Left Outer Join DetailTable4 d4 on m.TranID = d4.TranID
Left Outer Join DetailTable5 d5 on m.TranID = d5.TranID

and then to see where the detail table(s) have TranIDs that the MainTable doesn't have:

Select m.TrandID,d1,TranID,d2,TranID,d3,TranID,d4,TranID,d5,TranID
from MainTable m
Right Outer Join DetailTable1 d1 on m.TranID = d1.TranID
Right Outer Join DetailTable2 d2 on m.TranID = d2.TranID
Right Outer Join DetailTable3 d3 on m.TranID = d3.TranID
Right Outer Join DetailTable4 d4 on m.TranID = d4.TranID
Right Outer Join DetailTable5 d5 on m.TranID = d5.TranID

This should indicate what the problem(s) are.

Joining fields

Hello,
I Hope one of you can help me with the following:
I try to join three fields from a table into one output field: month,
day, year ==> date.
And I can't get it tow work right. The datatype of the fields is
numeric. Since this query is nested into another one the datatype of
the outputfield should be DATETIME.
I already tried google but it could not help me,
Thanks in advance
Jean-Paul Rijnsburger (Netherlands)Try:
declare
@.year numeric
, @.month numeric
, @.day numeric
select
@.year = 2006
, @.month = 1
, @.day = 2
select
convert (datetime, str (@.year) +'/' + str (@.month) + '/' + str (@.day))
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Jean-Paul Rijnsburger" <Jeepee75@.gmail.com> wrote in message
news:1136207889.492149.133070@.o13g2000cwo.googlegroups.com...
Hello,
I Hope one of you can help me with the following:
I try to join three fields from a table into one output field: month,
day, year ==> date.
And I can't get it tow work right. The datatype of the fields is
numeric. Since this query is nested into another one the datatype of
the outputfield should be DATETIME.
I already tried google but it could not help me,
Thanks in advance
Jean-Paul Rijnsburger (Netherlands)|||It works,
Thanks Tom|||Tom Moreau (tom@.dont.spam.me.cips.ca) writes:
> Try:
> declare
> @.year numeric
> , @.month numeric
> , @.day numeric
> select
> @.year = 2006
> , @.month = 1
> , @.day = 2
> select
> convert (datetime, str (@.year) +'/' + str (@.month) + '/' + str (@.day))
This may produce different result depending on language and dateformat
settings. Add a third parameter to control the interpreration:
select
convert (datetime, str (@.year) +'/' + str (@.month) + '/' + str (@.day),
111)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Joining different datasets with parameters

Hi all,

lets say i have one table with colour ids and colour names, and another table with broad-leafed trees and different colour id fields for root, leafs and trunk:

Table colours:
integer field colour_id, varchar field colour_name
Table trees:
varchar field tree_type, integer field leaf_colour_id,
integer field trunk_colour_id, integer field root_colour_id

one way to get the fields would be:

select T.tree_type, C1.colour_name as leaf_colour_name,
C2.colour_name as trunk_colour_name, C3.colour_name as root_colour_name
from trees as T
inner join colours as C1 on C1.colour_id = leaf_colour_id
inner join colours as C2 on C2.colour_id = trunk_colour_id
inner join colours as C3 on C3.colour_id = root_colour_id;

But i would like to do that with some kind of inline function like:
colourName := select colour_name from colours where colour_id = @.colourId;
and then:
select tree_type, colourName(leaf_colour_id), colourName(trunk_colour_id), colourName(root_colour_id) from trees;

Is that possible? Any comments welcome.

Thanks,
haraldIn Sql Server, you can create a User Defined Function (Scalar) which will produce this. It will make code more readable but will slow it down a bit.
See the templates in SQL Server, and this article.
It would be something like:
CREATE FUNCTION fnGetColourName(@.ColourID as int)
RETURNS int
as
BEGIN
DECLARE @.Value as int
SET @.Value = (SELECT MAX(Colour_name)
FROM Colours
WHERE colour_id = @.ColourID
END
RETURN @.Value
SELECT dbo.fnGetColourName(leaf_colour_id) as leafColour
FROM trees
Alternatively, you can use the Custom Code section of the report to create a function that will do the same thing, see examples from Bryant Likes's blog here.|||

Hi wavemash,

thanks for your reply, now my sql statement is more readable

Thanks,
harald

Joining date and time

Hi all,
I need some help with joining two fields of type datetime, one with date
relevancy and the other with time.
If i join the integer part of date field with the fraction part of time
field, the joined datetime is not the same.
What's the trick here?
TIA, JozzaOne way... taking date from @.a, time from @.b
declare @.a datetime, @.b datetime
set @.a = getdate()-1
set @.b = dateadd(hh,5,getdate())
select @.a, @.b,dateadd(ms,datediff(ms,convert(varcha
r(10),@.b,101),@.b),
convert(varchar(10),@.a,101))
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Can you show us an example? What do you mean by "fraction part of time?"
Keith Kratochvil
"Jozza" <hmm@.hmm.com> wrote in message
news:lrcjg.3576$oj5.1220262@.news.siol.net...
> Hi all,
> I need some help with joining two fields of type datetime, one with date
> relevancy and the other with time.
> If i join the integer part of date field with the fraction part of time
> field, the joined datetime is not the same.
> What's the trick here?
> TIA, Jozza
>|||I thought that datetime is stored the way that integer part of a float
represents the date and the fraction part represents the time.
So adding them together would join them. But it doesn't seem to be the case
on SLQ server.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OvXKJ0hjGHA.1508@.TK2MSFTNGP04.phx.gbl...
> Can you show us an example? What do you mean by "fraction part of time?"
> --
> Keith Kratochvil
>
> "Jozza" <hmm@.hmm.com> wrote in message
> news:lrcjg.3576$oj5.1220262@.news.siol.net...
>|||Converting fields to varchar, concatenate strings and convert it back to
datetime does the trick. (which was not exactly what your exemple was, but i
got the idea)
Is there any other way where i could add fields together in mathematical
terms, because i suspect there could be and error in string conversions when
different locale formats are used. Or am i wrong?
Thanks, Jozza
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:22B16AB3-DF86-4878-B181-727F79448589@.microsoft.com...
> One way... taking date from @.a, time from @.b
> declare @.a datetime, @.b datetime
> set @.a = getdate()-1
> set @.b = dateadd(hh,5,getdate())
> select @.a, @.b,dateadd(ms,datediff(ms,convert(varcha
r(10),@.b,101),@.b),
> convert(varchar(10),@.a,101))
>
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>|||Well concatenating the strings might lead to wrong date format if the string
format changes. Thats why I didn't go for the concatenation.
And the example I gave was in mathematical terms :)
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||After looking at the example a little bit longer i realize that you are
absolutely correct.
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:33203119-C451-4C10-9733-F8D9EA1B0229@.microsoft.com...
> Well concatenating the strings might lead to wrong date format if the
> string
> format changes. Thats why I didn't go for the concatenation.
> And the example I gave was in mathematical terms :)
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
>

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

Joining an aggregate of a table to itself

I am trying to figure out how to do a simple aggregate of a table and then join it to itself to get the remaining fields. I have a few tables that list information on daily entries that includes job_id, location_id, desc, date, beginDate, endDate, manager. I want to list every entry in the table, but for each job_id and location_id combination, I'd like to get the very first entry for beginDate and the very latest entry for endDate for the group and then list all the remaining information within each record.

My first attempt to do this is:

Select M.job_id, M.location_id, M.desc, M.date, Y.beginDate, Y.endDate, M.manager
from myTable M
inner join

(select job_id, location_id, min(beginDate), max(endDate)

from myTable

group by job_id, location_id) as Y

on M.job_id = Y.job_id, M.location_id = Y.location_id

This seems inefficient and I'm not sure it's the best way to get what I want. Any suggestions?

Thanks!!!

I'm not completely sure what to aim at; it will help if you will give some sample data and the desired output. You might be looking for something like one of these:

declare @.myTable table
( rid integer,
job_id integer,
location_id integer,
[desc] varchar(10),
date datetime,
manager varchar(10)
)

insert into @.myTable
select 1, 1, 1, 'First Job', '4/20/7', 'Flintstone' union all
select 2, 1, 1, 'First Job', '4/21/7', 'Slate' union all
select 3, 1, 1, 'First Job', '4/22/7', 'Flintstone' union all
select 4, 2, 2, 'Future Job', '4/20/7', 'Jetson' union all
select 5, 2, 2, 'Future Job', '4/21/7', 'Spacely' union all
select 6, 2, 2, 'Future Job', '4/22/7', 'Spacely'

Select M.job_id,
M.location_id,
M.[desc],
M.date,
Y.beginDate,
Y.endDate,
M.manager
from @.myTable M
inner join
( select job_id,
location_id,
min(Date) as beginDate,
max(Date) as endDate
from @.myTable
group by job_id, location_id
) Y
on M.job_id = Y.job_id
and M.location_id = Y.location_id

/*
job_id location_id desc date beginDate endDate manager
- -- - -
1 1 First Job 2007-04-20 00:00:00.000 2007-04-20 00:00:00.000 2007-04-22 00:00:00.000 Flintstone
1 1 First Job 2007-04-21 00:00:00.000 2007-04-20 00:00:00.000 2007-04-22 00:00:00.000 Slate
1 1 First Job 2007-04-22 00:00:00.000 2007-04-20 00:00:00.000 2007-04-22 00:00:00.000 Flintstone
2 2 Future Job 2007-04-20 00:00:00.000 2007-04-20 00:00:00.000 2007-04-22 00:00:00.000 Jetson
2 2 Future Job 2007-04-21 00:00:00.000 2007-04-20 00:00:00.000 2007-04-22 00:00:00.000 Spacely
2 2 Future Job 2007-04-22 00:00:00.000 2007-04-20 00:00:00.000 2007-04-22 00:00:00.000 Spacely
*/

declare @.myTable2 table
( rid integer,
job_id integer,
location_id integer,
[desc] varchar(10),
date datetime,
manager varchar(10)
)

insert into @.myTable2
select 1, 1, 1, 'First Job', '4/20/7', 'Slate' union all
select 2, 1, 1, 'First Job', '4/21/7', 'Slate' union all
select 3, 1, 1, 'First Job', '4/22/7', 'Slate' union all
select 4, 2, 2, 'Future Job', '4/20/7', 'Spacely' union all
select 5, 2, 2, 'Future Job', '4/21/7', 'Spacely' union all
select 6, 2, 2, 'Future Job', '4/22/7', 'Spacely'
select job_id,
location_id,
[desc],
min(date) as beginDate,
max(date) as endDate,
Manager
from @.myTable2
group by job_id,
location_id,
[desc],
Manager
order by job_id,
location_id

/*
job_id location_id desc beginDate endDate manager
- -- - -
1 1 First Job 2007-04-20 00:00:00.000 2007-04-22 00:00:00.000 Slate
2 2 Future Job 2007-04-20 00:00:00.000 2007-04-22 00:00:00.000 Spacely
*/

Joining 2 Foreign keys in a table

I have a table that is named CliCore and has the following fields,
RegNo - Primary Key
CliFName - First Name
CliMM - Middle Initial
CliLName - Last Name
CliDOB - Date of Birth
I have another table that is named CliEvents and has the following fields,
UID - Primary Key
RegNo - Foreign Key from CliCore table
AggressorRegNo - Same as above
EventCatID - Category ID
Comments - Event Comments
How can I get the names of the clients for both RegNo and AggressorRegNo?
Thanks,
Drewyou have to refer 2 times to the CliCore table...something like:
select ... from clievents inner join CliCore A on CliEvents .RegNo =
A.RegNo inner join CliCore B on CliEvents .AggressorRegNo = B.RegNo
Francesco Anti
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:OYAKkK0RFHA.1176@.TK2MSFTNGP12.phx.gbl...
>I have a table that is named CliCore and has the following fields,
> RegNo - Primary Key
> CliFName - First Name
> CliMM - Middle Initial
> CliLName - Last Name
> CliDOB - Date of Birth
> I have another table that is named CliEvents and has the following fields,
> UID - Primary Key
> RegNo - Foreign Key from CliCore table
> AggressorRegNo - Same as above
> EventCatID - Category ID
> Comments - Event Comments
> How can I get the names of the clients for both RegNo and AggressorRegNo?
> Thanks,
> Drew
>|||SELECT E.uid,
E.regno, C1.clilname,
E.aggressorregno, C2.clilname,
E.eventcatid, E.comments
FROM CliEvents AS E
JOIN CliCore AS C1
ON E.regno = C1.regno
JOIN CliCore AS C2
ON E.aggressorregno = C2.aggressorregno
David Portas
SQL Server MVP
--
(untested)|||Try,
select a.CliFName, a.CliLName, b.CliFName, b.CliLName
from CliEvents as e inner join CliCore as a on e.RegNo = a.RegNo
inner join CliCore as b on e.AggressorRegNo = b.RegNo
AMB
"Drew" wrote:

> I have a table that is named CliCore and has the following fields,
> RegNo - Primary Key
> CliFName - First Name
> CliMM - Middle Initial
> CliLName - Last Name
> CliDOB - Date of Birth
> I have another table that is named CliEvents and has the following fields,
> UID - Primary Key
> RegNo - Foreign Key from CliCore table
> AggressorRegNo - Same as above
> EventCatID - Category ID
> Comments - Event Comments
> How can I get the names of the clients for both RegNo and AggressorRegNo?
> Thanks,
> Drew
>
>|||Thank you all for the replies... I was trying it like this and it wasn't
working...
SELECT...
FROM Events E INNER JOIN CliCore CC ON E.RegNo = CC.RegNo OR
E.AggressorRegNo = CC.RegNo...
Thanks a bunch for clearing this up!
Drew
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:OYAKkK0RFHA.1176@.TK2MSFTNGP12.phx.gbl...
>I have a table that is named CliCore and has the following fields,
> RegNo - Primary Key
> CliFName - First Name
> CliMM - Middle Initial
> CliLName - Last Name
> CliDOB - Date of Birth
> I have another table that is named CliEvents and has the following fields,
> UID - Primary Key
> RegNo - Foreign Key from CliCore table
> AggressorRegNo - Same as above
> EventCatID - Category ID
> Comments - Event Comments
> How can I get the names of the clients for both RegNo and AggressorRegNo?
> Thanks,
> Drew
>

Joining 2 fields: Redundancy results..need help

I am working on this access DB that has been created by some consulting firm couple of years ago, which I wasnt here at that time.

The DB is being by college faculty members to store about students who study abroad..
now,
there are students who has more than one major.. and when I run query to find out how many students are studying abroad..
it shows me more than the actual number of students who are studying abroad, because it shows same student twice because of double major..
In other words, it inserts two records for a student who has 2 majors into the DB.

Now my question is, is it possible to combine 2 records into one record on query results?
I know you can do the following: SELECT StudentName = 'major + major'

but the problem is, the name for field major is the same.. so I cannot say in my query 'Major + Major' to combine 2 records. it doesnt work..

let me know if anyone has solution to this, that will be greatful...
thanks,

moradCan you post your table structure? That would help me give you more specific answers.

The short answer is to pick one of the majors as the "most important", and select only that row using a condition in the WHERE clause. If you want access to both rows, use a LEFT JOIN to get access to the second row. If you post a DDL declaration, I can give you more specific help.

-PatP|||I just attached the structure..
well, there is most important major on this database..
and all records are stored in that table...

for example.. here is my query

First Name Last Name Host Country ProgramName Sponsor
Rebecca AINSWO Griffith University Australia N/A Direct
Ronda ALEXAN Universitat at Bonn Germany N/A Western Michigan University
Matt ANDER Rikkyo University Japan N/A Western Michigan University
Matt ANDER Rikkyo University Japan N/A Western Michigan University
Nicholas Applin University of Wollongong Australia N/A Western Michigan University

I get two records of Ander! because Matt Ander has double major, thus he has double records...two records are the same except the major.

let me know what you think...
thanks|||Now I've got the stuff that I needed to get specific! Try using:SELECT *
FROM tblMajor AS a
LEFT JOIN tblMajor AS b
ON (b.SID = a.SID)
WHERE a.MajId = (SELECT Min(c.MajId)
FROM tblMajor AS c
WHERE c.SID = a.SID)
AND (a.MajId < b.MajId OR b.MajId IS NULL);I'm pretty sure that this will give you what you want.

-PatP|||it worked :) but here is the thing though..
it only selected the duplicates, what about the other records that are not duplicate..

the query doesnt show them...
what do I have to add to show the rest... let me know :)
because I tried to use the union and I couldnt t use it union because I need to have same amount of columns for two tables.

thanks.. I appreciate your help|||Crud! The syntax I posted works with real SQL, but not with Jet (the default engine supplied with MS-Access). You could use something like:SELECT a.*
, (SELECT Max(b.MajId)
FROM tblMajor AS b
WHERE b.SID = a.SID
AND a.MajId < b.MajId) AS second_major
FROM tblMajor AS a
WHERE a.MajId = (SELECT Min(c.MajId)
FROM tblMajor AS c
WHERE c.SID = a.SID);This works around an ugly limitation of the Jet database engine.

-PatP|||I tried that today at work, and it worked, but the only thing is that I wanted to change is, instead of showing what record number of the 2nd major for the student, I wanted to show the actual 2nd Major Name.

in other words I want second_major to show the actual name of the major and not the field number of 2nd major..

let me know if you have an idea..

thanks|||Picky, picky, picky... ;)SELECT a.*
, (SELECT Max(b.Major)
FROM tblMajor AS b
WHERE b.SID = a.SID
AND a.MajId < b.MajId) AS second_major
FROM tblMajor AS a
WHERE a.MajId = (SELECT Min(c.MajId)
FROM tblMajor AS c
WHERE c.SID = a.SID);...should fix you right up!

-PatP|||hey pat,
thanks for great help...

Now, what I asked you about was for tblMajor.

what I am trying to do now, is run a query to list students and their majors as well as their minors..

When I tried to run your code for Majors.. it worked, and it added a new field called second major.

but now, when I try to include Minor in my query, it would show the same problem, because student can have more than one minor.

So basically, the objective is to get the following results
SID, Major, 2nd Major, Minor, 2nd Minor

And the minor table is same as major table design as shown above in my previous post.

Now what I want to know, is how to combine these two queries into one

this:
SELECT a.*, (SELECT Max(b.Major)
FROM tblMajor AS b
WHERE b.SID = a.SID
AND a.MajId < b.MajId) AS [Second Major]
FROM tblMajor AS a
WHERE a.MajId = (SELECT Min(c.MajId)
FROM tblMajor AS c
WHERE c.SID = a.SID);

AND

SELECT e.*, (SELECT Max(f.Minor)
FROM tblMinor AS f
WHERE f.SID = e.SID
AND e.MinId < b.MinId) AS [Second Minor]
FROM tblMinor AS e
WHERE e.MinId = (SELECT Min(g.MinId)
FROM tblMinor AS g
WHERE g.SID = e.SID);

Now I was thinking of having using SID as relationship between them, but then I couldnt figure it out.. let me know if you have an idea of how to..

thanks|||The second minor throws an interesting wrinkle into the query, because it now makes a three set intersection instead of just two. You'll need to test this carefully with your data, but I think that you can use:SELECT a.*
, (SELECT Max(b.Major)
FROM tblMajor AS b
WHERE b.SID = a.SID
AND a.MajId < b.MajId) AS second_major
, (SELECT Max(d.Minor)
FROM tblMajor AS d
WHERE d.SID = a.SID
AND d.Minor <> a.Minor) AS second_minor
FROM tblMajor AS a
WHERE a.MajId = (SELECT Min(c.MajId)
FROM tblMajor AS c
WHERE c.SID = a.SID);The gist of this query is that the first row you'd find if the table was sorted by SID then by MajId is assumed to contain the student's "primary" major and minor. The b and d subqueries find the largest Major and Minor that aren't the "primary" values. While this makes perfect sense to me as an outsider, it may or may not make sense in terms of your data, YMMV (your milage may vary). Test this carefully, but logically it should work.

-PatP|||I've tried the code you posted with few tweeks and I was able to get it through

SELECT a.*
, (SELECT Max(b.Major)
FROM tblMajor AS b
WHERE b.SID = a.SID
AND a.MajId < b.MajId) AS second_major
, (SELECT Max(d.Minor)
FROM tblMinor AS d, tblMinor AS e
WHERE d.SID = a.SID
AND d.Minor <> e.Minor) AS second_minor
FROM tblMajor AS a, tblProcessInfo, tblMinor
WHERE a.MajId = (SELECT Min(c.MajId)
FROM tblMajor AS c
WHERE c.SID = a.SID) AND tblProcessInfo.SID = a.SID AND tblProcessInfo.Term = '041';

Now, this query only shows the 2nd minor and not the first minor..
I tried to select tblMinor.Minor in the select statement, and that didnt help much..
let me know what you think..

thanks..|||This is a pure crap-shoot, based on the assumption that the tblMinor structure is exactly like the tblMajor structure. You'll need to test this very carefully before you "bless" this into production!!!SELECT a.*
, (SELECT Max(b.Major)
FROM tblMajor AS e
WHERE e.SID = a.SID
AND a.MajId < e.MajId) AS second_major
, (SELECT Max(d.Minor)
FROM tblMinor AS e
WHERE e.SID = b.SID
AND b.MinId < e.MinId) AS second_minor
FROM tblProcessInfo AS p
JOIN tblMajor AS a
ON (a.SID = p.SID
AND a.MajId = (SELECT Min(c.MajId)
FROM tblMajor AS c
WHERE c.SID = a.SID))
LEFT JOIN tblMinor AS b
on (b.SID = p.SID
AND b.MinId = (SELECT Min(d.MinId)
FROM tblMinor AS d
WHERE d.SID = a.SID))
WHERE tblProcessInfo.Term = '041';If this doesn't work, I'd suggest that you create a "play" copy of your MDB file. Butcher the names and the universities to avoid giving out any usable personal information and post the MDB so I can work with your structures instead of having to guess about everything.

Better yet, see if you can find some enterprising grad student scrabbling for some way to get a few co-op dollars or even just some resum worthy experience! I'm sure that some of them would eat this kind of problem alive, and grovel for the opportunity!

-PatP|||hey pat, thanks for your great help..
I was gone for finals and projects that were due..but everything is back to normal now :)

Now, I need a logical explanation for this problem..

When I run a query of how many students were in a certain country from year 2000 to 2004 I get 490 Students (No duplication records)

And when I run a query of how many students with majors (tblMajor Does have duplication records because of having more than one major) that went to that country from 2000 to 2004.. I get 435

I am missing 50 records when I link tblMajor.SID with tblPermInfo.SID and run a query.

I just dont get it why?!
I thought for myself, that tblPermInfo maybe is giving 490 because there is duplication of records for having more than one major, but then it is not linked to tblMajor..so there is no duplication in what so ever.
but when i run a query where SID of tblMajor and tblPermInfo is matched...it only gives me 435...
so there are SIDs that are left over because there is no match btw tables..right?

What other logical reasons could there be..
let me know what you think..
thanks

kicker

Joing tables using more than one field

I have two tables I need to join but there are 2 fields which they
could be joined on.

Using the example Tablles, TableA and TableB below;

TableA
ID1 ID2 Qty
1 Null 4
2 A 5
Null B 6

TableB
ID1 ID2 Qty
Null A 6
3 B 6
4 Null 7
Null C 8

I want to create TableC which will look like this;
ID1 ID2 TableA.Qty Tableb>Qty
1 Null 4 Null
2 A 5 6
3 B 6 6
4 Null Null 7
Null C Null 8

Any ideas?

Regards,
CiarnTry:

select
*
from
TableA a
join
TableB b on b.ID1 = a.ID1 and b.ID2 = a.ID2

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
<chudson007@.hotmail.com> wrote in message
news:1142947436.438108.100180@.j33g2000cwa.googlegr oups.com...
I have two tables I need to join but there are 2 fields which they
could be joined on.

Using the example Tablles, TableA and TableB below;

TableA
ID1 ID2 Qty
1 Null 4
2 A 5
Null B 6

TableB
ID1 ID2 Qty
Null A 6
3 B 6
4 Null 7
Null C 8

I want to create TableC which will look like this;
ID1 ID2 TableA.Qty Tableb>Qty
1 Null 4 Null
2 A 5 6
3 B 6 6
4 Null Null 7
Null C Null 8

Any ideas?

Regards,
Ciarn|||select coalesce(a.ID1,b.ID1),
coalesce(a.ID2,b.ID2),
a.Qty,
b.Qty
from TableA a
full outer join TableB b on a.ID1=b.ID1 or a.ID2=b.ID2

Join two tables using sum and max

I've got two tables, one called clientsharedeals and the clientorderdeals. In the first table, I have four fields (Rundate, Accno, Dealid, Nominal) that I need to sum(Nominal), grouping by dealid.

Once I've done this, I need to join to clientorderdeals, also having the same fields plus one extra (Rundate, Accno, Dealid, Nominal and Dealseq). Because of Dealseq, I can have more than one row in the table, matching (Rundate, Accno, Dealid, Nominal) of the first table. However, Dealseq increments, so I need to select max(Dealseq).

My query is doubling up on nominal because in my select statement, I am only using one account number, so I know what the value is for nominal and there are two rows in clientorderdeals - and it is not selecting max(dealseq) but both.

Can someone please cast some pearls my way ?

ThanksThis may not be what you want, but if you post your question in the following manner with the expected results, I'm sure you'd get an answer rather quickly

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Rundate datetime, Accno int, Dealid int, Nominal int)
CREATE TABLE myTable00(Rundate datetime, Accno int, Dealid int, Nominal int, Dealseq int)
GO

INSERT INTO myTable99(Rundate, Accno, Dealid, Nominal)
SELECT '1/1/2005',1,1,1 UNION ALL
SELECT '1/1/2005',1,2,1 UNION ALL
SELECT '1/1/2005',1,3,1 UNION ALL
SELECT '1/1/2005',1,4,1

INSERT INTO myTable00(Rundate, Accno, Dealid, Nominal, Dealseq)
SELECT '1/1/2005',1,1,1,1 UNION ALL
SELECT '1/1/2005',1,2,1,1 UNION ALL
SELECT '1/1/2005',1,3,1,1 UNION ALL
SELECT '1/1/2005',1,1,1,2 UNION ALL
SELECT '1/1/2005',1,2,1,2 UNION ALL
SELECT '1/1/2005',1,3,1,2 UNION ALL
SELECT '1/1/2005',1,4,1,1
GO

SELECT *
FROM (
SELECT Dealid, SUM(Nominal) AS SUM_Nominal
FROM myTable99
GROUP BY Dealid) AS xxx
JOIN ( SELECT *
FROM myTable00 a
WHERE DealSeq = (SELECT MAX(Dealseq)
FROM myTable00 b
WHERE a.Dealid = b.Dealid)) AS yyy
ON xxx.Dealid = yyy.Dealid

SET NOCOUNT OFF
DROP TABLE myTable99
DROP TABLE myTable00
GO