CODEWITHSUNDEEP
mysqlsql
starvator
starvator

Reputation: 1017

Query using a SQL reference table

I have a standard sql database which contains ID, title and description columns but also contains location, category division etc.. that are int values.

I have a second reference sql database which contains an ID column, a reference column and a description column. The ID column has a 2char reference value which reflects what type of data it is holding (CA for category, DI for division etc..)

I would like to display these results in a table, but am unable to write the proper Query.

For example: the title ABC with the description letters has a location of 1 a category of 2 and a division of 3 needs compare the location, category and division with the same reference table and return the appropriate description text. 

Data database
ID | Title | Description | location | category | division |
------------------------------------------------------------
1111 | ABC | letters     |    1     |    2      |    3   |
1112 | ASD | asdasd      |    3     |    2      |    3   |

Reference Database
ID | Reference | Description |
------------------------------
LO | 1        | The location |
DI | 3        | division words | 
CA | 2        | category words |
DI | 1        | ALL |

I want to have a table that returns 

ID | Title | Description | location |    category    | division |
------------------------------------------------------------------
1111 | ABC | letters | The location | category words | division words |



So far, I have 

select  ID
    ,title
    ,description
    ,location
    ,category
    ,division
    ,reference.description as CAname
from data
left join reference on data.category = reference.reference
where id_type = 'CA'

I hope this is clear,

Upvotes: 0

Views: 5388

Answers (1)

Sparky
Sparky

Reputation: 15105

This should give you a starting point.

SELECT dt.ID,dt.Title,dt.description,
       L1.Description as LocationDescription,
       L2.Description as CategoryDescription  ...

FROM data dt
JOIN Reference L1 on dt.location=L1.Reference and L1.ID=='LO'
JOIN Reference L2 on dt.category=L2.Reference and L2.ID=='CA'

I agree with Mike's comment though, this is a bad schema design

Upvotes: 2

Related Questions