asked on

multiple tables

Hi all,

I need to make a new table that takes information from 3 tables(table a, b, c);

table a:
id1 x y z
1 a1 b1 c1
2 a2 b2 c2
3 a3 b3 c3

table b:
id1 idR
1 11
2 21
3 31

table c:
id2 text
6 x
7 y
8 z

table wanted:
id1 id2 text
11 6 a1
11 7 b1
11 8 c1
21 6 a2
21 7 b2
21 8 c2
31 6 a3
31 7 b3
31 8 c3

it's kinda complicated....
anybody could help?

Thanks in advance :D

Jim Horn

Seems to me that these tables should be left alone. Each one has one (or two) unique primary keys, and all other fields are descriptors of the primary keys.

What's your business need for combining these tables?

-Jim

Nestorio

It seems that you are combining all rows from tables b and c. But where column text comes from?

ASKER CERTIFIED SOLUTION

lwilkin7

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jim Horn

Write a make-table query, add all 3 tables, make sure the relationships are intact, and make sure all (unique) fields are appended.

wmulyadi

ASKER

Nestorio,
column text comes from table a,
i.e. when id1 is 11, it's value is 1 in table a,
so we'd like the values of x, y, z of that row,
which is a1, b1 and c1.

jimhorn,
I'm not so sure on why they need this new table,
but this is what my supervisor asked me to write...

Thanks :D

SOLUTION

GRayL

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lwilkin7

Quite right to add the column name GRayL.

Thanks,
Lloyd

wmulyadi

ASKER

I got a msg saying: "type mismatch in expression"

here's my current code:

SELECT b.IssueId, c.CustomFieldId,
iif(c.CustomFieldName = 'patient_time_zone', cstr(a.patient_time_zone),
iif(c.CustomFieldName = 'monday_from_time', cstr(a.monday_from_time),
iif(c.CustomFieldName = 'monday_to_time', cstr(a.monday_to_time) ,

iif(c.CustomFieldName = 'tuesday_from_time', cstr(a.tuesday_from_time),
iif(c.CustomFieldName = 'tuesday_to_time', cstr(a.tuesday_to_time),

iif(c.CustomFieldName = 'wednesday_from_time', cstr(a.wednesday_from_time),
iif(c.CustomFieldName = 'wednesday_to_time', cstr(a.wednesday_to_time) ,

iif(c.CustomFieldName = 'thursday_from_time', cstr(a.thursday_from_time),
iif(c.CustomFieldName = 'thursday_to_time', cstr(a.thursday_to_time) ,

iif(c.CustomFieldName = 'friday_from_time', cstr(a.friday_from_time),
iif(c.CustomFieldName = 'friday_to_time', cstr(a.friday_to_time) ,
""
)
)

)
)

)
)

)
)

)
)
) AS test
FROM cati_20040829_yrdu AS a, IssueTracker_Issues AS b, IssueTracker_ProjectCustomFields AS c
WHERE a.patient_id = b.IssueTitle;

lwilkin7

Are all of your x,y,z columns the same type? If not, they have to be converted to the same type for this to work. (i.e. all values in the test (or text) column of the resultant table must be the exact same type.

Looks like that is what you are attempting with cstr.

Also, when you are comparing a.patient_id to b.IssueTitle, these have to be the same type.

I don't know your column types, so that's about all I can speculate without more info.

Lloyd

wmulyadi

ASKER

Thanks a lot guys,

the error msg comes from a.patient_id and b.IssueTitle.

GRayL

Thanks!