[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2015-02-16
20
Medium Priority
?
107 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 5
  • +2
20 Comments
 
LVL 34

Accepted Solution

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

Open in new window

0
 
LVL 12

Expert Comment

by:Nathan Riley
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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 12

Expert Comment

by:Nathan Riley
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 34

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 12

Expert Comment

by:Nathan Riley
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 12

Expert Comment

by:Nathan Riley
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
 
LVL 34

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 12

Expert Comment

by:Nathan Riley
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 34

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 34

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 49

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
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…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

649 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