0

I'm trying to create an SQL statement to match either an id number or a postcode and then assign a new id number

What I want to end up with is ‘newid’ that correctly recognizes that the first four records are the same person (even though the postcode for record 2 is different).

record  id  postcode  newid
--------------------------
   1    1      1       1
   2    1      2       1
   3    1      1       1
   4    2      1       1
   5    3      3       2

Any suggestions would be appreciated greatly.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user2964644
  • 189
  • 9
  • Sorry can you clarify? you have 3 with id of 1 - one with id of 2 - of those with id of 1 - one has a postcode of 2 and the one with id of 2 has a postcode of 1 - Now we could say (if id or postcode = 1 - new id -> 1?? Is that what you mean?) – GrahamTheDev Jan 08 '14 at 17:29
  • Not quite, because the numerical codes aren't necessarily the same across columns (the numbers are to illustrate). The postcode values could be anything e.g. A,B,A,A,C or 4,5,4,4,6. The statement needs to 'remember' what ID has already been assigned based on the id column. – user2964644 Jan 08 '14 at 17:38
  • I do not understand still - take a step away from columns, tables etc. What are you ACTUALLY trying to achieve - from what you have shown there is no distinctive pattern - add a few more rows to your example if you can – GrahamTheDev Jan 08 '14 at 17:41
  • I am trying to merge records together that belong to the same person. So the statement needs to recognise that records 1 to 4 are the same, because either the id number or the postcode match. Suppose that the postcode column is A,B,A,A,C. Record 4 has the same postcode as records 1 and 2, so it should be assigned the same id number as records 1 to 3. – user2964644 Jan 08 '14 at 17:47
  • Going on that logic, why shouldn't records 1-3 be matched to the id of record 4 instead? i.e., Why shouldn't records 1-3 receive the newid value of 2 instead of the other way around? – Joe C Jan 08 '14 at 18:06
  • That would be fine too, it doesn't matter what ID number they get, as long as they all share the same one. – user2964644 Jan 08 '14 at 18:12

1 Answers1

0

Going based on your example:

SELECT RECORD, 
       (SELECT MIN (ID)
        FROM users u2
        WHERE users.id IN (u2.id, u2.postcode)
        OR users.postcode in (u2.id, u2.postcode)
       ) AS newid

FROM users

This results with the following data:

RECORD     NEWID
------------------
   1         1
   2         1
   3         1
   4         1
   5         3

Here is the SQLFiddle

Joe C
  • 3,506
  • 2
  • 21
  • 32
  • Thanks but when I use some data with text postcodes, record 4 gets a new ID number even though it has the same postcode as records 1 and 3. http://www.sqlfiddle.com/#!2/03658f/1/0 – user2964644 Jan 09 '14 at 15:17
  • Many thanks. Similarly, I want to match dates but allow two of the three portions (dd,mm or yy) to be the same. However, record 7 in this example gets ID 1 but should have its own number (date is 1/1/1900). Why does this record get matched to the others? http://www.sqlfiddle.com/#!2/e8535/1/0 – user2964644 Jan 10 '14 at 09:35
  • @user2964644 You're matching the date parts against the *record* id. In this case, you're matching January 1st against the record id of 1. That's a match. – Joe C Jan 10 '14 at 15:56
  • Do you mean that January 1st is treated as the number 1? – user2964644 Jan 11 '14 at 16:59
  • You're matching any two of a set of three values: record, mm, dd. Row one has a *record* value of 1. The *mm* and *dd* values of row seven are both 1. I'm not really sure why you're checking the *mm* and *dd* values against *record*, but I don't know your business requirements. – Joe C Jan 11 '14 at 20:38
  • I don't want to check the dates against record, I just want to assign the same record number if any two of the date portions match e.g. (dd EQ mm) OR (mm EQ yy) OR (dd EQ yy) – user2964644 Jan 13 '14 at 11:44
  • The code above produces an error message: Unknown column 'RECORD' in 'field list': SELECT RECORD, (SELECT MIN (id) FROM mytable u2 WHERE mytable.id IN (u2.id, u2.postcode) OR mytable.postcode in (u2.id, u2.postcode) ) AS newid FROM mytable – user2964644 Oct 08 '14 at 09:30
  • If you're trying to adapt it for your specific purposes, you may need to change column names. I don't know if "record" in your example was an alias. The fiddle works as expected, but I wrote it based on the sample output you provided. – Joe C Oct 08 '14 at 10:36