How to get the null and non null records from the table?
Hi,
I have one table sample_table. It contains the records like below. This is just sample table
ID PAR_ID NAME SURNAME
100 1000 SURI
200 1000 RAJ XXX
300 1000 SURIYA
The above sample table contains some sort of records. I want to get the records if the surname column is not null i should return that not null row for that particular PAR_ID. If the SURNAME column is null, I should return any one of the row from table for that particular PAR_ID. For example the above table contains the XXX record in SURNAME column. So that column does not contain null value. So i need to return that XXX row from the table for that particular PAR_ID. But if that XXX is not avaiable, all the rows are null for that column, i need to return any of the row from that table for particular PAR_ID.
For this we have to write SQL query. Can anyone share with me if you get something.
Thanks
I have one table sample_table. It contains the records like below. This is just sample table
ID PAR_ID NAME SURNAME
100 1000 SURI
200 1000 RAJ XXX
300 1000 SURIYA
The above sample table contains some sort of records. I want to get the records if the surname column is not null i should return that not null row for that particular PAR_ID. If the SURNAME column is null, I should return any one of the row from table for that particular PAR_ID. For example the above table contains the XXX record in SURNAME column. So that column does not contain null value. So i need to return that XXX row from the table for that particular PAR_ID. But if that XXX is not avaiable, all the rows are null for that column, i need to return any of the row from that table for particular PAR_ID.
For this we have to write SQL query. Can anyone share with me if you get something.
Thanks
The idea to return exactly one row from first or second condition:
Oracle syntax:
SELECT * FROM
(SELECT * FROM sample_table WHERE SURNAME IS NOT NULL AND ROWNUM <= 1
union
SELECT * FROM sample_table WHERE SURNAME IS NULL AND ROWNUM <= 1
)
WHERE ROWNUM <= 1
MS SQL Syntax:
SELECT TOP 1 * FROM
(SELECT TOP 1 * FROM sample_table WHERE SURNAME IS NOT NULL
union
SELECT TOP 1 * FROM sample_table WHERE SURNAME IS NULL
)
Oracle syntax:
SELECT * FROM
(SELECT * FROM sample_table WHERE SURNAME IS NOT NULL AND ROWNUM <= 1
union
SELECT * FROM sample_table WHERE SURNAME IS NULL AND ROWNUM <= 1
)
WHERE ROWNUM <= 1
MS SQL Syntax:
SELECT TOP 1 * FROM
(SELECT TOP 1 * FROM sample_table WHERE SURNAME IS NOT NULL
union
SELECT TOP 1 * FROM sample_table WHERE SURNAME IS NULL
)
and add the " AND PAR_ID = ... " to the first and second subquery...
ASKER
This is not homework for you mike. This is our thoughts you know. I was thinking this since from the morning. But this problem will be really interesting you know.
Well, I'm sceptic because I have recently seen similar requests.
ASKER
hI ALEX, Thanks for your reply. We have to use this in where condition. For example
SELECT SURNAME
FROM SAMPLE_TABLE
WHERE PAR_ID = 100
AND ( )
Based on this where condition SURNAME will return value if it contains. If it is not return any value we have to consider as null.
SELECT SURNAME
FROM SAMPLE_TABLE
WHERE PAR_ID = 100
AND ( )
Based on this where condition SURNAME will return value if it contains. If it is not return any value we have to consider as null.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Good One
And you want us to do this homework for you?