1 job code & 4 locations for each

officework13
04-14-2004, 10:15 AM
i have 2 tables whith the following
table 1 called [JC]
this table only has the job codes which are numbers such as 120, 121, 122, 150, 180, 250, 260, 320, 390, 450, etc.

table 2 called [JL]
this table has the job locations in which there are only 4 locations as follows: C440, S440, R440, & R442.

i need to create a query (which i need for a different query) which will do the following.
I need to create 4 records for every job code. i will have 2 collums collum 1 will have job codes and collum 2 will have the job locations. Every job code can be at 1 of 4 locations. for example
JC JL
120 C440
120 S440
120 R440
120 R442
121 C440
121 S440
121 R440
121 R442
etc

please provide assistance

HardCode
04-14-2004, 10:25 AM
Just "SELECT * FROM JC,JL;" and you will get the Cartesian Product of the tables, which is every record from one table (JC) matched to every record in another table (JL). However, this is bad SQL and if there is going to be more in terms of queries between these tables or other tables with these values, then I would suggest a better relational scheme.

officework13
04-14-2004, 11:10 AM
Thanks that worked for what i asked.
however, i did not provide all the details (to avoid confusion) and I need a little more help on this one. i said that the above only had 1 field in each table . Well there are 6&5 fields in each table. how do i do it so that only select fields are displayed. (i.e. i would like the following fields displayed (from the JC table - Job code, position, class and from the JL table - location code, location description)

officework13
04-14-2004, 11:52 AM
i figured it out.
thanks for the help you provided it was the hurdle that was stopping me

thanks again

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum