Diono
asked on
SQL
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
typo:
masterfield - some field from master table
masterfield - some field from master table
ASKER
Interbase guys.
Cheers
Cheers
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 ;-)
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 ;-)
ASKER
Problem is, one of the fields I need in the fieldslist is a blob.
ASKER
Phoenix_s, I like your solution, although IB uses IF THEN instead. I take it your solution is stored procedure based.
ASKER
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.
ASKER
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.
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.
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.
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.
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
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
in oracle
select distinct a.*, Decode(b.id,NULL,'No Detail','With Detail') from tablemaster a, tabledeteil b where a.id = b.id(+)
meikl ;-)