SQL Query help

[eluser]richzilla[/eluser]
I know this is very cheeky but ive run into a headache with an SQL query ( i was never very good at it) so any help would be appreciated. So far ive only really had to deal with relatively simlple sql queries, or ive limped on with inefficient workarounds but this ones got me stumped. Basically:

There is an issues table and a users table:

The users table contains all the users for the application which are identified with a user ID (primary key), a user name and a user alias.

The issues table has issueClientId (the person who raised the issue) and issueAssignedTo (the person who the issue is assigned to). these columns are both the same as the userId column on the user tables (there not foreign keys because im not using innodb)

My tables are:

Users:

Code:

userId
userName
userAlias

and Issues:

Code:

issueID
issueClientID
issueOpened
issueClosed
issueAssigned
issueAssignedTo

Currently, my query returns the name of the user that raised the issue:

Code:

SELECT issues.*, users.userAlias AS userAlias, users.userName, users.userGroup AS userGroup FROM issues,                                     users WHERE issues.issueClientId = users.userId

however, i also want to return the name of the user that the issue is assigned to. I can do that in its own query easily enough:

Code:

SELECT issues.*, users.userAlias AS userAlias, users.userName, users.userGroup AS userGroup FROM issues,                                     users WHERE issues.issueAssignedTo = users.userId

but i need to do both of those actions in the same query?

Once again any help at all would be hugely appreciated.

Thanks

[eluser]jedd[/eluser]
Hi ricardino,

LEFT JOIN is your friend here.

As an aside, I wrote a guide to [url="http://codeigniter.com/wiki/How_to_ask_a_good_question/"]asking good questions[/url] a while ago - it's always odd to see the actual question (or the question mark) slip in there right at the end of a very long message - means you have to read a really long post all the way through before actually finding out what you're trying to do (in this case a very simple thing) and then re-reading the whole post to make sure the question you asked, the answer I have in my mind, and everything else you've written, all makes sense.

Anyhoo .. your query mentions a thing called users.userGroup, but your table definition doesn't include any mention of a userGroup. Group implies one to many, which suggests your schema is a bit wonky. This isn't hugely germane to your current problem, but might bite you later.

Code:

Users:
   userId
   userName
   userAlias

Issues:
   issueID
   issueClientID
   issueOpened
   issueClosed
   issueAssigned
   issueAssignedTo

I'm assuming your multiple WHERE conditions are because you're attempting to qualify your pseudo-joins, and not because you're selecting based on user id - in other words, that you are in fact only trying to select based on an issue ID. Is that right? (Often it's good to describe your question in terms of what you're actually attempting to achieve - your end-game as it were - and not just something like 'I want this in one query?' - which is a touch ambiguous.)

Try something like this:

Code:

SELECT
    issueOpened, issueClosed, issueAssigned,
    UserReporter.userName,
    UserAssigned.userName
FROM
    Issues
LEFT JOIN
   Users AS UserReporter ON Issues.IssueClientID=Users.UserID
LEFT JOIN
   Users AS UserAssigned ON Issues.IssueAssignedTo=Users.UserID
WHERE
   Issue.issueID = $issue_id

Untested, of course, so you'll have to play with it - let me know how you go, as I don't have a comparable table set handy here to test it.

Plus your variable naming system is a bit blowy^H^H^H^H^H^Hconfusing - I'd highly recommend you come over to the light side and stick with singular table names, field names that have underscores rather than camel case, remove table names from field names, etc. Ultimately I think consistency is the key here - having userId and then issueID - is just plain befuddling. Always write code as though someone else much dumber is going to have to understand it later. (Hint - that someone else will be you, in six months from now.)

[eluser]Marc Arbour[/eluser]
Hi.

I may suggest something else I prefer... Not meaning the above is not correct. I'll write it in semi-english, then semi-sql, then sql... For I have trouble explaining myself in english.

Code:

select your variables from your tables where value in the 1st table equals value in the 2nd table

would approximately read this in sql

Code:

select t1.variable, t1.other_variable, t2.yet_another_variable from users as t1, issues as t2 where t1.unique_id = t2.same_id

as above tables seems to have no corresponding fields, I'll do my best guessing... (

select t1.userName, t2.issueId from Users as t1, Issues as t2 where t1.userId = t2.issueAssignedTo

The above will give you a list of all issues assigned with corresponding names

Code:

select t1.userName, t2.issueId from Users as t1, Issues as t2 where t1.userId = t2.issueAssignedTo AND t1.userName = 'Peter'

Will give you a list of assigned issues to Peter only...

Hope I helped a bit.

Duke