PL/SQL: Selecting multiple values from the same column

plsqlreport-builderssrs

We have a table in which all kinds of different Words are stored with a unique id each. In other tables, certain fields which should contain words instead only contain the id of these words. Let's look at an example.

This is our example "words" table:

+---------+--------+
| word_id |   word |
+---------+--------+
|       1 |  Apple |
|       2 |  Melon |
|       3 |    Car |
|       4 |   Boat |
+---------+--------+

In another table (example: "rows"), there are references to that table:

+--------+----------+------------+
| row_id | fruit_id | vehicle_id |
+--------+----------+------------+
|      1 |        1 |          4 |
|      2 |        2 |          3 |
|      3 |        1 |          3 |
|      4 |        2 |          4 |
+--------+----------+------------+

For my SSRS Report, I need to select many rows of the "words" table and insert the value of the "word" column into the report. For now I have solved it by creating a different SQL query for each field using report parameters to select the id, but I was wondering if I could make the report more efficient by cutting down the number of SQL queries.

How I did it for now using our example:

SELECT
    rows.id,
    words.word AS FRUIT
FROM
    rows
INNER JOIN
    words ON
    rows.fruit_id = words.word_id
WHERE
    words.word_id = :FRUIT_ID
----------------------------------------
SELECT
    rows.id,
    words.word AS VEHICLE
FROM
    rows
INNER JOIN
    words ON
    rows.vehicle_id = words.word_id
WHERE
    words.word_id = :VEHICLE_ID

The ideal output for row = 1 would be:

+--------+-------+---------+
| row_id | fruit | vehicle |
+--------+-------+---------+
|      1 | Apple |    Boat |
+--------+-------+---------+

Sadly, I have not found a way to select multiple values of the same column in just one SQL statement.

I would be very grateful for any ideas or solutions, Thanks!

EDIT: I know the "words" table setup is not ideal and can have its own problems, however, I cannot do anything about them since the database is to be used as-is and I am not allowed to change anything.

Best Answer

You can reference the same table multiple times in your query with an alias to solve this issue.

For example:

SELECT ref.row_id, f.word as fruit, v.word as vehicle
  FROM Rows ref INNER JOIN Words f ON ref.fruit_id = f.id
                INNER JOIN Words v ON ref.vehicle_id = v.id
 WHERE f.id = :PRODUKTART_ID
   and v.id = :VEHICLE_ID