Question 335582 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL between two tables where we tacking good result

I have an issue between two tables in sql

Table 1

name_id 1 2 3
SPEC-A 100 110 120
SPEC-B 200 220 230

Table 2

name name_id cat
M.John SPEC-B 2
J.Smith SPEC-A 3

So I would like to connect two tables where I would like to select persons name_id and his category in table 2 with rows and columns in table 1

select * 
from table 1
inner join table 2 on table1.name_id = table2.name_id

But I can’t connect table2.cat with each columns in table 1
for example table2.cat = 2 with table1.2 automaticaly.

I would like to see next:

name name_id cat price
M.John SPEC-B 2 220
J.Smith SPEC-A 3 120

I would like to see next:

name name_id cat price
M.John SPEC-B 2 220
J.Smith SPEC-A 3 120
Tags:

3

Answers


  1. 0

    The query will be :

    WITH T AS
(    
SELECT T2.name, T2.name_id, T2.cat,
        CASE T2.cat WHEN 1 THEN T1."1"
                    WHEN 2 THEN T1."2"
                    WHEN 3 THEN T1."3"
        END AS price
FROM    "Table 1" AS T1
           JOIN "Table 2" AS T2
              ON T1.name_id = T2.name_id 
)
SELECT *
FROM   T
WHERE  price IS NOT NULL;

    But your data structure does not respects the state of art in data modeling…. SO the performances will be catastrophic….

    Table 1 will gain to be split into 2 tables :

    CREATE "Table 1" 
(name_id VARCHAR(32) PRIMARY KEY);

CREATE "Table 1.1" 
(name_id VARCHAR(32) NOT NULL REFERENCES "Table 1" (name_id), 
 cat INT NOT NULL, 
 price DECIMAL(16,2) NOT NULL, 
 PRIMARY KEY (name_id, cat );

    Then you can have a view that presents the old version of "Table 1"… (with some PIVOT operator or at least a CASE statement).

    Login or Signup to reply.
  2. 0

    It’s so much easier to talk about your problem if you use realistic column names. I’m using table1 etc and col2 etc.

    select table2.name, table2.name_id,
    elt(table2.cat, table1.col1, table1.col2, table1.col3) price
from table1
inner join table2 on table1.name_id = table2.name_id
    Login or Signup to reply.
  3. 0

    In a consistently designed database table1 would look like this: table1 (name_id, cat, price).

    You say, though, that you are forced to work with the inappropriately designed table.

    My suggestion: Tell them they should change their data model. If they are intelligent people they will probably do so.

    If they don’t, create a view that represents the missing table:

    create view view1 (name_id, cat, price) as
  select name_id, 1 as cat, `1` as price
  union all
  select name_id, 2 as cat, `2` as price
  union all
  select name_id, 3 as cat, `3` as price
;

    The column names 1, 2 and 3 are a horror of course and can cause problems. Above CREATE VIEW statement uses syntax that is valid only in MySQL. Standard SQL does not use backticks for names. In standard SQL you’d use double quotes (") instead. So depending on what mode MySQL is run in, one or the other form will be invalid. With such names the database administrator must make sure to decide for one mode and rigidly stick to it.

    The query will become a simple:

    select *
from view1 t1
join table2 t2 using(name_id, cat)

    If you are not allowed to create the view and they don’t do it for you either, then use an ad-hoc view:

    with view1 as ( <see above> )
  select *
  from view1 t1
  join table2 t2 using(name_id, cat)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search