• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1746
  • Last Modified:

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 :)
0
Kritor_123
Asked:
Kritor_123
  • 5
  • 4
  • 2
1 Solution
 
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
0
 
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.
0
 
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



0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
0
 
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)
0
 
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
)
0
 
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 = ?)
0
 
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 = ?)
0
 
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 ;)
0
 
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.
0
 
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!
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now