Link to home
Start Free TrialLog in
Avatar of Stacie
StacieFlag for United States of America

asked on

Sql select statement inner join

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
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

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

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