Home » RDBMS Server » Server Administration » How to stop null fields from filling with data
How to stop null fields from filling with data [message #374013] Sat, 19 May 2001 11:46 Go to next message
Chris Rhine
Messages: 2
Registered: May 2001
Junior Member
I have 4 tables Person, Contact, Student and Company
I want to list all of the Persons that live in the state 'PA' (Contact and Student both have a foreign key "PersonID" which is also their primary key). Also, if the contact has a company associated with them, I want it to display that company name. However, when I attempt to combine all of the people, Oracle fills ALL of thecompany names next to EACH student. This returns a crapload of results. I would like this column to show up blank for the student but I can't figure out how. Is there any command which works like this?
THis is my current query:
select distinct p.lname, c.name
from person p, company c, contact ca, student s
where state = 'PA' AND c.companyID = ca.companyID AND ca.personID = p.personID OR s.personID = p.personID;

Any help is appreciated.

Report message to a moderator

Re: How to stop null fields from filling with data [message #374014 is a reply to message #374013] Sat, 19 May 2001 11:51 Go to previous messageGo to next message
Chris Rhine
Messages: 2
Registered: May 2001
Junior Member
Mistake: The field is not null, it just doesn't exist in the student table. However, in the sql statement above, it puts data next to it in the results

Report message to a moderator

Re: How to stop null fields from filling with data [message #374030 is a reply to message #374013] Mon, 21 May 2001 17:37 Go to previous messageGo to next message
Sundar Venkatasubramaniam
Messages: 26
Registered: May 2001
Junior Member
Please post us the structure of all the tables with examples.

Report message to a moderator

Re: How to stop null fields from filling with data [message #374032 is a reply to message #374013] Mon, 21 May 2001 23:16 Go to previous messageGo to next message
Sachin Patil
Messages: 7
Registered: May 2001
Junior Member
While creating table use check constrint.

e.g.

create table test(col1 datatype constraint constraint_name check(col1 is null));

Report message to a moderator

Re: How to stop null fields from filling with data [message #374033 is a reply to message #374013] Mon, 21 May 2001 23:16 Go to previous messageGo to next message
Sachin Patil
Messages: 7
Registered: May 2001
Junior Member
While creating table use check constrint.

e.g.

create table test(col1 datatype constraint constraint_name check(col1 is null));

Report message to a moderator

Re: How to stop null fields from filling with data [message #374034 is a reply to message #374013] Mon, 21 May 2001 23:16 Go to previous messageGo to next message
Sachin Patil
Messages: 7
Registered: May 2001
Junior Member
While creating table use check constrint.

e.g.

create table test(col1 datatype constraint constraint_name check(col1 is null));

Report message to a moderator

Re: How to stop null fields from filling with data [message #374035 is a reply to message #374013] Mon, 21 May 2001 23:16 Go to previous message
Sachin Patil
Messages: 7
Registered: May 2001
Junior Member
While creating table use check constrint.

e.g.

create table test(col1 datatype constraint constraint_name check(col1 is null));

Report message to a moderator

Previous Topic: how to write a correct sql sentence.
Next Topic: How to query this question?
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Jul 03 13:15:46 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.