CODEWITHSUNDEEP
sqloracle-database
DanH
DanH

Reputation: 61

SQL Statement with 3 select statements

I am trying to combine the data of three tables but running into a minor issue.

Let's say we have 3 tables

Table A

ID | ID2 | ID3 | Name | Age
 1    2x    4y   John    23
 2    7j         Mike    27
 3    1S1   6HH  Steve   67
 4    45    O8   Carol   56

Table B

 | ID2 | ID3  | Price
    2x    4y      23
    7j    8uj     27
    x4    Q6      56

Table C

|ID | Weight|
  1    145
  1    210
  1    240
  2    234
  2    110
  3    260
  3    210
  4    82

I want to get every record from table A of everyone who weighs 200 or more but they cannot be in table B. Table A and C are joined by ID. Table A and B are joined by either ID2 or ID3. ID2 and ID3 don't both have to necessarily be populated but at least 1 will. Either can be present or both and they will be unique. So expected result is

3 | 1S1 | 6HH | Steve| 67

Note that a person can have multiple weights but as long as at least one record is 200 or above they get pulled.

What I have so far

Select *
From tableA x 
Where 
    x.id in (Select distinct y.id
             From tableA y, tableC z
             Where y.id = z.id 
               And z.weight >= '200' 
               And y.id not in (Select distinct h.id
                                From tableA h, tableB k
                                Where (h.id2 = k.id2 or h.id3 = k.id3)))

When I do this it seems to ignore the check on tableB and I get John, Mike and Steve. Any ideas? Sorry it's convoluted, this is what I have to work with. I am doing this in oracle by the way.

Upvotes: 0

Views: 129

Answers (4)

Dan P
Dan P

Reputation: 1999

Here's what I came up with.

SELECT DISTINCT
  A.ID,
  A.ID2,
  A.ID3,
  A.Name,
  A.Age
FROM 
                  A
  LEFT OUTER JOIN C ON C.ID = A.ID
  LEFT OUTER JOIN B ON 
       B.ID2 = A.ID2 
   OR  B.ID3 = A.ID3    
WHERE
      C.Weight >= 200
   AND B.Price IS NULL

BELOW is test data

CREATE TABLE A
(
  ID INT,
  ID2 VARCHAR(3),
  ID3 VARCHAR(3),
  Name VARCHAR(10),
  Age INT
);

INSERT INTO A VALUES (1, '2x', '4y', 'John', 23);
INSERT INTO A VALUES (2, '7j', NULL , 'Mike', 27);
INSERT INTO A VALUES (3, '1S1', '6HH', 'Steve', 67);
INSERT INTO A VALUES (4, '45', 'O8', 'Carol', 56);

CREATE TABLE B
(
  ID2 VARCHAR(3),
  ID3 VARCHAR(3),
  Price INT
 );

 INSERT INTO B VALUES ('2x', '4y', 23);
 INSERT INTO B VALUES ('7j', '8uj', 27);
 INSERT INTO B VALUES ('x4', 'Q6', 56);

 CREATE TABLE C
 (
   ID INT,
   Weight INT
 );

 INSERT INTO C VALUES (1, 145);
 INSERT INTO C VALUES (1, 210);
 INSERT INTO C VALUES (1, 240);
 INSERT INTO C VALUES (2, 234);
 INSERT INTO C VALUES (2, 110);
 INSERT INTO C VALUES (3, 260);
 INSERT INTO C VALUES (3, 210);
 INSERT INTO C VALUES (4, 82);

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271023

This sounds like exists and not exists. So a direct translation is:

select a.*
from tableA a
where exists (select 1 from tableC c where c.id = a.id and c.weight >= 200) and
      not exists (select 1 from tableB b where b.id2 = a.id2 or b.id3 = a.id3);

Splitting the or into two separate subqueries can often improve performance:

select a.*
from tableA a
where exists (select 1 from tableC c where c.id = a.id and c.weight >= 200) and
      not exists (select 1 from tableB b where b.id2 = a.id2) and
      not exists (select 1 from tableB b where b.id3 = a.id3);

Upvotes: 2

unleashed
unleashed

Reputation: 771

I was beating to the answers, but I used INNER JOIN on tables a and c and a NOT EXISTS on table b.

--This first section is creating the test data
with Table_A (id, id2, id3, Name, age) as
    (select 1,    '2x',    '4y',   'John',    23 from dual union all
    select 2,    '7j',    null,     'Mike',    27 from dual union all
    select 3,    '1S1',   '6HH',  'Steve',  67 from dual union all
    select 4,    '45',    'O8',   'Carol',   56 from dual),
Table_B(id2, id3, price) as
    (select    '2x',    '4y',      23 from dual union all
    select    '7j',    '8uj',     27 from dual union all
    select    'x4',    'Q6',      56 from dual),
Table_C(id, weight) as
    (select  1,    145 from dual union all
    select  1,    210 from dual union all
    select  1,    240 from dual union all
    select  2,    234 from dual union all
    select  2,    110 from dual union all
    select  3,    260 from dual union all
    select  3,    210 from dual union all
    select  4,    82 from dual)
--Actual query starts here
select distinct a.*
from   table_a a
  --join to table c, include the weight filter
  inner join table_c c on (a.id = c.id and c.weight >= 200)    
where not exists  -- The rest is the NOT EXISTS to exclude the values in table b
  (select 1 from table_b b
   where a.id2 = b.id2
      or a.id3 = b.id3);

Upvotes: 0

hisnameismyname2
hisnameismyname2

Reputation: 387

Select a.id, a.id2, a.id3
From table_a a
Left join table_c c on a.id = c.id
Where c.weight >=200
      And not exists
      (Select 1
         From table_b b
       Where a.id = b.id2
              Or a.id = b.id3
       );

Upvotes: 0

Related Questions