Comparing fields in the same table
We moved data from a set of fields in a table to another set of fields in the same table.
These are the old field names:
QWife1Child1 , QWife1Child2, QWife1Child3
QWife2Child1 , QWife2Child2, QWife2Child3
QHusb1Child1 , QHusb1Child2, QHusb1Child3
QHusb2Child1 , QHusb2Child2, QHusb2Child3
These are the new field names:
Child1, Child2, Child3 ... Child10
If there is a name in the old field then we copy it to a new field. Not every couple has a child together. They could each have children from previous marriages. Keeping that in mind Child1 doesn't always = QWife1Child1. Child1 could = QHusb2Child1.
I wrote the query to complete this task and I believe that it ran like I wanted. I just need to make sure.
I need to write a MSSQL query to make sure that every child from the old fields are listed in the new fields. The query I wrote to do this is giving me syntax errors.
How can I change this query to get the data I need?
These are the old field names:
QWife1Child1 , QWife1Child2, QWife1Child3
QWife2Child1 , QWife2Child2, QWife2Child3
QHusb1Child1 , QHusb1Child2, QHusb1Child3
QHusb2Child1 , QHusb2Child2, QHusb2Child3
These are the new field names:
Child1, Child2, Child3 ... Child10
If there is a name in the old field then we copy it to a new field. Not every couple has a child together. They could each have children from previous marriages. Keeping that in mind Child1 doesn't always = QWife1Child1. Child1 could = QHusb2Child1.
I wrote the query to complete this task and I believe that it ran like I wanted. I just need to make sure.
I need to write a MSSQL query to make sure that every child from the old fields are listed in the new fields. The query I wrote to do this is giving me syntax errors.
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
How can I change this query to get the data I need?
SELECT q.MattersQTRUSTINFO,
QWife1Child1 , QWife1Child2, QWife1Child3
QWife2Child1 , QWife2Child2, QWife2Child3
QHusb1Child1 , QHusb1Child2, QHusb1Child3
QHusb2Child1 , QHusb2Child2, QHusb2Child3
FROM [MattersQTRUSTINFO] q
JOIN Matters m ON m.matters = q.Matters
WHERE QWife1Child1 NOT IN (SELECT qchild1, QCHILD2, QCHILD3, QCHILD4, QCHILD5, QCHILD6, QCHILD7, QCHILD8, QCHILD9, QCHILD10 FROM [MattersQTRUSTINFO] q1 WHERE q1.Matters = q.Matters)
OR QWife1Child2 NOT IN (SELECT qchild1, QCHILD2, QCHILD3, QCHILD4, QCHILD5, QCHILD6, QCHILD7, QCHILD8, QCHILD9, QCHILD10 FROM [MattersQTRUSTINFO] q1 WHERE q1.Matters = q.Matters)ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I closed this out too quickly. I am not getting the correct results.
The reason I changed the 'or' to 'and' is because when my results came back there were a lot of them. I went into a few records and things matched. There was nothing wrong. That's when I changed the 'or' to 'and'. Again I did random spot checks and I found two records that had data in QHusb1Child1 and Qhusb1Child2 but nothing in Qchild1 or Qchild2. These are the two records I am looking for.
Any other ideas?
The reason I changed the 'or' to 'and' is because when my results came back there were a lot of them. I went into a few records and things matched. There was nothing wrong. That's when I changed the 'or' to 'and'. Again I did random spot checks and I found two records that had data in QHusb1Child1 and Qhusb1Child2 but nothing in Qchild1 or Qchild2. These are the two records I am looking for.
Any other ideas?
Is the WHERE clause you posted above complete? If so, you need to expand it to handle the QHusb#Child# fields. If you did expand it, post the complete WHERE clause.
ASKER
I tried it both ways with 'or' and 'and'.
WHERE
(
(ISNULL(RTRIM(QWife1Child1), '') <> '' AND (QWife1Child1 <> QChild1 AND QWife1Child1 <> QChild2 AND QWife1Child1 <> QChild3 AND QWife1Child1 <> QChild4
AND QWife1Child1 <> QCHILD5 AND QWIFE1CHILD1 <> QCHILD6 AND QWIFE1CHILD1 <> QCHILD7 AND QWIFE1CHILD1 <> QCHILD8 AND QWIFE1CHILD1 <> QCHILD9 AND QWife1Child1 <> QChild10))
OR
(ISNULL(RTRIM(QHUSB1CHILD1), '') <> '' AND (QHUSB1CHILD1 <> QChild1 OR QHUSB1CHILD1 <> QChild2 OR QHUSB1CHILD1 <> QChild3 OR QHUSB1CHILD1 <> QChild4
OR QHUSB1CHILD1 <> QCHILD5 OR QHUSB1CHILD1 <> QCHILD6 OR QHUSB1CHILD1 <> QCHILD7 OR QHUSB1CHILD1 <> QCHILD8 OR QHUSB1CHILD1 <> QCHILD9 OR QHUSB1CHILD1 <> QChild10))
OR
(ISNULL(RTRIM(QHUSB1CHILD2), '') <> '' AND (QHUSB1CHILD2 <> QChild1 AND QHUSB1CHILD2 <> QChild2 AND QHUSB1CHILD2 <> QChild3 AND QHUSB1CHILD2 <> QChild4
AND QHUSB1CHILD2 <> QCHILD5 AND QHUSB1CHILD2 <> QCHILD6 AND QHUSB1CHILD2 <> QCHILD7 AND QHUSB1CHILD2 <> QCHILD8 AND QHUSB1CHILD2 <> QCHILD9 AND QHUSB1CHILD2 <> QChild10)))
The part of your WHERE clause for QHUSB1CHILD1 did not have the OR changed to AND.
You mention that QHUSB1CHILD1 / QHUSB1CHILD2 was not found in QChild1 / QChild2. Could those values exist in one of the other columns and therefore being eliminated from the query?
You mention that QHUSB1CHILD1 / QHUSB1CHILD2 was not found in QChild1 / QChild2. Could those values exist in one of the other columns and therefore being eliminated from the query?
ASKER
You are correct that I did not change the OR to AND in the one I had posted. I was trying all different scenarios and none of them gave me what I wanted.
I just looked in the table and nothing is in any of the child fields except in QHUSB1CHILD1 / QHUSB1CHILD2.
I just looked in the table and nothing is in any of the child fields except in QHUSB1CHILD1 / QHUSB1CHILD2.
Could the process that moved the data have skipped these rows?
ASKER
I think what happened is that the user inputted the data in the old fields after we did the move but I am not sure. We did the move a few weeks ago.
The problem is that that row doesn't come up when checking.
The problem is that that row doesn't come up when checking.
ASKER
We use a program called ProLaw. The fields on the form are directly linked to the DB. If we add or delete fields on the form they are added or deleted from the DB. We wanted to give the users a chance to look at the new fields before we changed it for good. We told everyone not to use the old fields but I think someone did. Or possibly the move query I ran didn't work. I don't think that is the case since it did work correctly on over 1,000 records.
This WHERE clause may do what you want:
WHERE (ISNULL(RTRIM(QWife1Child1), '') <> '' OR ISNULL(RTRIM(QWife1Child2), '') <> '' OR ISNULL(RTRIM(QWife1Child3), '') <> ''
OR ISNULL(RTRIM(QWife2Child1), '') <> '' OR ISNULL(RTRIM(QWife2Child2), '') <> '' OR ISNULL(RTRIM(QWife2Child3), '') <> ''
OR ISNULL(RTRIM(QHusb1Child1), '') <> '' OR ISNULL(RTRIM(QHusb1Child2), '') <> '' OR ISNULL(RTRIM(QHusb1Child3), '') <> ''
OR ISNULL(RTRIM(QHusb2Child1), '') <> '' OR ISNULL(RTRIM(QHusb2Child2), '') <> '' OR ISNULL(RTRIM(QHusb2Child3), '') <> '')
AND (ISNULL(RTRIM(QChild1), '') = '' AND ISNULL(RTRIM(QChild2), '') = '' AND ISNULL(RTRIM(QChild3), '') = '' AND ISNULL(RTRIM(QChild4), '') = ''
AND ISNULL(RTRIM(QChild5), '') = '' AND ISNULL(RTRIM(QChild6), '') = '' AND ISNULL(RTRIM(QChild7), '') = '' AND ISNULL(RTRIM(QChild8), '') = ''
AND ISNULL(RTRIM(QChild9), '') = '' AND ISNULL(RTRIM(QChild10), '') = '')
ASKER
WHERE
(ISNULL(RTRIM(QWife1Child1
AND QWife1Child1 <> QCHILD5 AND QWIFE1CHILD1 <> QCHILD6 AND QWIFE1CHILD1 <> QCHILD7 AND QWIFE1CHILD1 <> QCHILD8 AND QWIFE1CHILD1 <> QCHILD9 AND QWife1Child1 <> QChild10))
OR
(ISNULL(RTRIM(QWife1Child2
AND QWIFE1CHILD2 <> QCHILD5 AND QWIFE1CHILD2 <> QCHILD6 AND QWIFE1CHILD2 <> QCHILD7 AND QWIFE1CHILD2 <> QCHILD8 AND QWIFE1CHILD2 <> QCHILD9 AND QWIFE1CHILD2 <> QChild10))