Solved

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

Posted on 2015-02-16
20
95 Views
Last Modified: 2015-02-16
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 */
0
Comment
Question by:dizzycat
  • 8
  • 5
  • 5
  • +2
20 Comments
 
LVL 33

Accepted Solution

by:
Mike Eghtebas earned 500 total points
ID: 40612812
SELECT TrayNo FROM Session
EXCEPT
SELECT TrayNo FROM Count

Open in new window

0
 
LVL 11

Expert Comment

by:N R
ID: 40612816
select TrayNo
from Session
where TrayNo not in (
select TrayNo
from count
)

Open in new window

0
 

Author Comment

by:dizzycat
ID: 40612817
This will not work because the TrayNo field does not exist in the Count table.
0
 
LVL 11

Expert Comment

by:N R
ID: 40612820
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
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40612829
What fields exist in each table?
0
 

Author Comment

by:dizzycat
ID: 40612830
The above returns 1 2 3 4 1 1 1 which are the results from the 2 seperate queries  together
0
 
LVL 11

Expert Comment

by:N R
ID: 40612834
Yeah if you would show a output of both table structures I could modify it to pull the needed data.
0
 

Author Comment

by:dizzycat
ID: 40612835
The Session table has the fields:

Session_ID
TrayNo
SessionDate

The Count table has the fields:

Session_ID
CountDate
Total
0
 
LVL 11

Expert Comment

by:N R
ID: 40612838
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
 

Author Comment

by:dizzycat
ID: 40612846
The above returns nothing
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40612869
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
 

Author Comment

by:dizzycat
ID: 40612878
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
 
LVL 13

Expert Comment

by:magarity
ID: 40612880
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
 
LVL 11

Expert Comment

by:N R
ID: 40612885
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
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40612889
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
 

Author Comment

by:dizzycat
ID: 40612900
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
 

Author Comment

by:dizzycat
ID: 40612934
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
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40612960
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
 

Author Comment

by:dizzycat
ID: 40612963
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40613123
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now