Minus / Intersect in MS SQL Server

Hi,

I've been trying to make a minus/intersect kind of sql sentence in MS SQL Server but haven't been able todo it yet. I know I should use something like NOT EXISTS or JOIN stuff, but the sql sentences I need to combind aren't just a simple "SELECT * FROM A WHERE NOT EXISTS IN B" type.

I need the data result from this sentence:

SELECT Deler.DeleID, Deler.Beskrivelse, Deler.Pris FROM Service, Våpen, Modell, Deler, DeleModellListe WHERE Service.ServiceID = ? AND Våpen.VåpenID = Service.VåpenID AND Modell.ModellID = Våpen.ModellID AND DeleModellListe.ModellID = Modell.ModellID AND Deler.DeleID = DeleModellListe.DeleID

Subtracted with this data result:

SELECT Deler.DeleID, Deler.Beskrivelse, Deler.Pris FROM Service, Våpen, Modell, Deler, ServiceMal, DeleMalListe WHERE Service.ServiceID = ? AND Våpen.VåpenID = Service.VåpenID AND Modell.ModellID = Våpen.ModellID AND ServiceMal.ModellID = Modell.ModellID AND ServiceMal.Skudd = Våpen.Innkallingsskudd AND DeleMalListe.ServiceMalID = ServiceMal.ServiceMalID AND Deler.DeleID = DeleMalListe.DeleID

The current sentences I've tried have either returned 0 rows or just an infinite amount :p

In advance, thanks for the help :)
Kritor_123Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
>SELECT * FROM A WHERE NOT EXISTS IN B

2 versions:
SELECT * FROM A WHERE NOT EXISTS (SELECT 1 FROM B WHERE B.Field = A.Field )
SELECT * FROM A LEFT JOIN B ON A.Field = B.Field WHERE B.Field IS NULL
Kritor_123Author Commented:
Thanks for the prompt reply, but the sentence "SELECT * FROM A WHERE NOT EXISTS IN B" was just an example of what kind of sentence I didn't have in this case :)

As I'm using alot of AND's I havent been able to make one that works yet for the 2 big sentences I got.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you might find this interesting:

declare @t table (DeleID int, Beskrivelse int,  Pris int )
insert into @t
SELECT Deler.DeleID, Deler.Beskrivelse, Deler.Pris FROM Service, Våpen, Modell, Deler, ServiceMal, DeleMalListe WHERE Service.ServiceID = ? AND Våpen.VåpenID = Service.VåpenID AND Modell.ModellID = Våpen.ModellID AND ServiceMal.ModellID = Modell.ModellID AND ServiceMal.Skudd = Våpen.Innkallingsskudd AND DeleMalListe.ServiceMalID = ServiceMal.ServiceMalID AND Deler.DeleID = DeleMalListe.DeleID

select a.*
from (
SELECT Deler.DeleID, Deler.Beskrivelse, Deler.Pris FROM Service, Våpen, Modell, Deler, DeleModellListe WHERE Service.ServiceID = ? AND Våpen.VåpenID = Service.VåpenID AND Modell.ModellID = Våpen.ModellID AND DeleModellListe.ModellID = Modell.ModellID AND Deler.DeleID = DeleModellListe.DeleID
) a
left join @t t
on t.DeleID = a.DeleID
and t.Beskrivelse = a.Beskrivelse
and t.Pris  = a.Pris
where t.DeleID  is null
and t.Beskrivelse is null
and t.Pris  is null



Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Scott PletcherSenior DBACommented:
SELECT Deler.DeleID, Deler.Beskrivelse, Deler.Pris
FROM Service, Våpen, Modell, Deler, ServiceMal, DeleMalListe
LEFT OUTER JOIN (
    SELECT Deler.DeleID, Deler.Beskrivelse, Deler.Pris
    FROM Service, Våpen, Modell, Deler, DeleModellListe
    WHERE Service.ServiceID = ? AND Våpen.VåpenID = Service.VåpenID AND Modell.ModellID = Våpen.ModellID AND DeleModellListe.ModellID = Modell.ModellID AND Deler.DeleID = DeleModellListe.DeleID
) AS t2 ON t2.DeleID = Deler.DeleID AND t2.Beskrivelse = Deler.Beskrivelse AND t2.Pris = Deler.Pris
WHERE t2.DeleID IS NULL AND
Service.ServiceID = ? AND Våpen.VåpenID = Service.VåpenID AND Modell.ModellID = Våpen.ModellID AND ServiceMal.ModellID = Modell.ModellID AND ServiceMal.Skudd = Våpen.Innkallingsskudd AND DeleMalListe.ServiceMalID = ServiceMal.ServiceMalID AND Deler.DeleID = DeleMalListe.DeleID
Kritor_123Author Commented:
Hi ScottPletcher,

I tried the sentence but got this error message from Visual Studio:

{"The multi-part identifier "Deler.Pris" could not be bound. The multi-part identifier "Deler.Beskrivelse" could not be bound. The multi-part identifier "Deler.DeleID" could not be bound."}

Trying to fix it myself but havent been able to yet :)

By the way, I got this sentence to work perfectly, but I can only use it to get Deler.DeleID as thats the only variable in Deler thats integer, and VS complained that DISTINCT only worked with integers:

SELECT DISTINCT Deler.DeleID
FROM Service, Våpen, Modell, Deler, DeleModellListe
WHERE Service.ServiceID = ?
AND Våpen.VåpenID = Service.VåpenID
AND Modell.ModellID = Våpen.ModellID
AND DeleModellListe.ModellID = Modell.ModellID
AND Deler.DeleID = DeleModellListe.DeleID
AND Deler.DeleID NOT IN
(SELECT Deler.DeleID FROM Service, Våpen, Modell, Deler, ServiceMal, DeleMalListe WHERE Service.ServiceID = ? AND Våpen.VåpenID = Service.VåpenID AND Modell.ModellID = Våpen.ModellID AND ServiceMal.ModellID = Modell.ModellID AND ServiceMal.Skudd = Våpen.Innkallingsskudd AND DeleMalListe.ServiceMalID = ServiceMal.ServiceMalID AND Deler.DeleID = DeleMalListe.DeleID)
Scott PletcherSenior DBACommented:
Hmm, the names in the  inner and outer queries are being shared, maybe that's "confusing" to SQL:


LEFT OUTER JOIN (
    SELECT Deler2.DeleID, Deler2.Beskrivelse, Deler2.Pris
    FROM Service Service2, Våpen Våpen2, Modell Modell, Deler Deler2, DeleModellListe DeleModellListe2
    WHERE Service2.ServiceID = ? AND Våpen2.VåpenID = Service2.VåpenID AND Modell2.ModellID = Våpen2.ModellID AND DeleModellListe2.ModellID = Modell2.ModellID AND Deler2.DeleID = DeleModellListe2.DeleID
)
Kritor_123Author Commented:
Tried the new sentence now, but it complained about some more now hehe:

{"The multi-part identifier \"Deler.Pris\" could not be bound.\r\nThe multi-part identifier \"Deler.Beskrivelse\" could not be bound.\r\nThe multi-part identifier \"Deler.DeleID\" could not be bound.\r\nThe multi-part identifier \"Modell2.ModellID\" could not be bound.\r\nThe multi-part identifier \"Modell2.ModellID\" could not be bound."}

But I think VS usually re-writes the sentences into its "own way", but havent experienced that it has actually ruined the sentence though. Here is the rewritten version by the way, maybe it shows why VS doesnt understand it:

SELECT     Deler.DeleID, Deler.Beskrivelse, Deler.Pris
FROM         Service INNER JOIN
                      Våpen ON Service.VåpenID = Våpen.VåpenID INNER JOIN
                      Modell AS Modell_1 ON Våpen.ModellID = Modell_1.ModellID INNER JOIN
                      ServiceMal ON Modell_1.ModellID = ServiceMal.ModellID AND Våpen.Innkallingsskudd = ServiceMal.Skudd INNER JOIN
                      DeleMalListe LEFT OUTER JOIN
                          (SELECT     Deler2.DeleID, Deler2.Beskrivelse, Deler2.Pris
                            FROM          DeleModellListe AS DeleModellListe2 INNER JOIN
                                                   Deler AS Deler2 ON DeleModellListe2.DeleID = Deler2.DeleID CROSS JOIN
                                                   Service AS Service2 INNER JOIN
                                                   Våpen AS Våpen2 ON Service2.VåpenID = Våpen2.VåpenID CROSS JOIN
                                                   Modell AS Modell
                            WHERE      (Service2.ServiceID = ?) AND (Modell2.ModellID = Våpen2.ModellID) AND (DeleModellListe2.ModellID = Modell2.ModellID)) AS t2 ON
                      t2.DeleID = Deler.DeleID AND t2.Beskrivelse = Deler.Beskrivelse AND t2.Pris = Deler.Pris ON
                      ServiceMal.ServiceMalID = DeleMalListe.ServiceMalID INNER JOIN
                      Deler ON DeleMalListe.DeleID = Deler.DeleID
WHERE     (t2.DeleID IS NULL) AND (Service.ServiceID = ?)
Scott PletcherSenior DBACommented:
Please try this:


SELECT     Deler.DeleID, Deler.Beskrivelse, Deler.Pris
FROM         Service INNER JOIN
                      Våpen ON Service.VåpenID = Våpen.VåpenID INNER JOIN
                      Modell AS Modell_1 ON Våpen.ModellID = Modell_1.ModellID INNER JOIN
                      ServiceMal ON Modell_1.ModellID = ServiceMal.ModellID AND Våpen.Innkallingsskudd = ServiceMal.Skudd INNER JOIN
                      DeleMalListe ON ServiceMal.ServiceMalID = DeleMalListe.ServiceMalID INNER JOIN
                      Deler ON DeleMalListe.DeleID = Deler.DeleID
LEFT OUTER JOIN
                          (SELECT     Deler2.DeleID, Deler2.Beskrivelse, Deler2.Pris
                            FROM          DeleModellListe AS DeleModellListe2 INNER JOIN
                                                   Deler AS Deler2 ON DeleModellListe2.DeleID = Deler2.DeleID CROSS JOIN
                                                   Service AS Service2 INNER JOIN
                                                   Våpen AS Våpen2 ON Service2.VåpenID = Våpen2.VåpenID CROSS JOIN
                                                   Modell AS Modell2
                            WHERE      (Service2.ServiceID = ?) AND (Modell2.ModellID = Våpen2.ModellID) AND (DeleModellListe2.ModellID = Modell2.ModellID)) AS t2 ON
                      t2.DeleID = Deler.DeleID AND t2.Beskrivelse = Deler.Beskrivelse AND t2.Pris = Deler.Pris
WHERE     (t2.DeleID IS NULL) AND (Service.ServiceID = ?)
Kritor_123Author Commented:
VS gave this error message:

{"The data types text and text are incompatible in the equal to operator."}

VS is hard to please ;)
Scott PletcherSenior DBACommented:
Sorry, I have no way of knowing which columns are text.  You will need to research that yourself.  You will either need to CAST() the text to strings, such as:
CAST(textCol as VARCHAR(5000)) = CAST(textCol2 AS VARCHAR(5000))
or just drop them from the comparison.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kritor_123Author Commented:
No problem, I'm sure I will be able to solve this on my own with all your great tips, and will award you the points :) If not I'll just use the DISTINCT sentence I made and just use a for() loop to get the other info on each DeleID or something.

Thanks for the help!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.