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: