OraFAQ Forum: SQL & PL/SQL » Query to find all records with either same value or null value

Home » SQL & PL/SQL » SQL & PL/SQL » Query to find all records with either same value or null value

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query to find all records with either same value or null value [message #648549]

Thu, 25 February 2016 04:16

ranugupta3
Messages: 2
Registered: February 2016

Junior Member

I Need to find all records from a table of zip code having zip_code, City and Country code
Now my requirement is to find all records with same zip_code . At the same time the City of These zip_codes can be either all same or null.

my sample data is

zip_code zip_city
1234 abc
1234 abc
1234 null
1567 def
1567 fgh
3456 efg
3456 efg

Output desired :
zip_code zip_city
1234 abc

Report message to a moderator

Re: Query to find all records with either same value or null value [message #648551 is a reply to message #648549]

Thu, 25 February 2016 05:10

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

We don't do homework but we can help you, so post what you have already tried and explain where you are stuck.

Hints: GROUP BY, COUNT will help you.

[Updated on: Thu, 25 February 2016 05:11]

Report message to a moderator

Re: Query to find all records with either same value or null value [message #648553 is a reply to message #648551]

Thu, 25 February 2016 05:59

ranugupta3
Messages: 2
Registered: February 2016

Junior Member

My Problem : it gives records with same zip_code and zip_city also. I want only those records with same code and City either same or null. But in one record it has to be null.
For example in my sample data above. my code Piece with return "3456 efg" also but thats not my requirement.

Below is my code Piece :
SELECT zz.zip_zip
,zz.zip_city
,zz.id_seq_zip
FROM tzip zz
WHERE zz.zip_zip IN (SELECT a.zip_zip
FROM (SELECT z.zip_zip
,z.zip_city
,COUNT(z.zip_zip)
FROM tzip z
GROUP BY z.zip_zip
,z.zip_city
HAVING(COUNT(*) > 1)) a)
ORDER BY 1

[Updated on: Thu, 25 February 2016 06:00]

Report message to a moderator

Re: Query to find all records with either same value or null value [message #648554 is a reply to message #648553]

Thu, 25 February 2016 06:09

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Michel Cadot wrote on Thu, 25 February 2016 12:10

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

We don't do homework but we can help you, so post what you have already tried and explain where you are stuck.

Hints: GROUP BY, COUNT will help you.

Report message to a moderator

Re: Query to find all records with either same value or null value [message #648558 is a reply to message #648554]

Thu, 25 February 2016 07:32

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Received in PM:

ranugupta3 wrote on Thu, 25 February 2016 13:47

Hallo Michel,
I dont understand what more Information should i post for my question.
I can't give the create table Syntax as it might be a breach to my Company policy.

Also i gave my code Piece. what more is needed from my end?

Thanks & Regards,
Ranu

Where is the breach in your company policy to give a CREATE TABLE for a table with zip_code and zip_city?
Where is the breach in your company policy to give INSERT statements for 1234, abc and so on?

The other information needed are in my posts.
And FORMAT your posts, this can't be a breach in your company policy.

Report message to a moderator

Re: Query to find all records with either same value or null value [message #648561 is a reply to message #648558]

Thu, 25 February 2016 08:36

varlamovvp
Messages: 13
Registered: February 2016
Location: Russia

Junior Member

hint:

 more readable code

SELECT
   zz.*
FROM tzip zz
WHERE zz.zip_zip IN (
  SELECT z.zip_zip
   FROM tzip z
   WHERE z.zip_city IS NULL
   )
  AND zz.zip_city IS NOT NULL
ORDER BY 1

Report message to a moderator

Previous Topic:	update statement using from clause
Next Topic:	composite data type

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jun 30 16:32:09 CDT 2024