0

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.

1
  • 2
    If it's 'not supported' that suggests you're running SQL Server 2000. I'd highly suggest you consider upgrading. – Mark Sinkinson May 3 '15 at 9:07
3

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' 
1
  • 3
    Minor note: if (highly probable) there is a unique constraint on (otrder_no, item_code), then the query is 100% fine. If there isn't, you need select distinct as the intersect automatically removes duplicates. – ypercubeᵀᴹ May 3 '15 at 12:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.