Hi There,
I'm kind a stuck at this query and I need just a bit of suggstion.
I've 3 tables CRM, ESCR and Orchard. All 3 table has exact columns. i need a statment where it result matches to CRM and ESCR but not to Orchard. I'have tried building a temp table and do it works for all except 3-4 records and I can't afford to have them. I know I might not be clear but if it makes any sense eplease help! below is result of 1 table, other two table will have similar result, I just want a logical test when it matches cRM and ESCR but not in orchard
INTERNAL_ID ID USN VERSION
28 f1/28 28 2
29 f1/29 28 3
30 f1/30 28 3
SELECT Col1, Col2, ...
FROM CRM AS C
JOIN ESCR AS E
ON E.SomeID = C.SomeID
AND E.OtherID = C.OtherID
WHERE NOT EXISTS
(
SELECT *
FROM Orchard AS O
WHERE O.SomeID = C.SomeID
AND O.OtherID = C.OtherID
)
Thanks Kristen, Works like a charm. I looked up and got another help
Intersect and except function.
I never used them before but I like your idea, apperciate it a lot.
If there is only a one-to-one relationship with the [Orchard] table you could use an OUTER JOIN instead
SELECT Col1, Col2, ...
FROM CRM AS C
JOIN ESCR AS E
ON E.SomeID = C.SomeID
AND E.OtherID = C.OtherID
LEFT OUTER JOIN Orchard AS O
ON O.SomeID = C.SomeID
AND O.OtherID = C.OtherID
WHERE O.SomeID IS NULL
I prefer this as we often want to fiddle about (in DEV at least) Including / Excluding the rows in [Orchard] to see what is getting joined to etc etc. So it is easy enough to change
WHERE O.SomeID IS NULL
to
WHERE O.SomeID IS NOT NULL
or just leave the WHERE clause out.
But its probably a bit of an "alien" way of doing things, compared to the more obvious-to-understand EXISTS construction.
If [Orchard] has multiple rows for a single row in the other tables then that would cause multiple rows to be returned in the resultset, even though you are not including any columns from [Orchard] in the SELECT, so that would definitely be a reason to use EXISTS rather than an OUTER JOIN 
Yeah, outer join is not going to work in this case due to multiple row. My datasets are quite messed up..I just find your way a bit easier then mine, the only think is I'm getting 4 records difference between your query and mine, so going to fgiure out.. Wonders of small datasets.. haha!
Thanks and appericate your help!
You might need INNER JOIN and SELECT * (or "more columns" at least) to see what is getting matched ... and then once debugged you can revert to NOT EXISTS
I'm not usiing Distinct,, now its similar rows, for some reason, I was getting a repeat entry. rubbish on street is cleaner then this datasets..
Worth trying to avoid using DISTINCT. SQL has to get all the rows, and then throw the duplicates away, so better to "program out" the cause of the duplicates, if you can.
Our main CRM application plus data from all sorts of sources. It logs any "Data Validation Errors" that the import routine regards as unreasonable (based on Rules we have agreed with the Client, and the Client's assumption that Other-Application-A does a "good job" of keeping its data clean. We typically have tens-of-thousands of validation errors when we hand our application over to the client ... it beggars belief, to me, that their other systems are allowed to exist given that, as you so rightly say, the streets are cleaner!!
1 Like