Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Aliasing and ordering 1

Status
Not open for further replies.

zeland

IS-IT--Management
Aug 19, 2002
569
MY
Is it possible for me to alias a column with data from another table and sorting it using a direct SQL statement.
Example:
[tt]
Table: diagnosis
+--------+--------------------------------------+
| name | diag |
+--------+--------------------------------------+
| Jack | severe back sprain at right shoulder |
| David | back pain at 5th vertebrae. |
+--------+--------------------------------------+

Table: aliases
+-------------+-------------+
| generalTerm | translate |
+-------------+-------------+
| backache | back pain |
| backache | back sprain |
+-------------+-------------+
[/tt]
Ultimately, I would like to get output like below ordered by diag:
[tt]
+--------+----------+
| name | diag |
+--------+----------+
| Jack | backache |
| David | backache |
+--------+----------+
[/tt]


--== Anything can go wrong. It's just a matter of how far wrong it will go till people think its right. ==--
 
How about something like:
[tt]
SELECT name,generalTerm diag
FROM
diagnosis d
LEFT JOIN aliases a
ON d.diag LIKE CONCAT('%',a.translate,'%')
GROUP BY name
[/tt]
If there is more than one generalTerm for a patient's diagnosis, just one of them will be returned; if you want them all returned, leave out the GROUP BY clause.
 
So CONCAT('%',a.translate,'%') was the bleeping piece of code that I needed. All I could think off was d.diag LIKE a.translate but all I got was an empty set. Wasted almost a whole day trying to figure it out. Thanks a million.


--== Anything can go wrong. It's just a matter of how far wrong it will go till people think its right. ==--
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top