| Join question [message #371490] |
Sat, 28 October 2000 17:08  |
Joe Mac
Messages: 3
Registered: October 2000
|
Junior Member |
|
|
I have a table(TABLE_1)where two of the columns contain userID's. The user table(TABLE_2) contains both the codes and the username for the code. I need to do a select out of the first table (mutliple rows will be returned) and join to the second to decode both columns containing the userID's so I can display the usernames that are associated with the codes stored in (TABLE_1).
I can decode one or the other userID's,,, but not both at the same time.
Any help would be greatly appreciated. If I have not explained properly or have not given enought info, please let me know that too. Thanks, Joe
|
|
|
|
| Re: Join question [message #371494 is a reply to message #371490] |
Mon, 30 October 2000 09:02  |
Prem
Messages: 79
Registered: August 1998
|
Member |
|
|
Joe,
Your question was a lil cryptic. This is what i got from it. Correct me if i m wrong.
Table_1 contains these two files (may be more)
ID1 and ID2
Table_2 contains the same set of fields along with the username associated with this pair. i.e. ID1, ID2, USERNAME. You want to retrieve all codes from TABLE_1 along with the associated username. Is so, this query should do
SELECT ID1, ID2, USERNAME FROM TABLE_2 WHERE
(ID1, ID2) IN (SELECT ID1, ID2 FROM TABLE_1)
hth
Prem :)
|
|
|
|
| Re: Join question [message #371497 is a reply to message #371490] |
Mon, 30 October 2000 10:56 |
Joe Mac
Messages: 3
Registered: October 2000
|
Junior Member |
|
|
Sorry for the bad description of my problem.
That isn't what I need to do.
This might be a better description.
Let me know if this makes more sense.Any help would be greatly appreciated.
Thanks so much
--Table One--
user_id assigned_to_id ticket_id
=============================================
2 , 3 , 222
2 , 2 , 223
1 , 2 , 224
5 , 4 , 225
--Table two--
id user_name
============================
1 , Joe
2 , Tom
3 , Bill
4 , Sam
5 , Dave
Display should look like this on my web page...
USER ASSIGNED TO TICKET ID
=============================================
Tom , Bill , 222
Tom , Tom , 223
Joe , Joe , 224
Dave, Sam , 225
|
|
|
|
| Re: Join question [message #371500 is a reply to message #371490] |
Mon, 30 October 2000 12:29 |
Prem
Messages: 79
Registered: August 1998
|
Member |
|
|
Joe,
Then use this
SELECT B.USER_NAME, C.USER_NAME, A.TICKET_ID
FROM TABLE_1 A, TABLE_2 B, TABLE_2 C
WHERE A.USER_ID = B.ID
AND A.ASSIGNED_TO_ID = C.ID
hth
Prem :)
|
|
|
|
| Re: Join question [message #371501 is a reply to message #371490] |
Mon, 30 October 2000 12:31 |
Prem
Messages: 79
Registered: August 1998
|
Member |
|
|
Joe,
forgot to add an alias for the column names. It makes the query more clear.
SELECT B.USER_NAME USER, C.USER_NAME Assigned_to, A.TICKET_ID Ticket
FROM TABLE_1 A, TABLE_2 B, TABLE_2 C
WHERE A.USER_ID = B.ID
AND A.ASSIGNED_TO_ID = C.ID
Prem :)
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
