[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 113
  • 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

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