Solved

Sql select statement inner join

Posted on 2016-07-27
4
49 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 500 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 48

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 40

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 40

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

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.…
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…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

837 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