[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Sql select statement inner join

Posted on 2016-07-27
4
Medium Priority
?
66 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
  • 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

865 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