Nismo
Member
Registered: 12th Sep 02
User status: Offline
|
Ok a little bit stuck.
I have this query that I run for reporting,
code:
$query="SELECT cases.*, cases.id AS casesid, cases.name AS subject, users.id, users.user_name, accounts.name, accounts.id
FROM users, accounts, cases
WHERE cases.created_by = users.id
AND cases.account_id = accounts.id
AND accounts.name LIKE '%$customer%'
AND cases.status LIKE '$status'
AND cases.type LIKE '$type'
AND cases.date_entered >= '$from'
AND cases.date_entered <= '$to' ORDER BY cases.date_entered ASC";
This returns all cases between a certain date range, When i row it out onto a table i have 2 fields that need to pull the username from the users table, the cases table contains created_by and modified_user_id which has the users.id
code:
cases.created_by = users.id
lets me replace there users.id with users.user_name however i cant use user_name for 2 rows as it will not pull both out?
Not sure if i have explained this very well. 
[Edited on 31-10-2012 by Nismo]
|
AndyKent
Member
Registered: 3rd Sep 05
User status: Offline
|
Don't get what you're asking.
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Online
|
You need a JOIN .
Can do you give us a few sample rows from each table?
SELECT * FROM users LIMIT 10;
etc.
u2u it if its real data.
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Online
|
SELECT cases.*, users.user_name AS created_username FROM cases INNER JOIN users ON cases.created_by = users.id LIMIT 10;
No WHERE stuff in that so it'll give you the wrong rows but the user name information should be correct for whoever has created the case.
|
James
Member
Registered: 1st Jun 02
Location: Surrey
User status: Offline
|
I don't understand your question but you definitely need some joins otherwise your query will render unexpected results.
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Online
|
As I understand it the case table has user ids in which need the retrieval of the username from the users table.
Slightly more complicated because the user id is in there twice and its a different user each time, ie.
case
caseid | createdby | modifiedby
101 | 4 | 6
user
userid | name
1 | ian
2 | dave
3 | bob
4 | frank
5 | lisa
6 | tony
and he wants to see
101 | frank | tony
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Online
|
SELECT c.*, u1.user_name AS created_username, u2.user_name AS modified_username FROM cases c
INNER JOIN users u1 ON c.created_by = u1.id
INNER JOIN users u2 ON c.modified_by = u2.id
LIMIT 1
Something like that although the field names might need correcting
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Online
|
That won't display any rows if there is no user id in either created_by/modified_by field which I suspect will be a problem for modified_by, try that with LEFT JOIN on the second JOIN and you'll see NULL where there is no valid modified_by id.
|
James
Member
Registered: 1st Jun 02
Location: Surrey
User status: Offline
|
Something like this should do that.
SELECT C.CaseId, U1.Name AS CreatedBy, U2.Name AS ModifiedBy
FROM Cases C
JOIN Users U1 ON C.CreatedBy = U1.UserId
JOIN Users U2 ON C.ModifiedBy = U2.UserId
|
James
Member
Registered: 1st Jun 02
Location: Surrey
User status: Offline
|
FFS Ian 
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Online
|
It'll still need work - I would think modified_by is going to sometimes be empty.
|
Nismo
Member
Registered: 12th Sep 02
User status: Offline
|
Thanks, Yeah Ian summed up what i was trying to say in a lot less work 
I was reading up on the JOIN, but then came across inner join, right join and left join and got brain frazzled
The modified by is never empty as when a case is logged it auto updates the modified_by at the same time to be the initial user.
Cheers guys I will have a little play around this morning and report back what I get 
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Online
|
If modified_by is never empty you can use an inner join. The only difference is that if you want to join, inner won't display the row unless the join condition is met, for example:
case
caseid | createdby | modifiedby
101 | 1 | 1
102 | 1 | 4
103 | 2 | 1
104 | 2 | null
user
userid | name
1 | ian
2 | dave
3 | tony
no user id 4 in that table yet
The inner join query would give you
101 | ian | ian
103 | dave | ian
104 | dave | null
The outer join would give you
101 | ian | ian
102 | ian | null
103 | dave | ian
104 | dave | null
null | tony | null
See how on 102 it gives you null instead of user 4 because there is no user 4. You also get null cases and user tony even though tony has no cases. Its just variations on the same theme, the type of join just dictates whether all fields need to be present or not and if not, which ones from which tables.
The left is the same but wouldn't give you 104 because of the null in modifiedby
If you always have data in modified_by then that makes the query more simple. Just make sure someone in the future isn't going to modify the code that writes the row initially because your queries will start not displaying those rows. At least if you get the right type of join that will stand up to anyone dicking about with in the future who doesn't know what they're doing. You might find someone in the future will realise that writing modified by when the case hasn't been modified yet isn't actually correct.
That's all MySQL specific btw. Microsoft stuff is similar but a bit more restrictive in terms of syntax, I'd need to look that up, can't do it from memory. Needs nesting with brackets I think.
As an aside, modified_by should really be normalised in its own table, that way you could have dates that the stuff was modified against all the additional rows and you'd end up with a bit of an audit trail of all the modifications. If you needed it in a single row like you're currently seeing, you could order by date descending and limit 1 on the query so you only see the newest. Looks the same as you have right now but its far more powerful and you don't lose data like you are now.
|
Nismo
Member
Registered: 12th Sep 02
User status: Offline
|
Thanks Ian thats spot on never got round to having a look yesterday hopefully fit it in this afternoon
|
