Find a same column in different table in sql

Question

I have a small problem, I have four table, I'm using mysql database

Survey Table Here all survey will be created.

╔══════════╤════════╗
║ SurveyId │ Name   ║
╠══════════╪════════╣
║ 1        │ First  ║
╟──────────┼────────╢
║ 2        │ Second ║
╚══════════╧════════╝

Gauge category table

╔════════════╤═════════════╗
║ CategoryId │ Title       ║
╠════════════╪═════════════╣
║ 1          │ Stress      ║
╟────────────┼─────────────╢
║ 2          │ Environment ║
╟────────────┼─────────────╢
║ 3          │ Health      ║
╚════════════╧═════════════╝

To connect both Gauge Category table with Survey table,

I use another table called Title table

Title table

Here title1,title2 and title3 are foreign keys of Gauge Category table, and surveyId is foreign key of Survey table

╔══════════════╤════════╤════════╤════════╤══════════╗
║ GaugeTitleId │ title1 │ title2 │ title3 │ surveyId ║
╠══════════════╪════════╪════════╪════════╪══════════╣
║ 1            │ 2      │ 3      │ 1      │ 1        ║
╟──────────────┼────────┼────────┼────────┼──────────╢
║ 2            │ 1      │ 3      │ 2      │ 1        ║
╟──────────────┼────────┼────────┼────────┼──────────╢
║ 3            │ 3      │ 1      │ 2      │ 2        ║
╚══════════════╧════════╧════════╧════════╧══════════╝

Another table called called Average_values which has respective value of Title table

Average_values table

╔═════════╤════════╤════════╤════════╤══════════╤══════════════╗
║ GaugeID │ Gauge1 │ Guage2 │ Gauge3 │ SurveyId │ GaugeTitleId ║
╠═════════╪════════╪════════╪════════╪══════════╪══════════════╣
║ 1       │ 34     │ 76     │ 23     │ 1        │ 1            ║
╟─────────┼────────┼────────┼────────┼──────────┼──────────────╢
║ 2       │ 56     │ 23     │ 67     │ 1        │ 1            ║
╟─────────┼────────┼────────┼────────┼──────────┼──────────────╢
║ 3       │ 14     │ 28     │ 56     │ 1        │ 2            ║
╚═════════╧════════╧════════╧════════╧══════════╧══════════════╝

My problem is, If I want to get the Stress values from Average_values table, how can I get it? Because the foreign keys are not in order. The output should be 23,56,28 . Is there any way to get it? I have another alternative idea, that modify the Average_values Table to

╔═════════╤════════╤════════════╤══════════╗
║ GuageId │ values │ CategoryId │ SurveyId ║
╚═════════╧════════╧════════════╧══════════╝

and put the values one by one, Here CategoryId and SurveyID are in foreign key relationship of Gauge category table and Survey Table respectively. But I feel, it won't be an efficient table. Because a lot of data wants to be processed dynamically.

Answer 1

I feel your table design is weird and 2 of those tables need to be "unpivoted" in my view to remove the 1,2,3 suffixes on some fields. It remains a mystery as to what the rest of your question is. There is no clear way to join to a table called Guage but has a key of CategoryID below is a query that does an "unpivot" and attempts to join all tables

SQL Fiddle

MySQL 5.6 Schema Setup:

CREATE TABLE Survey 
    (`SurveyId` int, `Name` varchar(6))
;

INSERT INTO Survey 
    (`SurveyId`, `Name`)
VALUES
    (1, 'First'),
    (2, 'Second')
;


CREATE TABLE Gauge 
    (`CategoryId` int, `Title` varchar(11))
;

INSERT INTO Gauge 
    (`CategoryId`, `Title`)
VALUES
    (1, 'Stress'),
    (2, 'Environment'),
    (3, 'Health')
;


CREATE TABLE Title 
    (`GaugeTitleId` int, `title1` int, `title2` int, `title3` int, `surveyId` int)
;

INSERT INTO Title 
    (`GaugeTitleId`, `title1`, `title2`, `title3`, `surveyId`)
VALUES
    (1, 2, 3, 1, 1),
    (2, 1, 3, 2, 1),
    (3, 3, 1, 2, 2)
;


CREATE TABLE Average_values 
    (`GaugeID` int, `Gauge1` int, `Guage2` int, `Gauge3` int, `SurveyId` int, `GaugeTitleId` int)
;

INSERT INTO Average_values 
    (`GaugeID`, `Gauge1`, `Guage2`, `Gauge3`, `SurveyId`, `GaugeTitleId`)
VALUES
    (1, 34, 76, 23, 1, 1),
    (2, 56, 23, 67, 1, 1),
    (3, 14, 28, 56, 1, 2)
;

Query 1:

select
        t.surveyId
      , t.GaugeTitleId
      , g.title Gauge_Title
      , case when cj.n = 1 then t.title1 
             when cj.n = 2 then t.title2
             when cj.n = 3 then t.title3
        end Title
      , case when cj.n = 1 then av.Gauge1 
             when cj.n = 2 then av.Guage2
             when cj.n = 3 then av.Gauge3
        end Gauge
from Title t
cross join (
  select 1 n union all
  select 2 n union all
  select 3 n) cj
inner join Average_values av on t.surveyId = av.surveyId
                            and t.GaugeTitleId = av.GaugeTitleId
inner join Gauge g on t.GaugeTitleId = g.CategoryId  
where g.title = 'Stress'
order by Title, Gauge

Results:

| surveyId | GaugeTitleId | Gauge_Title | Title | Gauge |
|----------|--------------|-------------|-------|-------|
|        1 |            1 |      Stress |     1 |    23 |
|        1 |            1 |      Stress |     1 |    67 |
|        1 |            1 |      Stress |     2 |    34 |
|        1 |            1 |      Stress |     2 |    56 |
|        1 |            1 |      Stress |     3 |    23 |
|        1 |            1 |      Stress |     3 |    76 |

Answer 2

If gauge1, gauge2, gauge3 rapresent the same concept, there is no reason to create multiple fields.

That is an “error” in database design.

You should normalize your table and have one row for every value. Relational databases engines are designed to work in that way. Take a look at relational DB normalization over the internet to learn more.