Get data from one query that dosen't exist in another

Hi experts,

I effectively have 2 queries which I am trying to make into 1, I  am trying to get the trayNos that exist in the first query
IE 1 2 3 4   but do not appear in the second query, so I am looking for the query to return TrayNos 2 3 4.  I hope this makes sense.


select TrayNo
from Session, Count
where (Session.Session_ID = Count.Session_ID)           /* 1 2 3 4 */

select   TrayNo
from     Session  
where    (NOT EXISTS
                             (select   Session_ID
                               from    Count
                               where  (Session.Session_ID = Count.Session_ID)))   /* 1 1 1 */
dizzycatAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
SELECT TrayNo FROM Session
EXCEPT
SELECT TrayNo FROM Count

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nathan RileyFounderCommented:
select TrayNo
from Session
where TrayNo not in (
select TrayNo
from count
)

Open in new window

0
dizzycatAuthor Commented:
This will not work because the TrayNo field does not exist in the Count table.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

Open in new window

0
Mike EghtebasDatabase and Application DeveloperCommented:
What fields exist in each table?
0
dizzycatAuthor Commented:
The above returns 1 2 3 4 1 1 1 which are the results from the 2 seperate queries  together
0
Nathan RileyFounderCommented:
Yeah if you would show a output of both table structures I could modify it to pull the needed data.
0
dizzycatAuthor Commented:
The Session table has the fields:

Session_ID
TrayNo
SessionDate

The Count table has the fields:

Session_ID
CountDate
Total
0
Nathan RileyFounderCommented:
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
)

Open in new window

0
dizzycatAuthor Commented:
The above returns nothing
0
Mike EghtebasDatabase and Application DeveloperCommented:
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?
0
dizzycatAuthor Commented:
The 1234 and the 111 are TrayNo's which is a field that exists in the session table only and not the Count table.
0
magarityCommented:
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
0
Nathan RileyFounderCommented:
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

Open in new window

0
Mike EghtebasDatabase and Application DeveloperCommented:
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.
0
dizzycatAuthor Commented:
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.
0
dizzycatAuthor Commented:
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.
0
Mike EghtebasDatabase and Application DeveloperCommented:
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.
0
dizzycatAuthor Commented:
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
0
PortletPaulfreelancerCommented:
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
;

Open in new window


EXCEPT
"Returns distinct rows by comparing the results of two queries."
https://msdn.microsoft.com/en-us/library/ms188055.aspx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.