We help IT Professionals succeed at work.

SQL

Diono
Diono asked
on
Hi All,

I have a simple master-detail setup. Some rows in the master don't have corresponding detail. I need to get a result dataset which will contain a distinct result set containing all records from the master(whether they have detail or not), but with some kind of calculated field indicating which of those records have detail and which not. The reason I need this is to display a list of all members, and at the same time indicate(via a colour) all members in my app which don't have detail. Oh, by the way I am looking for the SQL.
 
Thanks a lot,
Dion.  
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2004

Commented:
what database?

in oracle

select  distinct a.*,  Decode(b.id,NULL,'No Detail','With Detail') from tablemaster a, tabledeteil b where a.id = b.id(+)

meikl ;-)
for SQL server ->

select a.*,
       case
         when ((select count(*) from detailtable where id = a.id) is null) then
           'No Detail'
         else
           'With Detail'
       end  as detailstatus

from mastertable a


this should work... I use similar constructs in my work
Igor UL7AAjrSenior developer

Commented:
Hi Diono,
meikl ;-)

madterfield - some field from mater table
detailfield - some field from detail table

SQL statement:

select masterfield,  count(detailfield)
from mastertable left outer join detailtable
on mastertable.id = detailtable.id
group by masterfield

-----
Igor
Igor UL7AAjrSenior developer

Commented:
typo:
masterfield - some field from master table

Author

Commented:
Interbase guys.

Cheers
CERTIFIED EXPERT
Top Expert 2004

Commented:
well then igors comment should work

if you want all feild from master just use this modification

select masterfieldlist,  count(detailfield)
from mastertable left outer join detailtable
on mastertable.id = detailtable.id
group by masterfieldlist

whereas masterfieldlist is field1,field2,field3,...
with the real fieldnames, of course

meikl ;-)

Author

Commented:
Problem is, one of the fields I need in the fieldslist is a blob.

Author

Commented:
Phoenix_s, I like your solution, although IB uses IF THEN instead. I take it your solution is stored procedure based.

Author

Commented:
I need to update one of the tables(the master), so I think that a storewd procedure is out.IBO will allow me to do this.

Author

Commented:
What I mean by IBO will allow me to do this, is that I can insert using a Query object.
Diono, nope, standard transact SQL for SQL server.

Commented:
HI, Diono,
If I understood you correctly, you don't need to enter
any text in empty fields (without details).
You only need to color them that's all.
So, write the ordinary SQL statement using
"left outer join", and in the Event OnDrawColumnCell of your DBGrid cahnge the Brush.Color of the Canvas,
when the corresponding field is NULL.
Sincerely,
Nestorua.
Diono:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
CERTIFIED EXPERT

Commented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

accept Phoenix_s's comment as answer

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

Thanks,

geobul
EE Cleanup Volunteer

Explore More ContentExplore courses, solutions, and other research materials related to this topic.