3

I've been trying to create a query using COALESCE with multiple forms of matching in order to join two tables -- but as far as I can tell, it hasn't been working.

Can somebody tell me what's wrong with this query?

SELECT *
FROM t1
LEFT JOIN t2 ON COALESCE(t1.id,t1.phone,t1.address) = COALESCE(t2.id,t2.phone,t2.address)

Something like that. The hope would be that the query would look to see if the unique IDs in t1 and t2 matched first, and if they didn't, it would move on to see if the phones matched, etc. It would be very helpful to attempt to match on multiple sets of criteria and to return the ones that matched any of the column and to only return the NULLs from t1 if the query couldn't find a match at all.

Edit:

By "not working," I meant that it seems like the ID matching works -- where it will return the data from t2 (and not NULLs) if the unique identifiers matched, but it doesn't move on to attempt to match on phone number or address line; this is obviously likely because the t1 table isn't returning any NULL values.

The definitions of the table is that t1 is a smaller subset of data that likely lives in the larger t2 table. Let's say that t1 is a table of about 100 people with only a few criteria: name, phone, address, id (though ID doesn't exist in every row) -- whereas t2 is a larger table of about 30,000 with much more criteria (name, phone, address, id, job, volunteer, email, notes, etc.) where I'm trying to find the 100 within the 30k.

Ryan
  • 501
  • 1
  • 12
  • 26
  • If specific data can appear in id,phone, or address then you probably have bigger problems. – Strawberry Jan 12 '15 at 22:56
  • COALESCE returns the first non NULL value and stops there.The tables would need "synchronized NULLs" to jump through all values.And why would you keep the same-ish data in 2 tables? – Mihai Jan 12 '15 at 23:04
  • Please describe non-working (edit your question). And show the definitions of the table. – Gordon Linoff Jan 12 '15 at 23:04
  • @Strawberry Just used those three as an example to show what I mean. In this case, you could just assume that both tables are small enough in the sense that the phone numbers and/or addresses would both be unique. – Ryan Jan 12 '15 at 23:06
  • @Mihai Edit added above! – Ryan Jan 12 '15 at 23:15
  • @GordonLinoff Edit added above! – Ryan Jan 12 '15 at 23:15

1 Answers1

2

I suspect that you want this:

SELECT *
FROM t1 LEFT JOIN
     t2
     ON t1.id = t2.id OR t1.phone = t2.phone or t1.address = t2.address;

This will match two rows if any of the keys match. However, you might want:

SELECT *
FROM t1 LEFT JOIN
     t2
     ON (t1.id = t2.id) OR
        ((t1.id is null or t2.id is null) AND t1.phone = t2.phone) or
        (((t1.id is null or t2.id is null) and (t1.phone is null or t2.phone is null)) and t1.address = t2.address
        )

Your query could be failing for a number of reasons. One possibility is type incompatibility. Another is that different rows have different distributions of NULL values, so you end up comparing difference columns, such as t1.id to t2.address.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks! I've attempted the first way before, but tried to move onto something like COALESCE so that I didn't get multiple results for each record if, for example, both their IDs matched and their phone numbers match. But I suppose COALESCE doesn't work, because there's not going to be a NULL in `t1` since all the values are coming from there. – Ryan Jan 12 '15 at 23:19
  • And I think the second query is probably closer. As I said above, things will generally only show up as NULL in the return values if it can't match on one of the above criteria. Is there a way to do two things: (1) If it matches on the first criteria (e.g. ID), can it stop there and only give me the `t2` results based on the ID match, without giving me multiple return rows from matching phone/address/etc? (2) And it's less about the value in the original tables being null, and more about moving onto the next criteria if there's no match from the first. Is that possible? – Ryan Jan 13 '15 at 21:38
  • @Ryan . . . You might want to ask another question with sample data and desired results. – Gordon Linoff Jan 14 '15 at 13:32