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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy