January 24, 2011 at 2:20 pm
I have 2 tables. The first table has a SessionID and other columns. There will be multiple SessionID's with the same value in this table.
The second table has a SessionID and a UserID and other columns, with each userID having multiple SessionIDs.
For each UserID in the second table, I need to display all of the data in the first table based on matching the SessionIDs between the 2 tables. However, I need to eliminate duplicates. An example is as follows:
Table A Table B
SessionID UserID SessionID
00010LMA311GUCWLC2ICFFY 123456789 00010LMA311GUCWLC2ICFFY
000CYLEDVIRI0CWLC2ICFGA 123456789 00010LMA311GUCWLC2ICFFY
000D0WKSJHUHMCWLC2FCFFQ 123456789 00010LMA311GUCWLC2ICFFY
000FIKNA5H3FECWLC2FCFFA 123456789 00010LMA311GUCWLC2ICFFY
Table A has 100 rows with the same value Table B has 200 rows with the same value
A join would return 20000 rows for each sessionID. This is because 00010LMA311GUCWLC2ICFFY in Table A would have to join 200 times in Table B, and 00010LMA311GUCWLC2ICFFY in Table B would have to match 100 times in Table A. This is NOT what I am looking for.
I need to see 300 rows; i.e., for each UserID in Table B, the data in table A would be displayed where the SessionID in Table A matches of the SessionID in Table B, but would not display the data in Table B where the SessionID in Table B matches the SessionID in Table A.
I hope I explained this properly.
Any help would be greatly appreciated. [/size][/size][/size]
January 24, 2011 at 2:23 pm
please adjust your size settings and preview the result before posting:crazy:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 25, 2011 at 7:37 am
Sorry about the font size settings...I am a newbie.
Anyway, I have attached the actual table structures and some sample data:
Table A.sql -- Table structure for Table A
Table B.sql -- Table structure for Table B
Table A Data.txt -- Tab delimited sample data from Table A
Table B Data.txt -- Tab delimited sample data from Table B
January 25, 2011 at 8:38 am
gary.morey (1/24/2011)
I have 2 tables. The first table has a SessionID and other columns. There will be multiple SessionID's with the same value in this table.The second table has a SessionID and a UserID and other columns, with each userID having multiple SessionIDs.
For each UserID in the second table, I need to display all of the data in the first table based on matching the SessionIDs between the 2 tables. However, I need to eliminate duplicates. An example is as follows:
Table A Table B
SessionID UserID SessionID
00010LMA311GUCWLC2ICFFY 123456789 00010LMA311GUCWLC2ICFFY
000CYLEDVIRI0CWLC2ICFGA 123456789 00010LMA311GUCWLC2ICFFY
000D0WKSJHUHMCWLC2FCFFQ 123456789 00010LMA311GUCWLC2ICFFY
000FIKNA5H3FECWLC2FCFFA 123456789 00010LMA311GUCWLC2ICFFY
Table A has 100 rows with the same value Table B has 200 rows with the same value
A join would return 20000 rows for each sessionID. This is because 00010LMA311GUCWLC2ICFFY in Table A would have to join 200 times in Table B, and 00010LMA311GUCWLC2ICFFY in Table B would have to match 100 times in Table A. This is NOT what I am looking for.
I need to see 300 rows; i.e., for each UserID in Table B, the data in table A would be displayed where the SessionID in Table A matches of the SessionID in Table B, but would not display the data in Table B where the SessionID in Table B matches the SessionID in Table A.
I hope I explained this properly.
Any help would be greatly appreciated.
Why is your [TIME_STAMP] column defined as varchar in stead of datetime ??
Please use the correct data type !
Please don't use the old datatypes for (n)text, image, ...
Advise is to replace them with the new (n)varchar(max), varbinary(max) datatypes
Regarding the query....
Did you try this query ?
SELECT DISTINCT
Usr.UserID
, Web.*
FROM #AppServerData Usr
INNER JOIN #WebServerData Web
ON Web.JSESSIONID = Usr.JSESSIONID
ORDER BY Usr.UserID, Usr.JSESSIONID;
/* or just the key values */
SELECT Usr.UserID
, Usr.JSessionID
FROM #AppServerData Usr
INNER JOIN #WebServerData Web
ON Web.JSESSIONID = Usr.JSESSIONID
GROUP BY Usr.UserID
, Usr.JSessionID
ORDER BY Usr.UserID
, Usr.JSessionID;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 31, 2011 at 10:34 am
Thank you. That did it!
February 13, 2011 at 11:59 am
gary.morey (1/31/2011)
Thank you. That did it!
Heh... stop using font sizes, Gary. The defaults are just fine. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
February 13, 2011 at 12:08 pm
Jeff Moden (2/13/2011)
gary.morey (1/31/2011)
Thank you. That did it!Heh... stop using font sizes, Gary. The defaults are just fine. 😉
I thought he was whispering it and SSC doesn't supply a whispering icon;-)
Just kidding Gary.
I'm glad we could help out and appreciate the positive feedback.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply