?
Solved

Sql select statement inner join

Posted on 2016-07-27
4
Medium Priority
?
61 Views
Last Modified: 2016-07-27
I want to return all value that match the following criteria.

Return  all value from column A that are identical   where we have one row in Column B value is  'Export B' and Column C = 0.0  also a another from that a value from Column B is Export  and Column C is 10.00

The result should be 21282

Column A--- Column B ---Column C
21282       ---      Export  --- 10.0
21282       ---      Export b  --- 0.0
21281       ---      Export  --- 10.0
0
Comment
Question by:yguyon28
[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
  • 2
4 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 41732051
there will be several syntaxes to solve this, and depending on the real final requested output adifferent basic solution can be chosen.
select t.fieldA
 from yourtable t
 where t.fieldB = 'export'
   and t.fieldC = 0
 and exists( select null
   from yourtable o
   where o.fieldA = t.fieldA
     and o.fieldB = 'export b'
      and o.fieldC = 10
)

i may have mistyped some values, I presume you got the point of my suggestion
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41732108
I think you can achieve it using group by and having clauses like this:
Select ColumnA
From table1
Where (ColumnB='Export' And ColumnC = 10.0)
OR
(ColumnB='Export b' and ColumnC =0.0)
Group by ColumnA
HAVING COUNT(*) = 2

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 41732110
Not sure if I understand your problem correctly. Is this what you are looking for?
SELECT *
  FROM your_table
 WHERE (ColumnB = 'Export B' AND ColumnC = 0)
    OR (ColumnB = 'Export' AND ColumnC = 10)

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 41732118
Well. Based on Paul's reply, If there are ColumnA values duplicated for first or second condition.
SELECT ColumnA
  FROM your_table
 WHERE (ColumnB = 'Export B' AND ColumnC = 0)
    OR (ColumnB = 'Export' AND ColumnC = 10)
 GROUP BY ColumnA
HAVING COUNT(DISTINCT CASE WHEN ColumnB = 'Export B' AND ColumnC = 0 THEN 1 WHEN ColumnB = 'Export' AND ColumnC = 10 THEN 2 END) = 2

Open in new window

0

Featured Post

RHCE - Red Hat OpenStack Prep Course

This course will provide in-depth training so that students who currently hold the EX200 & EX210 certifications can sit for the EX310 exam. Students will learn how to deploy & manage a full Red Hat environment with Ceph block storage, & integrate Ceph into other OpenStack service

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses
Course of the Month14 days, 20 hours left to enroll

770 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