Joining 2 tables

[athan00]

Hi,
I have 2 tables. Table TA, Table TB. TA has two fields: code, descr. TB has five fields: code, descr, value, type, avail. TB has some records of TA (code,descr and also the rest of the fields). Now my question is how can I join these tables in a third one? Actually what I need is just the select statement.

I've tried but strange results came up. My main problem is that in the third table I want to have both TA,TB records but code field must be unique. And also the TA records must NOT have any value at the fields value,type,avail at the third table.

Any help?

Thanx
athan

 

[tdatgod]

Hi,
This sounds like a OUTER join where the records from A which don't exist in B are included in the result set with NULL values for the non existent fields.

However I guess I have a problem with duplicating data between 2 tables.

Typically TA should contain Code, description

Then TB should contain just Code, value, type, avail

that way when you update the description of the code you don't have to update both TA and TB.

Now I guess this assumes that there is only one description per code which if there isn't then this is a moot issue.

 

[PruSQLer]

tdatgod is right in saying you shouldn't have the description field in 2 different tables. It should just be in a code table.

That being said, couldn't you just do the following?

Select ta.code,ta.descrip,tb.value,tb.type,tb.avail
From tableA ta, tableB tb
Where ta.code = tb.code

I'm assuming the codes are in synch in the tables.