sql statement select what cant be joined

Hi,

I have 2 tables:


**** TableA ****

NameA    valueA
-------  --------
john     10
mary     20
jack     30
tom       5

Open in new window


*** TableB ****

NameB    valueB
-------  --------
Sarah    10
Thomas   30
Ive      9

Open in new window


I know how to join this tables and get the ones that have the same value, I do it this way and get the following data:

select * from tableA inner join tableB on tableA.valueA = tableB.valueB

John 10 Sarah 10
Jack 30 Thomas 30

Open in new window


Questions:

Q1. How do I get only the ones present in table A, in this example:
   
   
Tom 5
    Mary 20

Open in new window


Q2. How do I get only the ones present in table B, in this example:

   
Ive 9

Open in new window

jsbxAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
This query will show you the full combination -- both, or either one -- and which table, or both, had data:


SELECT
    COALESCE(a.nameA, b.nameb) AS Name,
    COALESCE(a.valueA, b.valueB) AS Value,
    CASE
        WHEN a.nameA IS NULL THEN 'Only in B'  --or 'Missing from A'
        WHEN a.nameB IS NULL THEN 'Only in A'  --or 'Missing from B'
        ELSE 'In both A and B' END  --or 'Not missing in either'
        AS In_Which_Table
FROM tableA a
FULL OUTER JOIN tableB b ON
    b.nameB = a.nameA
ORDER BY Name
0
 
Steve WalesConnect With a Mentor Senior Database AdministratorCommented:
There are several ways to get this data, have a read of this article: How to find rows that are in one table, but not another which shows you several ways to do it.

You can use NOT IN, NOT EXISTS, a LEFT JOIN checking for NULL values or the EXCEPT operator (for SQL Server)
0
 
Philip PortnoySr. MS SQL DBA and Technical Account ManagerCommented:
Do you have a unique value in the tables? If you do then it's very easy.
Let's assume your name is a unique value.

For these values from tableA:
select * from tableA where name NOT IN (select tableA.name from tableA inner join tableB on tableA.valueA = tableB.valueB) AND name NOT IN (select tableB.name from tableA inner join tableB on tableA.valueA = tableB.valueB)

Open in new window


For values from tableB:
select * from tableB where name NOT IN (select tableA.name from tableA inner join tableB on tableA.valueA = tableB.valueB) AND name NOT IN (select tableB.name from tableA inner join tableB on tableA.valueA = tableB.valueB)

Open in new window


Please note that the fact that I don't know your database structure and what's the data in there this code is really "dirty" and might require optimization.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
jsbxAuthor Commented:
testing ...
0
 
awking00Commented:
As pointed out there are severla ways to do this. Here is one -
For Q1
select * from tableA
where valueA in
(select valueA from tableA except select valueA from tableB)

For Q2
select * from tableb
where valueb in
(select valueB from tableB except select valueA from tableA)
0
 
awking00Commented:
And another way -
select * from TableA as a
where not exists
(select 1 from tableB as b
 where a.valueA = b.valueB)

select * from TableB as b
where not exists
(select 1 from tableA as a
 where a.valueA = b.valueB)
0
 
jsbxAuthor Commented:
Thanks to all.
jsbx
0
 
PortletPaulCommented:
Was this homework? I'm surprised no-one asked.

I'm also surprised no-one offered this: the visual chart available here is very useful
http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins
0
 
jsbxAuthor Commented:
No it wasn't.
I just simplified the question in order to avoid misunderstandings since english is not my native lang.
Jsbx
0
 
jsbxAuthor Commented:
Portlet paul
Anyway the article you pointed is remarkable.  Thanks.
0
All Courses

From novice to tech pro — start learning today.