November 2, 2007 at 3:37 pm
Hi gang ... Im a new DBA and this is my first time here. I have a bit of a delema.
I want to join a table that consists of transactions. The owner_id is what links the transaction to a particular person or contract. In the transaction table there is a field indicating 'type1' and 'type2'. Type 1 is a debt and 2 is a credit. What i would like to do is find out what the newest type1 is based on owner_id.
The problem is that when I ....
Join transactions
on contact.id = transactions.owner_id
I expect to get 16,000+ results .... I get over 500,000 and I know i only have 16000 clients. How the heck do I join a table and just pull out what I want ??? Thanks for any help.
November 2, 2007 at 3:54 pm
Rough guess based on your description. You'd be better off if you posted the DDL (that's the table definitions).
SELECT TOP1
*
From Table1 t1
INNER JOIN
Table2 t2
On
Table1.id = table2.id
Where type = 'type1'
ORDER BY t1.transactiondate DESC --This and the TOP 1 will get you the "most recent"
November 4, 2007 at 10:12 am
Each client has multiple transactions, I'm assuming, so that's why you get more values.
You could select the types, but use the MAX( date), group by the owner_id, and that should help as well.
November 23, 2007 at 6:37 pm
The relationship between those two tables are many-to-many.
You can use select * from contact join (select max(owner_id) from transactions group by owner_id) b
on contact.id = b.owner_id
You can also check if there are any duplicated records using Group by function
riverswillbeer (11/2/2007)
Hi gang ... Im a new DBA and this is my first time here. I have a bit of a delema.I want to join a table that consists of transactions. The owner_id is what links the transaction to a particular person or contract. In the transaction table there is a field indicating 'type1' and 'type2'. Type 1 is a debt and 2 is a credit. What i would like to do is find out what the newest type1 is based on owner_id.
The problem is that when I ....
Join transactions
on contact.id = transactions.owner_id
I expect to get 16,000+ results .... I get over 500,000 and I know i only have 16000 clients. How the heck do I join a table and just pull out what I want ??? Thanks for any help.
November 24, 2007 at 12:34 am
Hi !,
hope this code will help u.
create table join1
(
IDINTIDENTITY(1,1) PRIMARY KEY CLUSTERED,
Mydatesmalldatetime default(getdate())
)
GO
create table join2
(
IDINTIDENTITY(1, 1) PRIMARY KEY CLUSTERED,
join1IDINT,
Productvarchar(100)
)
Go
Alter table join2
Add constraint fk_join1_t1 foreign key (join1ID)
references join1 (ID)
insertjoin1(Mydate)
SelectGETDATE() UNION
SelectGETDATE() UNION
SelectGETDATE() + 1 UNION
SelectGETDATE() + 2
INSERTjoin2(join1ID, Product)
SELECT1, 'A' UNION
SELECT1, 'B' UNION
SELECT1, 'C-MaxVal' UNION
SELECT2, 'A' UNION
SELECT2, 'B-MaxVal' UNION
SELECT3, 'D' UNION
SELECT3, 'R' UNION
SELECT3, 'T-MaxVal'
SELECTj1.ID, j2.*
FROMjoin1 J1
INNER JOIN
(
SELECT ROW_NUMBER() OVER (PARTITION BY join1ID ORDER BY ID DESC) ASRID, *
FROM join2
) J2 ON j1.ID = j2.join1ID AND j2.ASRID = 1
Regards,
Abhijit
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply