Get data from one query that dosen&#x27;t exist in another

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

select TrayNo
from Session
where TrayNo not in (
select TrayNo
from count
)

ASKER

This will not work because the TrayNo field does not exist in the Count table.

select TrayNo
from Session s
where TrayNo not in (
select Session_ID
from count c
where c.session_id = s.session_id
)

What fields exist in each table?

ASKER

The above returns 1 2 3 4 1 1 1 which are the results from the 2 seperate queries together

Yeah if you would show a output of both table structures I could modify it to pull the needed data.

ASKER

The Session table has the fields:

Session_ID
TrayNo
SessionDate

The Count table has the fields:

Session_ID
CountDate
Total

Also what does this return?

select TrayNo
from Session s
where TrayNo in (
select Session_ID
from count c
where c.session_id = s.session_id
)

ASKER

The above returns nothing

Question 1: /* 1 2 3 4 */ in Session table are values from TrayNo or Session_ID?
Question 2: /* 1 1 1 */ in Count table are values from What field?

ASKER

The 1234 and the 111 are TrayNo's which is a field that exists in the session table only and not the Count table.

magarity

Isn't this a problem for outer join?

SELECT TrayNo
FROM Session LEFT OUTER JOIN COUNT ON Session.Session_ID = COUNT.Session_ID
WHERE COUNT.Session_ID IS NULL

Ah ok makes sense now, you'll need an outer join then to accomplish.

select trayno
from session s
left outer join count c on s.session_id = c.session_id
where c.session_id is null

dizzycat,

Which is correct:
1234 <-- value in one column
or
1 2 3 4 <-- values in 4 ccolumns.

They are not the same.

Could you please paste sample data here. The explanation is not clear but very confusing.

ASKER

Both the above solutions are returning 111
I am looking for 234, these would be the tray numbers which are empty and can be used in another session.

ASKER

Sorry eghtebas I do not understand what you are doing, as for sample data I have already given the queries and
the data returned in my original question.

Table Session
==============================
Session_ID TrayNo
-------------- -------------
100 1
200 2
300 3
3400 4

Table Count
==============================
Session_ID ? field names
-------------- -------------
100 x
200 x
300 x
3400 x

You know exactly what you have but we don't know what exactly you have. Fill in above table a bit.

ASKER

I think I have managed to answer my own question, the below query returns TrayNo's 234

SELECT TrayNo
FROM Session LEFT OUTER JOIN COUNT ON Session.Session_ID = COUNT.Session_ID
WHERE COUNT.Session_ID IS NOT NULL
Except
SELECT TrayNo
FROM Session LEFT OUTER JOIN COUNT ON Session.Session_ID = COUNT.Session_ID
WHERE COUNT.Session_ID IS NULL

I will award the points to you eghtebas as you alerted me to the EXCEPT key word

PortletPaul

I think you might get the same results from the following

SELECT DISTINCT TrayNo
FROM   Session INNER JOIN COUNT ON Session.Session_ID = COUNT.Session_ID
;