SQL Server – Rewrite INTERSECT Query as JOIN

join;sql server

I have two queries written in PHP against a Microsoft SQL Server database (I know this is weird).

I'm querying one table.

I want the records shared by both results sets ONLY.

Here is query #1:

SELECT     ORDER_NO
FROM         X_INVOIC
WHERE     (ITEM_CODE = 'DONE')

Here is query #2:

SELECT     ORDER_NO
FROM         X_INVOIC
WHERE     (ITEM_CODE = 'LAPMACPRO15.4')

The results of both queries have some of the same ORDER_NOs between them.

Example Results Set #1:

101
102
103

Example Results Set #2:

105
102
106

My query should only return "102", since it is common in both results sets.

I simply want to compare the results and show a list of ORDER_NOs that are in both results sets. An INTERSECT query would work perfectly for this, but I get this error message when trying to test my query in the SQL Server Manager:

The INTERSECT SQL construct or statement is not supported.

Is there a way to do this with INNER JOINS?

I have tried the following already, which involved joining the table to itself as an alias, but it returned no results.

select a.ORDER_NO,
       b.ORDER_NO
  from X_INVOIC a
  join X_INVOIC b on b.INVOICES_ID = a.INVOICES_ID
where a.ITEM_CODE =  'LAPMACPRO15.4'
  and b.ITEM_CODE = 'DONE'

Thanks.

Best Answer

Yes, it's possible, The query you already tried was on the right lines but had the wrong join condition (b.INVOICES_ID = a.INVOICES_ID should be b.ORDER_NO = a.ORDER_NO)

try this

SELECT DISTINCT a.order_no
FROM   x_invoic a
       INNER JOIN x_invoic b
         ON a.order_no = b.order_no
WHERE  a.item_code = 'DONE'
       AND b.item_code = 'LAPMACPRO15.4'
Related Question