CODEWITHSUNDEEP
sqlsql-serversql-server-2005
lancscoder
lancscoder

Reputation: 8768

SQL in-line equivalent to intersect

Is there an inline sql command that is similar to or produces the same results as intersect? If not is there a way to rewrite the query so it is in one query rather than using intersect?

My current sql query is:

SELECT 
  c.Log_Link 
FROM 
  Classes c INNER JOIN 
  ClassValues cv ON c.Class_Link = cv.Class_Link INNER JOIN 
  ClassSelection cs ON cv.ClassSelection_Link = cs.ClassSelection_Link
WHERE 
  cs.classselection_link IN (95,1)

But what I'm really after is:

SELECT 
  c.Log_Link 
FROM 
  Classes c INNER JOIN 
  ClassValues cv ON c.Class_Link = cv.Class_Link INNER JOIN 
  ClassSelection cs ON cv.ClassSelection_Link = cs.ClassSelection_Link
WHERE 
  cs.classselection_link = 95 
INTERSECT 
SELECT 
  c.Log_Link 
FROM 
  Classes c INNER JOIN 
  ClassValues cv ON c.Class_Link = cv.Class_Link INNER JOIN 
  ClassSelection cs ON cv.ClassSelection_Link = cs.ClassSelection_Link
WHERE 
  cs.classselection_link = 1

Thanks

Upvotes: 2

Views: 556

Answers (4)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115540

You can always do:

SELECT DISTINCT
  c.Log_Link 
FROM 
  Classes c INNER JOIN 
  ClassValues cv1 ON c.Class_Link = cv1.Class_Link INNER JOIN 
  ClassSelection cs1 ON cv1.ClassSelection_Link = cs1.ClassSelection_Link

INNER JOIN
  ClassValues cv2 ON c.Class_Link = cv2.Class_Link INNER JOIN 
  ClassSelection cs2 ON cv2.ClassSelection_Link = cs2.ClassSelection_Link

WHERE 
    cs1.classselection_link = 95 
  AND
    cs2.classselection_link = 1

or with GROUP BY:

SELECT 
  c.Log_Link 
FROM 
  Classes c INNER JOIN 
  ClassValues cv ON c.Class_Link = cv.Class_Link INNER JOIN 
  ClassSelection cs ON cv.ClassSelection_Link = cs.ClassSelection_Link
WHERE 
  cs.classselection_link IN (95,1)
GROUP BY
  c.Class_Link
HAVING
  COUNT(c.Class_Link) = 2

and since you don't really need the ClassSelection table:

SELECT 
  c.Log_Link 
FROM 
  Classes c INNER JOIN 
  ClassValues cv ON c.Class_Link = cv.Class_Link
WHERE 
  cv.ClassSelection_Link IN (95,1)
GROUP BY
  c.Class_Link
HAVING
  COUNT(c.Class_Link) = 2

As gbn pointed, I assumed that (Class_Link, ClassSelection_Link) is UNIQUE in table ClassValues.

If that is not true, then the HAVING clause at the last 2 queries should be changed to : 

HAVING
  COUNT(DISTINCT cv.ClassSelection_Link) = 2

Upvotes: 2

gbn
gbn

Reputation: 432271

INTERSECT and EXISTS are the same semantically. And you don't need so many tables

SELECT 
  c.Log_Link 
FROM 
  Classes c
WHERE 
  EXISTS (
      SELECT * FROM ClassValues cv
      WHERE c.Class_Link = cv.Class_Link AND cv.classselection_link = 95)
AND
  EXISTS (
      SELECT * FROM ClassValues cv
      WHERE c.Class_Link = cv.Class_Link AND cv.classselection_link = 1)

Upvotes: 2

Pete M
Pete M

Reputation: 2048

I'm pretty sure you just want distinct. You're trying to get a unique set of c.Log_Link that matches either criteria, correct? If so:

SELECT DISTINCT
  c.Log_Link 
FROM 
  Classes c INNER JOIN 
  ClassValues cv ON c.Class_Link = cv.Class_Link INNER JOIN 
  ClassSelection cs ON cv.ClassSelection_Link = cs.ClassSelection_Link
WHERE 
  cs.classselection_link IN (95,1)

Edit: I see your clarification. I think joining derived tables as amit_g suggested would be the way to go.

I'd tweak it just a bit though:

SELECT 
  c.Log_Link 
FROM 
  Classes c INNER JOIN 
  ClassValues cv ON c.Class_Link = cv.Class_Link INNER JOIN 
  ClassSelection cs ON cv.ClassSelection_Link = cs.ClassSelection_Link
  INNER JOIN (
        SELECT 
          c.Log_Link 
        FROM 
          Classes c INNER JOIN 
          ClassValues cv ON c.Class_Link = cv.Class_Link INNER JOIN 
          ClassSelection cs ON cv.ClassSelection_Link = cs.ClassSelection_Link
        WHERE 
          cs.classselection_link = 1
    ) T2 ON c.Log_Link = T2.Log_Link
WHERE 
  cs.classselection_link = 95

Upvotes: 1

amit_g
amit_g

Reputation: 31250

May be using inner join...

SELECT
    *
FROM
    (
        SELECT 
          c.Log_Link 
        FROM 
          Classes c INNER JOIN 
          ClassValues cv ON c.Class_Link = cv.Class_Link INNER JOIN 
          ClassSelection cs ON cv.ClassSelection_Link = cs.ClassSelection_Link
        WHERE 
          cs.classselection_link = 95 
    ) T1
INNER JOIN
    (
        SELECT 
          c.Log_Link 
        FROM 
          Classes c INNER JOIN 
          ClassValues cv ON c.Class_Link = cv.Class_Link INNER JOIN 
          ClassSelection cs ON cv.ClassSelection_Link = cs.ClassSelection_Link
        WHERE 
          cs.classselection_link = 1
    ) T2
ON
    T1.Log_Link = T2.Log_Link

Upvotes: 2

Related Questions