I am creating a view that displays service codes and descriptions. I have two tables. Table 1 has service codes and table 2 has service code and description.
all of the fields in table 1 are dependant on table 2.
For example
Table 1
service_code1
service_code2
sevice_code3
Table 2
service_code
service_code_description
How can I join all three service codes from table 1 to table 2 to get each individual service code description?
service_code1
service_code_description1
service_code2
service_code_description2
service_code3
service_code_description3
how about you explain a little better and give us a few real values. Table one sounds completely erroneous if it's redundant in the primary key in table two.
this is what i'm pictureing
are certain descriptions missing where you have something in table 1 but nothing in table 2 and you want to know how to join these tables keeping the values in table 1 even though you have no entry in 2? Perhaps i just didn't understand your schema explanation....
Include the same description table three times. You can do this by using aliases, like this:
SELECT
a.Service_code1, b.Service_code_description,
a.Service_code2, c.Service_code_description,
a.Service_code3, d.Service_code_description
FROM table1 a, table2 b, table2 c, table2 d
WHERE
a.Service_code1=b.Service_code
AND a.Service_code2=c.Service_code
AND a.Service_code3=d.Service_code
-Dave
hmm,
table 1 contains three different service code fields.
so the three fields get their description from table 2
does this help?
Originally posted by WineIsGood
Include the same description table three times. You can do this by using aliases, like this:
SELECT
a.Service_code1, b.Service_code_description,
a.Service_code2, c.Service_code_description,
a.Service_code3, d.Service_code_description
FROM table1 a, table2 b, table2 c, table2 d
WHERE
a.Service_code1=b.Service_code
AND a.Service_code2=c.Service_code
AND a.Service_code3=d.Service_code