Complicated join query checking 2 different rows

This may help you on your way for a solution.

status = 14 AND date_done IS NULL
OR
(status = 14 AND date_done IS NOT NULL
OR
status = 15 AND date_requested IS NOT NULL)
OR status NOT IN (14,15)

SELECT si.* -- not sure which fields you want to return in the query
FROM salesitem si
INNER JOIN status st
  ON si.current_status int = st.id
WHERE ((st.status = 14 AND st.date_done IS NULL
  OR (st.status = 14 and st.date_done IS NOT NULL
    OR st.status = 15 AND st.date_requested IS NOT NULL)
  OR st.status NOT IN (14,15))

Select allOpen in new window

I am a bit confused about your criteria stated in #2.
"2.  status = 14 and datedone is not null and  a row exists with  status=15 and date_requested is not null"

Are you treating this as an OR statement? Such as criteria 14 OR criteria 15?
If not, please explain so that I can adjust the code to meet your needs.

"2.  status = 14 and datedone is not null and  a row exists with  status=15 and date_requested is not null"

This means I want to pull all salesitems where a row in the status table for the salesitem has a status=14 and datedone is not null and a row in the status table for the same salesitem has a status=15 and date_requested is not null

reconsidering this - sorry - will post a new one

There is some missing information needed to satisfy your requirement I believe.

You indicate the one salesitem can have multiple rows in status:
"and  a row exists with  status=15 and date_requested is not null"

However, you may have only supplied part of the table definitions. e.g. [salesitem]
a primary key (unique) and current_status which is a foreign key to another unique id

so, with that limited knowledge, one row in salesitem can only have one current_status

[salesitem]
id current_status
1  102

[status]
id  status date_requested date_done onhold
101  14    2013-07-01       null     null
102  15    2013-07-02       null     null

there is no way to locate record 101 in [status]
UNLESS there are some other fields we don't know about.

Please provide the complete table definition of both tables and some sample data.

e.g. this is a complete guess, but it may help:

SELECT
       si.id
       
     , si.ORDERNO -- this is an assumed field!
                  -- and something of this nature is REQUIRED
     
     -- more columns as required
     
FROM [salesitem] AS si
INNER JOIN [status] AS st ON si.current_status = st.id
WHERE (
		st.status = 14
		AND st.date_done IS NULL
		)
	OR (
         (
            st.status = 14
            AND st.date_done IS NOT NULL
         )
        AND EXISTS (
                    SELECT 1
                    FROM salesitem xsi
                    INNER JOIN STATUS xst ON xsi.current_status = xst.id
-- NB                    
                    WHERE si.ORDERNO = xsi.ORDERNO -- enables finding the "other row"
-- NB                    
                    AND xst.status = 15
                    AND xst.date_requested IS NOT NULL
                    )
		)
	OR (st.STATUS NOT IN (14, 15))

Select allOpen in new window

You are right I missed a column in status called salesitem.

Table Name (salesitem):

id int(10)  primary key,
current_status int(2),  "(id column from status table)"


Table Name (status):
 
id int(10) primary key,
status int(2) ,
salesitem int(10) "id column from salesitem table"
date_requested date,
date_done date,
onhold int(1)

I will provide sample data on the morrow when I get to work.

It might help if you could provide some relevant sample data for the two tables and what your expected output should be that we could use to test.