SQL Multiple values 1

[thelobe]

Hi, I seem to have the same problem as tekdenied, only thing is, i do not use 1, 2, 3 or 4... What basicly happens is, i need to get user names from a table. But the IRCs table has two columns which use user id's, and i need to return different user names from the users table...

This is the code im using now...

SELECT dbo.TBL_USERS.USER_NAME AS ASSIGNED_USER_NAME,
dbo.TBL_USERS.USER_NAME AS RAISED_USER_NAME

FROM TBL_IRCS,
TBL_USERS

WHERE USER_ID = IRC_RAISED_BY
AND USER_ID = IRC_ASSIGNED_TO

Any help would be much appreciated.

 

[Katy44]

I'm not sure if I understand exactly what you are trying to do.
Do you need, for each record, to return the names of two users - the assigned user and the raised user?
If so, you need two copies of the user table, and you can alias them accordingly, something like:

SELECT ASSIGNED.USER_NAME AS ASSIGNED_USER_NAME,
RAISED.USER_NAME AS RAISED_USER_NAME

FROM TBL_IRCS,
TBL_USERS ASSIGNED,
TBL_USERS RAISED

WHERE RAISED.USER_ID = IRC_RAISED_BY
AND ASSIGNED.USER_ID = IRC_ASSIGNED_TO

something like the above?

Sorry if I have completely misunderstood your problem!

 

[Katy44]

By the way, you should probably be using inner or left joins rather than the joins shown, although I am happier with the joins you have done as I have an Oracle background, the inner / left / right joins are better to use in SQL Server.

 

[thelobe]

Thanks for the response!

Creating two user tables would solve the problem, but that isnt an option, not in this case anyway. I am not familiar with joins, they look to complicated to me isnt there some other way?

 

[Katy44]

Why is it not an option? I don't mean creating a physical table in the database, I just mean referring to it two separate times in the query.
If you can only refer to it once in your query, then I don't think there's a way to do it, as you will be getting the same value for both users.
The joins will be something like:

SELECT ASSIGNED.USER_NAME AS ASSIGNED_USER_NAME,
RAISED.USER_NAME AS RAISED_USER_NAME

FROM TBL_IRCS inner join TBL_USERS ASSIGNED
ON TBL_IRCS.IRC_ASSIGNED_TO = ASSIGNED.USER_NAME
inner join TBL_USERS RAISED ON TBL_IRCS.IRCRAISED_BY = RAISED.USER_NAME

something like that

 

[thelobe]

Hmmm, okay, i see what you mean. I tried the code and i got this

Syntax error converting the varchar value 'PTT Administrator' to a column of data type int.

I realised that in TBL_IRCS the value of IRC_ASSIGNED_TO is an int, i.e. the USER_ID, not a varchar.... What now?

 

[thelobe]

Nevermind! Got it working!

Thanks alot!! I think i need to learn joins...hehe

 

[Katy44]

So in the TBL_IRCS the value of IRC_ASSIGNED_TO is an int.
And users.user_id is a varchar?
Is that it? Are they joined in the database by a relationship?

 

[Katy44]

No problem - good luck with the rest of it

 

[thelobe]

Uhm, see, TBL_USERS.USER_ID is an int, and TBL_IRCS.IRC_ASSIGNED_TO is an int, and TBL_USERS.USER_NAME is the varchar...

I just had to think a bit more

This is the code that works

SELECT ASSIGNED.USER_NAME AS ASSIGNED_USER_NAME,
RAISED.USER_NAME AS RAISED_USER_NAME

FROM TBL_IRCS inner join TBL_USERS ASSIGNED
ON TBL_IRCS.IRC_ASSIGNED_TO = ASSIGNED.USER_ID
inner join TBL_USERS RAISED ON TBL_IRCS.IRC_RAISED_BY = RAISED.USER_ID

Thanks once again