SQL Question

[Senrab_nhoj]

Need to return all rows in a database for an ID where at least one row for that ID has a non null value in specific attribute field...

[EC_Red]

Oracle Sql okay?

 

Person (pid, pname, pphone)

Orders (oid, pid_fk, o_dept, o_lock)

 

Select * from orders

Where pid_fk in (

Select distinct pid_fk from orders i where i.o_dept is not null)

You can also use where exists and then a sub query

[John am Rhein]

Select * from my-table

[DecidedByPoll]

I love that youse are doing this.

[Kite]

Need to return all rows in a database for an ID where at least one row for that ID has a non null value in specific attribute field...

Database or table?

 

Select * from orders

Where pid_fk in (

Select distinct pid_fk from orders i where i.o_dept is not null)

Huh?

[Cunny]

Select * from orders

Where pid_fk in (
Select pid_fk from orders where

(select pid_fk, count(*)

from orders

where attribute is not null

having count(*) > 0

group by pid_fk))

 

I think that should work*

 

 

*but I do have a bad hangover

[EC_Red]

Huh?

What did i miss?

 

Is attribute field not equal to column?

Edited February 6, 2016 by EC_Red

[Nebraska Red]

whatever happened to "reinstall java"?

[Kite]

What did i miss?

Is attribute field not equal to column?

I suspect backwards barnesy hasn't explained correctly but your sub query and query are essentially the same, it's just a not null search.

[Case]

Select * from mytable where ID = @id and attribute IS NOT NULL

 

Eh? No? Eh?

[John am Rhein]

does the string 'NULL' count as a non-null value?

[Gerry]

Select * from mytable where ID = @id and attribute IS NOT NULL

 

Eh? No? Eh?

 

Well, you'd need to put a distinct in there or group by to avoid duplicates, but yes, it would seem that simple unless there's something more complicated about the original problem.

 

does the string 'NULL' count as a non-null value?

 

Yes.

[John am Rhein]

if there are duplicates in the 'database' (i.e. table) then avoiding duplicates most likely breaks the requirement of returning all rows

[Gerry]

if there are duplicates in the 'database' (i.e. table) then avoiding duplicates most likely breaks the requirement of returning all rows

 

Well Mr Barnes has been entirely specific about the problem in hand so we're all guessing a bit. Actually reading the question again, I see he seems to want all rows rather than all IDs, so yes a sub-query is probably required.

 

So probably something like...

 

SELECT * FROM <table> WHERE ID IN (SELECT DISTINCT ID FROM <table> WHERE <column> IS NULL)

 

...which is more or less what EC_Red said previously.

[Kite]

Not null!

 

What exactly is the difference between what that returns and what the sub query, sans "distinct" (just *) returns.

[Nathan Explosion]

A smaller set of results to search (with the 'in') through.....

 

Select id from table could return this:

 

1

1

2

3

4

5

1

6

1

2

3

4

5

5

6

 

Select distinct id from table would return this:

 

1

2

3

4

5

6

[Gerry]

Not null!

 

What exactly is the difference between what that returns and what the sub query, sans "distinct" (just *) returns.

 

Yeah. Not Null.

 

That query will return ALL rows for all IDs where the ID has at least one not null column (i.e. it would return rows when the attribute column is null, as long as the same ID has another row where it's not null). The sub query by itself would only return the rows where the attribute is not null.

 

I think it's the former that's being asked for, but I admit it may not be.

[EC_Red]

I suspect backwards barnesy hasn't explained correctly but your sub query and query are essentially the same, it's just a not null search.

 

The sub query and the main query arent the same though.

 

Take a case with a db with the following meta -

create table person (
      pid number not null,
      pname varchar2(100) not null,
      pphone number,
      CONSTRAINT pid_pk PRIMARY KEY (pid)
);

create table orders (
      oid number not null,
      pid_fk number not null,
      odept  varchar2(100),
      CONSTRAINT oid_pk PRIMARY KEY (oid),
      CONSTRAINT fk_orders
    FOREIGN KEY (pid_fk)
    REFERENCES person(pid)
);

COMMIT

insert into person (pid, pname, pphone) values (1, 'Alice',1234567890);
insert into person (pid, pname, pphone) values (2, 'Bob',1234567890);
insert into person (pid, pname, pphone) values (3, 'Charlie',1234567890);
insert into person (pid, pname, pphone) values (4, 'Devon',1234567890);
insert into person (pid, pname, pphone) values (5, 'Elizabeth',1234567890);

insert into orders (oid, pid_fk, odept) values (1, 1, 'Accounting');
insert into orders (oid, pid_fk, odept) values (2, 1, 'Supplies');
insert into orders (oid, pid_fk, odept) values (3, 2, null);
insert into orders (oid, pid_fk, odept) values (4, 1, null);
insert into orders (oid, pid_fk, odept) values (5, 1, 'Misc');
insert into orders (oid, pid_fk, odept) values (6, 3, 'Accounting');
insert into orders (oid, pid_fk, odept) values (7, 3, 'Supplies');
insert into orders (oid, pid_fk, odept) values (8, 4, null);
insert into orders (oid, pid_fk, odept) values (9, 1, null);
insert into orders (oid, pid_fk, odept) values (10, 5, 'Misc');
insert into orders (oid, pid_fk, odept) values (11, 5, 'Accounting');
insert into orders (oid, pid_fk, odept) values (12, 4, 'Supplies');
insert into orders (oid, pid_fk, odept) values (13, 4, null);
insert into orders (oid, pid_fk, odept) values (14, 2, null);
insert into orders (oid, pid_fk, odept) values (15, 3, 'Misc');

COMMIT
/

Now, Select count(*) from orders where pid_fk in (select distinct pid_fk from orders i where i.odept is not null); - returns 13 rows.

 

But Select count(*) from orders where odept is not null; - only returns 9.

 

This is because the subquery in the first query returns all foreign key id's with at least 1 row with data in the 'odept' column. Now the main query returns all rows with id listed in the subquery even if the value in 'odept' is null. The second query just doesnt do that.

[cymrococh]

Godfather 2

Empire Strikes back

The Toy Story ones

[Jay M]

Select * from mytable where ID = @id and attribute IS NOT NULL

 

Eh? No? Eh?

I'd go with this.

[PhilM]

Not null!

 

What exactly is the difference between what that returns and what the sub query, sans "distinct" (just *) returns.

 

The subquery gives a list of all of the IDs which have a qualifying row in them (ie: with an attribute value which isn't null). You then use the main query part to show all of the rows for that ID.

 

If you don't use the subquery, you only show the rows without a null value in attribute, not all of the rows for that ID.

 

I'd do it the same as EC_Red and Gerry (obviously without the latter's typo!)

[Senrab_nhoj]

Ok, back from a relaxing weekend and where was the hammer solution, very disappointed..

 

Will take a gander and to clarify is a SQL server not Oracle and yes the data is in a table within the database, its an audit table and I want to pull out all trades that have

* At some point in time an attribute field set to NOT Null

* That attribute field on a later (or prob max) update is now Null

Edited February 8, 2016 by Senrab_nhoj

[Jay M]

Ok, back from a relaxing weekend and where was the hammer solution, very disappointed..

 

Will take a gander and to clarify is a SQL server not Oracle and yes the data is in a table within the database, its an audit table and I want to pull out all trades that have

* At some point in time an attribute field set to NOT Null

* That attribute field on a later (or prob max) update is now Null

 

SELECT *

FROM mytable a

WHERE a.id = &p_id

AND EXISTS

(SELECT 1

FROM mytable b

WHERE a.id = b.id

AND b.attribute IS NULL

AND b.pk_value > (SELECT MAX(c.pk_value)

FROM mytable c

WHERE a.id = c.id

AND c.attribute IS NOT NULL));

If you don't have a Primary Key on the table, replace 'pk_Value' with something like a created date.

This is based on my assumption:

* Bring back all the records in the table for the ID where at least one record has ATTRIBUTE NOT NULL and in a future record it is set to NULL.

* If the criteria is the last updated record has ATTRIBUTE IS NULL, you will need to change the EXISTS to be something like:

AND <pk_value> = (SELECT MAX(b.pk_value)...

--------------------------

Hope that's correct. Although I'm sure everyone is thinking the same thing, it's no use giving out part of the requirement! Otherwise we're all just guessing

[Senrab_nhoj]

Ok that makes a lot of sense working through the logic, not sure about what the &p_id as this is throwing a syntax error, apologies if this is a noddy question (note am running this via SQL man studio 2012)

 

Have replaced the pk_value with the Updated Date field

[Jay M]

Ok that makes a lot of sense working through the logic, not sure about what the &p_id as this is throwing a syntax error, apologies if this is a noddy question (note am running this via SQL man studio 2012)

 

Have replaced the pk_value with the Updated Date field

 

If running in PL/SQL Developer, when running the query I posted it would display a prompt to pass a value.

 

To test your script just replace &p_id with a hard-coded value (e.g 10) or an ID you know exists. You could also remove the line completely to check ALL records.

 

This is all Oracle SQL so the syntax may change slighly for a SQL Server Database