Help with another query

[Darkmatter5]

Here's my table examples

 

Jobs

job_id

job_name

type_id_1

type_id_2

type_id_3

1

job1

1

1

3

2

job2

3

4

2

3

job3

1

2

3

 

Types

type_id

type

1

type1

2

type2

3

type3

4

type4

 

Now I need to create a query that would give me the result of.

 

job_id

job_name

type1

type2

type3

1

job1

type1

type1

type3

2

job2

type3

type4

type2

3

job3

type1

type2

type3

 

I was hoping the following query might get me what I need.

SELECT jobs.job_id, jobs.job_name, types.type_id AS type1, types.type_id AS type2, types.type_id AS type3
FROM jobs, types
WHERE types.type_id=jobs.type_id_1
AND types.type_id=jobs.type_id_2
ANd types.type_id=jobs.type_id_3
AND jobs.job_id=73

but it's producing a list of empty cells and yes job_id 73 does exist. :-)

 

What am I doing wrong?

[Mchl]

SELECT jobs.job_id, jobs.job_name, t1.type_id AS type1, t2.type_id AS type2, t3.type_id AS type3
FROM jobs, types AS t1, types AS t2, types AS t3
WHERE t1.type_id=jobs.type_id_1
AND t2.type_id=jobs.type_id_2
ANd t3.type_id=jobs.type_id_3
AND jobs.job_id=73

 

should work

 

BTW: Your database design is not optimal

[Darkmatter5]

Can you explain what you mean by not optimal?

[Mchl]

It's not normalized

You have three fields in your `Jobs` table to store the same type of information.

In most cases this should be remade into many-to-many relation using one more table.

 

It's hard to be 100% sure without knowing your exact requirements, but I'm like... 93.546% sure