-1

I need to join two tables on two different fields. I have table 1 like this:

key  productid  customer
1    100        jhon
2    109        paul
3    100        john

And table 2 has same fields but aditional data I must relate to first table

key  productid  customer  status  date ...
1    109        phil      ok      04/01 
2    109        paul      nok     04/03
3    100        jhon      nok     04/06 
4    100        jhon      ok      04/06

Both "key" fields are autoincrement. Problem is that my relationship fields are repeated several times across result and I need to generate a one-to-one relationship, in such manner that one row from table 2 must be related ONLY ONCE with a row on table 1.

I did a left join on (customer=customer and productid=productid) but relationship came out duplicated, a row from tablet 2 was related many times to rows of table one.

To clarify things...
I have to cross check both tables, table 1 is loaded from an XLS report, table 2 is data from a database that reflects customer transactions with many status data. I have to check if a row from XLS exists in database and then load additional status data. I must produce a report when rows from XLS has no correspondent data on database.

How can accomplish this JOIN, is this possible with only SQL?

ruhalde
  • 3,521
  • 3
  • 24
  • 28
  • 1
    Assume you want to take one of two rows (key=3 and key=4) from table2 for table1 record with key=1: which do you choose? Why? – Marco Apr 21 '12 at 16:46
  • Either one could be, condition is (productid=productid and customer=customer) but one-to-one. For instance, in my example table2-key3 could be related to tablet1-key1 and key3, but my requirement is to relate only once, so can be either key1 or key4 but only once. – ruhalde Apr 21 '12 at 16:56
  • @Marco's question is important. But also, since table1 has the same information as table 2 (which is probably bad schema design), why do you care about table1 at all? – Francis Avila Apr 21 '12 at 16:56
  • I have to cross check information from table 1 to table 2 and load additional data from table 2. – ruhalde Apr 21 '12 at 16:58
  • Can you provide an example of the result you're looking for? – Mosty Mostacho Apr 21 '12 at 17:11

2 Answers2

1

You can accomplish this in MS SQL using the sql below. Not sure if SQLite supports this. 

select a.*, c.*
from table2 a, ( select min(key) key, productid, customer
                 from table1
                 group by productid, customer 
                   ) b,
               table1 c
where a.productid = b.productid
and   a.customer = b.customer
and   b.key = c.key
Steve
  • 11
  • 1
0

One way to understand this would be to figure out what each table represents exactly. Both tables seem to represent the same thing, with a row representing what you might call a purchase. Why are there two separate tables, then? Perhaps the second table goes into more depth about each purchase? Like jhon bought product 100, and it was 'nok' first and then 'ok'? Is so, then the key (what makes the table unique) for the second table would be all three fields. You still join on only the two fields that match, but you can't expect uniqueness if there are two rows with the same unique keys. It helps sometimes to create additional indexes on a table, to see what is truly unique.

Jon Wilson
  • 726
  • 1
  • 8
  • 23
  • I have to cross check both tables, table 1 is loaded from an XLS report, table 2 is data from a database that reflects customer transactions with many status data. I have to check if a row from XLS exists in database and then load additional status data. I must produce a report when rows from XLS has no correspondent data on database. – ruhalde Apr 21 '12 at 17:01