Post #75,674
1/20/03 12:30:44 PM
|
Trying to build a query (n00b)
I'm trying to build a fairly complex query. I've got the following tables: USERS\nTICKETS USERS contains (among other things) the fields: USERID\nFIRSTNAME\nLASTNAME
TICKETS contains (among other things) the fields: ASSIGNEDTO\nOPENEDBY\nREQUESTEDBY
All of the above fields in TICKETS contain the USERS.USERID of the relevant user. I've got the following query already defined: SELECT * FROM TICKETS WHERE ASSIGNEDTO= (insert the number of the assigned person here) What I want is to have the query return the first and last names of the assigned individuals in a single string for each field - ASSIGNEDTO, OPENEDBY, and REQUESTEDBY . Any tips, pointers, or suggestions would be appreciated. Flaming cows will be sent to /dev/null. Any deity worthy of a graven image can cobble up a working universe complete with fake fossils in under a week - hey, if you're not omnipotent, there's no real point in being a god. But to start with a big ball of elementary particles and end up with the duckbill platypus without constant twiddling requires a degree of subtlety and the ability to Think Things Through: exactly the qualities I'm looking for when I'm shopping for a Supreme Being.
|
Post #75,677
1/20/03 12:40:17 PM
|
Off the top of my head
You need something like this. I don't have an SQL reference handy, so I might get the syntax wrong but you should get the idea.
SELECT (U1.FirstName + U1.LastName) as AssignedToName,
(U2.FirstName + U2.LastName) as OpenedByName,
(U3.FirstName + U3.LastName) as RequestedByName
FROM Tickets T
INNER JOIN Users U1 ON T.ASSIGNEDTO = U1.UserID
INNER JOIN Users U2 ON T.OPENEDBY = U2.UserID
INNER JOIN Users U3 ON T.REQUESTEDBY = U3.UserID
Jay
|
Post #75,679
1/20/03 12:49:05 PM
|
Blew my SQL engine...
...and I don't have a reference handy either, but it looks like a step in the right direction, so I'll use that and work from there...
Any deity worthy of a graven image can cobble up a working universe complete with fake fossils in under a week - hey, if you're not omnipotent, there's no real point in being a god. But to start with a big ball of elementary particles and end up with the duckbill platypus without constant twiddling requires a degree of subtlety and the ability to Think Things Through: exactly the qualities I'm looking for when I'm shopping for a Supreme Being.
|
Post #75,687
1/20/03 1:14:11 PM
1/20/03 1:14:46 PM
|
What is this INNER JOIN nonsense?
How can I tell that you have worked with Access far more than real database engines..?
Hint: INNER JOIN is not part of SQL.
Cheers,
Ben
"good ideas and bad code build communities, the other three combinations do not"
- [link|http://archives.real-time.com/pipermail/cocoon-devel/2000-October/003023.html|Stefano Mazzocchi]
Edited by ben_tilly
Jan. 20, 2003, 01:14:46 PM EST
|
Post #75,764
1/20/03 7:03:15 PM
|
Why do you say that?
The only references I have are MS, so I don't doubt they could be wrong.
But I was under the impression that JOIN is a standard part of SQL. The INNER part is probably the default join option, so I could have left that out.
In any case, what I gave above does work on SQL Server.
Jay
|
Post #75,772
1/20/03 7:23:54 PM
|
/me relearns the value of double-checking references...
It turns out that INNER JOIN is in the ANSI 92 standard.
However virtually the only place that I have seen it used is Access. And certainly I have never seen anyone working on a non-Microsoft database who uses the INNER JOIN syntax. We all do as, say, Spiceware did above and put the joins in the WHERE clause.
Sorry for my misimpression, but the general observation holds. Microsoft uses the INNER JOIN syntax, but you don't see many non-Microsoft people using it.
Cheers,
Ben
"good ideas and bad code build communities, the other three combinations do not"
- [link|http://archives.real-time.com/pipermail/cocoon-devel/2000-October/003023.html|Stefano Mazzocchi]
|
Post #75,788
1/20/03 8:21:58 PM
|
It works in MS SQL Server as well.
I recall using it a lot when I was a SQL Server programmer.
[link|http://pub75.ezboard.com/bantiiwethey|
New and improved, Chicken Delvits!]
|
Post #75,837
1/21/03 3:05:52 AM
|
Actually, Oracle also uses the JOIN syntax, as of version...
...uh, nine-point-something. (Doesn't work on 8.1.7.2.0, which is what they have here at the client where I am right now.)
And I don't know if it recognizes the keyword 'INNER' -- as someone pointed out, absent a 'LEFT' or a 'RIGHT', the inner join is the default, so it may have been deemed unneccesary.
Anyway, it *is* much cleaner to express your join condition separately from the 'WHERE' clause's limiting condition.
[link|mailto:MyUserId@MyISP.CountryCode|Christian R. Conrad]
(I live in Finland, and my e-mail in-box is at the Saunalahti company.)
Your lies are of Microsoftian Scale and boring to boot. Your 'depression' may be the closest you ever come to recognizing truth: you have no 'inferiority complex', you are inferior - and something inside you recognizes this. - [link|http://z.iwethey.org/forums/render/content/show?contentid=71575|Ashton Brown]
|
Post #75,858
1/21/03 7:35:19 AM
|
And that may be where my bias is from
Simultaneously using "big" databases that don't support it (eg Oracle 8) and Access that defaulted to it at the same time. Which left a taint of, "The stuff that Access produces doesn't work in 'real' databases."
Sort of like the * vs % for a wildcard.
Cheers,
Ben
"good ideas and bad code build communities, the other three combinations do not"
- [link|http://archives.real-time.com/pipermail/cocoon-devel/2000-October/003023.html|Stefano Mazzocchi]
|
Post #75,848
1/21/03 6:18:46 AM
|
I must be an exception.
I've been using INNER JOIN in MySQL extensively for the past year. Haven't programming in Access for several years (got sick of fighting VBA, amongst other things) but that was before I really learned SQL.
MySQL's doco mentions it: [link|http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#JOIN|http://www.mysql.com...ference.html#JOIN]
Maybe it's syntactic sugar, but it works for me. Particularly when I have to figure out a LEFT JOIN or a RIGHT JOIN as the set imagery is, I find, consistent.
Wade.
Is it enough to love
Is it enough to breathe
Somebody rip my heart out
And leave me here to bleed
| |
Is it enough to die
Somebody save my life
I'd rather be Anything but Ordinary
Please
| -- "Anything but Ordinary" by Avril Lavigne. |
|
Post #75,678
1/20/03 12:48:58 PM
|
Is this what you want?
\nSELECT \n *,\n (SELECT LASTNAME + ', ' + FIRSTNAME FROM USERS WHERE USERID = tab.ASSIGNEDTO),\n (SELECT LASTNAME + ', ' + FIRSTNAME FROM USERS WHERE USERID = tab.OPENEDBY),\n (SELECT LASTNAME + ', ' + FIRSTNAME FROM USERS WHERE USERID = tab.REQUESTEDBY)\nFROM TICKETS tab\nWHERE ASSIGNEDTO=1\n
|
Post #75,680
1/20/03 12:50:00 PM
|
That looks good...
Any deity worthy of a graven image can cobble up a working universe complete with fake fossils in under a week - hey, if you're not omnipotent, there's no real point in being a god. But to start with a big ball of elementary particles and end up with the duckbill platypus without constant twiddling requires a degree of subtlety and the ability to Think Things Through: exactly the qualities I'm looking for when I'm shopping for a Supreme Being.
|
Post #75,681
1/20/03 12:50:13 PM
|
Here I come to save the day
I am assuming that you have ASSIGNEDTO set to the same as USERID? In that case, this ANSI SQL statemet should work:
SELECT *,
(SELECT FIRSTNAME + ' ' + LASTNAME FROM USERS WHERE USERID = TICKETS.ASSIGNEDTO)AS ASSIGNEDNAME,
(SELECT FIRSTNAME + ' ' + LASTNAME FROM USERS WHERE USERID = TICKETS.OPENEDBY) AS OPENEDNAME,
(SELECT FIRSTNAME + ' ' + LASTNAME FROM USERS WHERE USERID = TICKETS.REQUESTEDBY) AS REUQESTEDNAME
FROM TICKETS
WHERE ASSIGNEDTO = FOO
Welcome to the wonderful world of sub-queries. You didn't say which database, so I am assuming ANSI SQL. Your millage may vary, please let me know if it works, I didn't have a chance to test it. It may need some tweaking. I think the parenthesis are needed. But I haven't done SQL since November, and my brain has lost some of my knowledge or I've forgotten it.
[link|http://pub75.ezboard.com/bantiiwethey|
New and improved, Chicken Delvits!]
|
Post #75,685
1/20/03 1:08:34 PM
|
Fixed.
Both Chris and Orion seem to have hit the magic combo - worked exactly like I wanted. Tried Chris's, spotted Orion's (which looks almost identical) solutions, and it's all good.
Thanks guys!
Any deity worthy of a graven image can cobble up a working universe complete with fake fossils in under a week - hey, if you're not omnipotent, there's no real point in being a god. But to start with a big ball of elementary particles and end up with the duckbill platypus without constant twiddling requires a degree of subtlety and the ability to Think Things Through: exactly the qualities I'm looking for when I'm shopping for a Supreme Being.
|
Post #75,789
1/20/03 8:23:07 PM
|
We posted around the same time
it may have taken me longer to type it out, and I may have made a spelling mistake or two. But I think I can still hash out SQL Queries.
Your welcome.
[link|http://pub75.ezboard.com/bantiiwethey|
New and improved, Chicken Delvits!]
|
Post #75,702
1/20/03 2:01:36 PM
1/20/03 2:06:41 PM
|
this is how I'd do it
select t.*,\n (u1.FIRSTNAME || ' ' || u1.LASTNAME) AT_FULLNAME,\n (u2.FIRSTNAME || ' ' || u2.LASTNAME) OB_FULLNAME,\n (u3.FIRSTNAME || ' ' || u3.LASTNAME) RB_FULLNAME\n from tickets t,\n users u1,\n users u2,\n users u3\n where t.ASSIGNEDTO = u1.USERID (+)\n and t.OPENEDBY = u2.USERID (+)\n and t.REQUESTEDBY = u3.USERID (+)\n u1.USERID would be NULL if there wasn't a match. W/out the (+) the result would be empty if any USERID didn't match. I've standardized our company to use this format for queries. It makes them much easier to read and maintain. Note: I'm using Oracle Darrell Spice, Jr.
[link|http://www.spiceware.org/|SpiceWare] - We don't do Windows, it's too much of a chore
Edited by SpiceWare
Jan. 20, 2003, 02:06:41 PM EST
|
Post #75,703
1/20/03 2:06:00 PM
|
Do you do that...
...even when the DB is guaranteed to always have a match between UserID and the other fields?
Just curious, as a n00b db programmer, always looking out for Best Practices, etc.
Any deity worthy of a graven image can cobble up a working universe complete with fake fossils in under a week - hey, if you're not omnipotent, there's no real point in being a god. But to start with a big ball of elementary particles and end up with the duckbill platypus without constant twiddling requires a degree of subtlety and the ability to Think Things Through: exactly the qualities I'm looking for when I'm shopping for a Supreme Being.
|
Post #75,706
1/20/03 2:24:33 PM
|
As to whether you *should* do that
It sort of depends on the DB you're using. If you're using something like MySQL without triggers and foreign key constraints[1] then you can't really "guarantee" that there will really always be matches. Adding field constraints as in Postgres or Oracle, you can guarantee that a mis-typed query will never produce invalid data.
Any time I think I can rely on an assumption that is only enforced by policy, and not by the system itself, the assumption is always proved wrong at some point. I like having assumptions spelled out in the code/queries.
[1] Yes, I know there are versions that have it. I don't know anyone who has migrated a production system to one of these versions yet.
===
Microsoft offers them the one thing most business people will pay any price for - the ability to say "we had no choice - everyone's doing it that way." -- [link|http://z.iwethey.org/forums/render/content/show?contentid=38978|Andrew Grygus]
|
Post #75,707
1/20/03 2:30:06 PM
|
how's it guaranteed?
is the relationship being enforced by the database? I've been bit a couple times where data that was "guaranteed to be there" wasn't. While I don't always use it, if there's a chance of data missing I'll use (+) and test for null on the key field using ISNULL() in the program. NULLS are hard to see when running the query interactively, so I'd then use the NVL function: NVL(p1,p2) = if p1 is null then return p2 else return p1select t.*,\n nvl(u1.FIRSTNAME, 'data missing') AT_FIRSTNAME,\n nvl(u2.FIRSTNAME, 'data missing') OB_FIRSTNAME,\n nvl(u3.FIRSTNAME, 'data missing') RB_FIRSTNAME\n from tickets t,\n users u1,\n users u2,\n users u3\n where t.ASSIGNEDTO = u1.USERID (+)\n and t.OPENEDBY = u2.USERID (+)\n and t.REQUESTEDBY = u3.USERID (+) I found that using (+) is a quick(performance) way to get a list of what's not in the other file. To see all tickets with an invalid ASSIGNEDTO value, I'd use this: select t.*,\n from tickets t,\n users u1\n where t.ASSIGNEDTO = u1.USERID (+)\n and u1.USERID is null\n Darrell Spice, Jr.
[link|http://www.spiceware.org/|SpiceWare] - We don't do Windows, it's too much of a chore
|
Post #75,714
1/20/03 2:53:02 PM
|
Enforced by DB.
|
Post #75,704
1/20/03 2:19:48 PM
|
This is how I'd do it
SELECT t.*,\n (u1.FIRSTNAME || ' ' || u1.LASTNAME) AT_FULLNAME,\n (u2.FIRSTNAME || ' ' || u2.LASTNAME) OB_FULLNAME,\n (u3.FIRSTNAME || ' ' || u3.LASTNAME) RB_FULLNAME\n FROM tickets t,\n LEFT JOIN users u1 ON t.USERID = u1.USERID,\n LEFT JOIN users u2 ON t.USERID = u2.USERID,\n LEFT JOIN users u3 ON t.USERID = u3.USERID This will solve the problem of non-matching records that you fixed on Oracle with the (+) operator. If you can assume there will always be a match, you can do: SELECT t.*,\n (u1.FIRSTNAME || ' ' || u1.LASTNAME) AT_FULLNAME,\n (u2.FIRSTNAME || ' ' || u2.LASTNAME) OB_FULLNAME,\n (u3.FIRSTNAME || ' ' || u3.LASTNAME) RB_FULLNAME\n FROM tickets t,\n users u1,\n users u2,\n users u3\n WHERE\n t.USERID = u1.USERID AND\n t.USERID = u2.USERID AND\n t.USERID = u3.USERID The second will (I believe) be faster. I can't verify the first version is SQL92 compliant, but works[1] on both Postgres and MySQL. [1] "I have not tested this, only verified it for correctness." ===
Microsoft offers them the one thing most business people will pay any price for - the ability to say "we had no choice - everyone's doing it that way." -- [link|http://z.iwethey.org/forums/render/content/show?contentid=38978|Andrew Grygus]
|
