Joining Tables

[jmob]

I asked this question yesterday, but the answer didn't work for me PHV. I am getting extra rows(erraneous ones). Any other ideas? All of my ideas-and PHV's- seem to give me 50% extra rows.

I have two tables: A & B.

Table A:
ID(Primary Key) | Type
001 A
002 B
003 C

Table B:
ID(this contains duplicates) | Origin(not imptnt.)
001 ZZ
001 YY
001 XX
002 NN

I need to add on the type column onto table B (preferably just a query). For each ID in table B, I need to look up the ID in table A and return the corresponding type. Help is EXTrEMEly appriciated! What the output needs to look like is:

Needed Query Table:
ID | Origin | Type
001 ZZ A
001 YY A
001 XX A
002 NN B

***this gave me 50%extra rows****
PHV (MIS) 21 Jun 05 22:57
Have a look at JOINs and RelationShips:
SELECT B.ID, B.Origin, A.Type
FROM [Table B] B INNER JOIN [Table A] A ON B.ID = A.ID

 

[PHV]

Which extra rows ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fredericofonseca]

the SQL looks correct.

Can you please do two individual selects on table A and B as follows.

select id, count(id) from [Table B]
where id in ( x,y,z,k,l)
group by id

where x,y,z,k,l are 5 of the id's you are getting duplicates from.

Results from table A should be 1 per ID.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[jmob]

It was my fault.. there is another column needed to make a key. Could you add in a [Project ID] (pid) for me with the ID in order to get the data I need still. Thanks so much!

 

[jmob]

I got it. Thanks PHV, you gave me the tools I needed!