Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 110
  • Last Modified:

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 */
0
dizzycat
Asked:
dizzycat
  • 8
  • 5
  • 5
  • +2
1 Solution
 
Mike EghtebasDatabase and Application DeveloperCommented:
SELECT TrayNo FROM Session
EXCEPT
SELECT TrayNo FROM Count

Open in new window

0
 
Nathan RileyFounder/CTOCommented:
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
Nathan RileyFounder/CTOCommented:
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 RileyFounder/CTOCommented:
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 RileyFounder/CTOCommented:
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 RileyFounder/CTOCommented:
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
 
PortletPaulCommented:
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 8
  • 5
  • 5
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now