Compare Question

[montollo]

I have problem….
I’m trying to pull data from two different tables by the code number, here is an example:
I want to compare data from Table A with the data from Table B. Both tables have the same field names, but there is no primary key because Code 1 in table A is actually populated differently in table B. This means that I can not pull data without first matching Codes. Did I explain myself correctly?

Table A
Code Type Cond
1 | Ford |new.


Table B
Code Type Cond
101 | Mazda |new

Thanks

 

[PHV]

Did I explain myself correctly?
I quite don't understand what is the final goal ?

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

 

[rac2]

Either you have a combination of columns in Table A that exactly matches a combination of columns in Table B, or you dont. If you dont, no one, not God, man, nor SQL can JOIN them.

The names of the columns is pretty much irrelevant.

A primary key is nothing more than a column, or a combination of columns, that has unique values, no two rows from the table have the same value. The uniqueness can be enforced and efficiency can be enhanced by adding a PRIMARY KEY CONSTRAINT to a table, but otherwise, it is just as I said.

The stored values in a column in one table may not directly provide values that match values in another table, but sometimes they can be used in expressions to provide matching values. For example TableA.x = TableB.w + 100; or SUBSTRING(TableB.w, 2, 1) = TableA.x.

When it comes to automobiles, pretty much the only candidate for a primary key is the VIN, otherwise there is nothing unique about a particular ride.

 

[rrrkrishnan]

May be this is what you are looking for.

select case when a.code is null then b.code else a.code as code,
case when a.code is null then b.type else a.type as type,
case when a.code is null then b.cond else a.cond as cond
from tbl_a a
full outer join tbl_b b
on a.code=b.code