CODEWITHSUNDEEP
mysqlsql
Finchie
Finchie

Reputation: 11

SQL - Renaming fields after query

Im Running into walls with this one but im sure somebody here knows a way around

I have 2 tables for example USERS and ISSUES. In USERS they have we say 2 columns needed

in 'ISSUES` they have 2 columns needed

Im trying to compare and replace a field on output. for example if users.id = issues.assigned_to_id then print 'users.firstnameinstead ofusers.id`

Any help would be fantastic. cheers guys.

EDIT; i can do two queries to what i need, the first one is this;

SELECT assigned_to_id AS Name, COUNT(*) AS Issues FROM issues
WHERE `status_id`=1 
OR `status_id`=2 
OR `status_id`=4
OR `status_id`=7 
OR `status_id`=8 
OR `status_id`=9 
OR `status_id`=10
GROUP BY `assigned_to_id`

and this one;

SELECT id, firstname FROM users

now if users.id = issues.assigned_to_id basically use the corresponding users firstname instead of the id

Upvotes: 0

Views: 64

Answers (4)

Finchie
Finchie

Reputation: 11

I had a look over the weekend, this is what worked and did what i needed it to do. thanks for your help guys.

SELECT COUNT(*) as Issues, assigned_to_id as UserID , users.firstname as Name FROM issues
JOIN users on users.id = issues.assigned_to_id
WHERE `status_id`=1 
OR `status_id`=2 
OR `status_id`=4
OR `status_id`=7 
OR `status_id`=8 
OR `status_id`=9 
OR `status_id`=10
GROUP BY `assigned_to_id`
ORDER BY Issues DESC

Upvotes: 0

xQbert
xQbert

Reputation: 35323

I don't see an issues.firstName column in your example so I assume you mean users.firstname

So to put into plain English what your after...

You want to return all issues and when an issue matches a user return the user name instead of the issueID?

If so then this should do it (Changed left to right as you wanted all issues I believe)

SELECT coalesce(u.FirstName, to_char(I.ID)) as UserNameOrIssueID
FROM Users U
RIGHT JOIN issues I 
 on U.ID = I.assigned_to_ID

The tricky part here is that firstname and ID are likely of different data types, so you have to cast the user.id to a character field in the DB appropriate syntax...

Using explicit cast (I'm also assuming Issue.ID is a numeric field if it's character then the second one below will work fine as it doesn't even need to do the implicit conversion.

SELECT coalesce(u.FirstName, cast(I.ID as char(30)) as UserNameOrIssueID
FROM Users U
RIGHT JOIN issues I 
 on U.ID = I.assigned_to_ID

Hoping implicit works:

SELECT coalesce(u.FirstName, I.ID) as UserNameOrIssueID
FROM Users U
RIGHT JOIN issues I 
 on U.ID = I.assigned_to_ID

Upvotes: 1

Stefan Candrea
Stefan Candrea

Reputation: 210

You can not do that ... Only if you Alter table issues... all you can do is this !

SELECT U.firstname FROM USERS U
LEFT JOIN ISSUES I ON U.id = I.assigned_to_id

I'll pus an update later ... with the alter table ! Stay tune !

UPDATE

IF OBJECT_ID ('tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable

SELECT U.firstname INTO #TempTable FROM USERS U
LEFT JOIN ISSUES I ON U.id = I.assigned_to_id

SELECT * FROM #TempTable

-- ALTER TABLE ISSUES ADD firstname varchar(100) -- RUN ONLY ONCE !

UPDATE I 
SET firstname = (SELECT TOP 1 U.firstname FROM USERS U
                    LEFT JOIN ISSUES I ON U.id = I.assigned_to_id)
    FROM ISSUES I

SELECT * FROM ISSUES

Upvotes: 0

schlagi123
schlagi123

Reputation: 735

I do not know, if this is what you need:

select 
  u.firstname
from 
  users as u,
  issues as i
where 
  u.id = i.assigned_to_id;

Upvotes: 0

Related Questions