|
Home > Archive > dBASE Web Applications > October 2006 > Stuck on Select
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| Claus Mygind 2006-10-25, 7:25 am |
| This ought to be a simple task, but I don't seem to get it.
In the select statement I want to:
1) use 2 tables.
2)show every row of the master table even if there are no related child
records
3)limit the rowset to only one record per key. Do not include multiple rows
of the same master record if there is more than one related child record.
assume the following data set
Table 1 has 3 records
id = A
id = B
id = C
Table 2 has 4 recoreds
id = A , subId = 1
id = A, subId = 2
id = B, subId = 1
id = B, subId = 2
The resulting rowset should be:
id = A, subId = 1
id = B, subId = 1
id = C, subId = blank
How would I write that SQL statement?
select t.id, t2.subid from t1 left join t2 on t1.id=t2.id
This select statement would include all records from
table 2. How do I limit it to only include the first
matching record.
| |
| David Kerber 2006-10-25, 7:25 am |
| In article <TsTiAKK6GHA.1160@news-server>, cmygind@tsccorp.com says...
> This ought to be a simple task, but I don't seem to get it.
It's not, actually, because the database engine needs to know how to
define "first" as you use it below. Is it enough to grab the first
record it happens across, or does it need to be the one with the lower
subID?
It also needs some intermediate-level SQL to accomplish this (beginner
level generally won't give you what you want). Try this (UNTESTED!!):
select tab1.id, (select first subid from tab2 where tab2.id = tab1.id
order by subid) from tab1
order by tab1.id
If your tables are big, this is going to be slow unless you have indexes
on tab2.id and tab2.subid.
>
> In the select statement I want to:
> 1) use 2 tables.
> 2)show every row of the master table even if there are no related child
> records
> 3)limit the rowset to only one record per key. Do not include multiple rows
> of the same master record if there is more than one related child record.
>
> assume the following data set
> Table 1 has 3 records
> id = A
> id = B
> id = C
>
> Table 2 has 4 recoreds
> id = A , subId = 1
> id = A, subId = 2
> id = B, subId = 1
> id = B, subId = 2
>
> The resulting rowset should be:
> id = A, subId = 1
> id = B, subId = 1
> id = C, subId = blank
>
> How would I write that SQL statement?
>
> select t.id, t2.subid from t1 left join t2 on t1.id=t2.id
>
> This select statement would include all records from
> table 2. How do I limit it to only include the first
> matching record.
>
>
>
--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).
| |
| Ivar B. Jessen 2006-10-25, 7:25 am |
| On Thu, 5 Oct 2006 12:15:56 -0500, in dbase.internet,
Subject: Stuck on Select,
Message-ID: <TsTiAKK6GHA.1160@news-server>,
"Claus Mygind" <cmygind@tsccorp.com> wrote:
>This ought to be a simple task, but I don't seem to get it.
>
>In the select statement I want to:
>1) use 2 tables.
>2)show every row of the master table even if there are no related child
>records
>3)limit the rowset to only one record per key. Do not include multiple rows
>of the same master record if there is more than one related child record.
When using dbf tables you could do something like the code low my signature. For Firebird or MSSQL
table you may possibly be able to do it in a singe sql statement.
Ivar B. Jessen
//-----
if file("oneKey1.dbf")
drop table oneKey1
endif
if file("oneKey2.dbf")
drop table oneKey2
endif
create table onekey1(ID char(1))
insert into onekey1 values ("A")
insert into onekey1 values ("B")
insert into onekey1 values ("C")
create table oneKey2(ID char(1), subID char(1))
insert into onekey2 values ("A", "1")
insert into onekey2 values ("A", "2")
insert into onekey2 values ("B", "1")
insert into onekey2 values ("B", "2")
select a.ID, subID ;
from oneKey1 a ;
left outer join onekey2 b ;
on ( a.ID = b.ID ) ;
order by a.ID, subID ;
save to "oneKey3.dbf"
use onekey3 excl
index on ID tag ID unique
list
use
drop table oneKey3
close all
//------
| |
| Ivar B. Jessen 2006-10-25, 7:25 am |
| On Thu, 05 Oct 2006 23:50:19 +0200, in dbase.internet,
Subject: Re: Stuck on Select,
Message-ID: < l0vai2d2juon7fk3ui4l
ibj7bsma3hggg7@4ax.com>,
Ivar B. Jessen <bergishagen@it.notthis.dk> wrote:
>On Thu, 5 Oct 2006 12:15:56 -0500, in dbase.internet,
>Subject: Stuck on Select,
>Message-ID: <TsTiAKK6GHA.1160@news-server>,
>"Claus Mygind" <cmygind@tsccorp.com> wrote:
>
>
>When using dbf tables you could do something like the code low my signature. For Firebird or MSSQL
>table you may possibly be able to do it in a singe sql statement.
>
Hmm, it came out to be a simple task :-) Try the code below my signature.
Ivar B. Jessen
//-----
if file("oneKey1.dbf")
drop table oneKey1
endif
if file("oneKey2.dbf")
drop table oneKey2
endif
create table onekey1(ID char(1))
insert into onekey1 values ("A")
insert into onekey1 values ("B")
insert into onekey1 values ("C")
create table oneKey2(ID char(1), subID char(1))
insert into onekey2 values ("A", "1")
insert into onekey2 values ("A", "2")
insert into onekey2 values ("B", "1")
insert into onekey2 values ("B", "2")
select a.ID, min(subID) No1 ;
from oneKey1 a ;
left outer join onekey2 b ;
on ( a.ID = b.ID ) ;
group by a.ID
list
use
close all
//-----
| |
| David Kerber 2006-10-25, 7:25 am |
| In article < sntbi29niesuanubot91
ljjd21b6krgavp@4ax.com>,
bergishagen@it.notthis.dk says...
> On Thu, 05 Oct 2006 23:50:19 +0200, in dbase.internet,
> Subject: Re: Stuck on Select,
> Message-ID: < l0vai2d2juon7fk3ui4l
ibj7bsma3hggg7@4ax.com>,
> Ivar B. Jessen <bergishagen@it.notthis.dk> wrote:
>
>
>
> Hmm, it came out to be a simple task :-) Try the code below my signature.
Good job Ivar! Of course it gets much tougher if he wants more data
from the records in table 2 than just that single value.
--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).
| |
| Ivar B. Jessen 2006-10-25, 7:25 am |
| On Fri, 6 Oct 2006 07:49:00 -0400, in dbase.internet,
Subject: Re: Stuck on Select,
Message-ID: <MPG. 1f900cb86b6c80c49896
e3@news.dbase.com>,
David Kerber < ns_dkerber@ns_Warren
RogersAssociates.com> wrote:
>
>Good job Ivar!
Thanks.
>Of course it gets much tougher if he wants more data
>from the records in table 2 than just that single value.
Claus did not ask for more data <g>
But I see the problem, When a new field is added to table oneKey2 it must be part of the group and
in that case there will be more 'hits' than the number of records in table onekey1.
Would it solve the problem if all additional fields were kept in a linked table?
Ivar B. Jessen
| |
| David Kerber 2006-10-25, 7:25 am |
| In article < t0oci2l7hbltll7atmod
n2qlaqj70h9r37@4ax.com>,
bergishagen@it.notthis.dk says...
> On Fri, 6 Oct 2006 07:49:00 -0400, in dbase.internet,
> Subject: Re: Stuck on Select,
> Message-ID: <MPG. 1f900cb86b6c80c49896
e3@news.dbase.com>,
> David Kerber < ns_dkerber@ns_Warren
RogersAssociates.com> wrote:
>
>
>
> Thanks.
>
>
> Claus did not ask for more data <g>
Yes, but his posted example looked like a stripped-down sample to
illustrate the problem, rather than the actual problem he was trying to
solve.
> But I see the problem, When a new field is added to table oneKey2 it must be part of the group and
> in that case there will be more 'hits' than the number of records in table onekey1.
Yes. The brute-force way around this is to do a separate sub-select for
each field you want, which works for fairly small tables, but when they
get big they get to be very slow.
I have been beating my head against the keyboard for a couple of years
trying to come up with an efficient way of getting multiple fields from
a table row, when that row must be selected from multiple ones linked to
a single row in another table. In my case it's all based on timestamps
rather than ID codes, but the principle is the same. I wish the
database vendors would come up with something to answer that problem.
Some of them have some useful OLAP functions which help this, but I
haven't found the complete answer yet.
>
> Would it solve the problem if all additional fields were kept in a linked table?
I haven't thought it through in detail, but that might be the best
answer if his needs are significantly more complex than the posted
example.
--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).
| |
| Ivar B. Jessen 2006-10-25, 7:25 am |
| On Fri, 6 Oct 2006 14:51:26 -0400, in dbase.internet,
Subject: Re: Stuck on Select,
Message-ID: <MPG. 1f906fbcd51e22019896
e5@news.dbase.com>,
David Kerber < ns_dkerber@ns_Warren
RogersAssociates.com> wrote:
>
>
>Yes. The brute-force way around this is to do a separate sub-select for
>each field you want, which works for fairly small tables, but when they
>get big they get to be very slow.
I tried with two immediate tables and a UNION predicate. Try the code below my signature. I am
afraid it is slow with many records. It also assumes that oneKey2.subID only contains values > 0.
I believe it now works if the table has more fields ;-)
Ivar B. Jessen
//-----
clear
close all
if file("oneKey1.dbf")
drop table oneKey1
endif
if file("oneKey2.dbf")
drop table oneKey2
endif
if file("oneKey3.dbf")
drop table oneKey3
endif
if file("oneKey4.dbf")
drop table oneKey4
endif
create table onekey1(ID char(3))
insert into onekey1 values ("A")
insert into onekey1 values ("B")
insert into onekey1 values ("C")
use oneKey1
generate 7
use
create table oneKey2(ID char(3), subID integer, Name char(5))
insert into onekey2 values ("A", 1, "Alain")
insert into onekey2 values ("A", 2, "Art")
insert into onekey2 values ("B", 1, "Bill")
insert into onekey2 values ("B", 2, "Bob")
use oneKey2
generate 46
replace subID with null for subID == 0
use
select a.ID, min(subID) No1 ;
from oneKey1 a ;
left outer join onekey2 b ;
on ( a.ID = b.ID ) ;
group by a.ID ;
save to oneKey3
select * ;
from oneKey2 b ;
where exists ;
( select * from oneKey3 c ;
where (b.ID = c.ID and b.subID = c.No1) ) ;
order by b.ID ;
save to oneKey4
select d.ID,d.Name from oneKey4 d ;
union ;
select c.ID, cast("" as char(5)) from oneKey3 c ;
where c.No1 is null
list
use
close all
//-----
| |
| Claus Mygind 2006-10-25, 7:25 am |
| Ivar and David thanks for all the effort on this answer. I should have been
more specific in my original posting. I am trying to extract this from a
MySQL table not a dBase table. But from the discussion, I see that this is
not just a simple problem.
I actually have 3 tables. A Master and 2 child tables.
From the master table I want to use the basics Name, Address, City, St, Zip.
The key of course is the "ID". From each of the 2 child tables all I want
are two fields, the child's key and date.
example
Master table
ID
Name
etc......
Job table
ID
JobId
JobDate
Proposal table
ID
PropId
PropDate
All I want of course is just the last job and proposal associated with the
client if they exists
In my own mind I had come up with this solution; the use of a sub-query in
the select's from statement (not tested, not even sure if the syntax is
correct):
Select t1.id, t1.name..., t2.jobid, t2.jobdate, t3.propid, t3.propdate
From master t1
left join (select distinct j.id, j.jobId, j.jobDate
from job j
order by jobid desc) as t2
left join (select distinct p.id, p.propId, p.propDate
from prop p
order by propid desc) as t3
where t1.collection = 'T'
order by ID
Of course I see even if the syntax is not correct, I will still generate too
many rows since the modifier "distinct" applies to all fields named
collectively. Therefore two jobs with the same "jobId" but different
"jobDate" will each be viewed as distinct (unique).
I expect the total number of records that will be returned from the query on
the master file to be 100 or less. Therefore my ugliest workaround, will be
to extract the master records and then during the streaming out process to
the web page do a separate query on both the job and proposal tables, to get
the last record associated record.
The tables are not that big, but it was just annoying that the follwing
occured.
1) in the master table there are 60 records that qualify
2) when I link the job table I get about 560 records
3) then when I add the proposal table to the link it jumps to 8800+ records
in the extracted rowset.
| |
| Ivar B. Jessen 2006-10-25, 7:25 am |
| On Sun, 8 Oct 2006 19:12:39 -0500, in dbase.internet,
Subject: Re: Stuck on Select,
Message-ID: <8tEhSkz6GHA.1164@news-server>,
"Claus Mygind" <cmygind@yahoo.com> wrote:
>Ivar and David thanks for all the effort on this answer. I should have been
>more specific in my original posting. I am trying to extract this from a
>MySQL table not a dBase table. But from the discussion, I see that this is
>not just a simple problem.
>
>I actually have 3 tables. A Master and 2 child tables.
>From the master table I want to use the basics Name, Address, City, St, Zip.
>The key of course is the "ID". From each of the 2 child tables all I want
>are two fields, the child's key and date.
>
>example
>Master table
> ID
> Name
> etc......
>
>Job table
> ID
> JobId
> JobDate
>
>Proposal table
> ID
> PropId
> PropDate
>
>All I want of course is just the last job and proposal associated with the
>client if they exists
>
>In my own mind I had come up with this solution; the use of a sub-query in
>the select's from statement (not tested, not even sure if the syntax is
>correct):
It would be easier to test, both for you and for others, if you posted code to create the three
example tables in the same way I did in my demo, then listed the intended result. In this way you
would quickly see if your syntax was correct, no need to guess ;-)
Ivar B. Jessen
| |
| Claus Mygind 2006-10-25, 7:25 am |
| Ivar
Ok here is the code. The syntax is correct and it works (I have tested it),
just not the way I like it.
SELECT t1.id, t2.id, t2.jobid, t3.id, t3.propid
FROM master t1
left join (
SELECT distinct j.id, j.jobid, j.jobdate
FROM job j
order by jobid desc
)
as t2 on t1.id = t2.id
left join (
SELECT distinct p.id, p.propid, p.propdate
FROM prop p
order by propid desc
)
as t3 on t1.id = t3.id
where t1.collection = "T"
I am only returning one field from the master table for demo purposes. The
problem here is of the 20K client records only 60 qualify in the "where"
clause.
When I add the first subquery "t2" for the job table of 46K records the
returned rowset grows from 60 to 888.
Then when I add the 2nd subquery "t3" for the proposal table of 33K records
the returend rowset grows to 83,358.
So the question is, is there a way to limit the returned rowset to just 60
while having included the two fields from jobs (t2.jobId, t2.jobDate) and
the two fields from proposals (t3.propId, t3.propDate)?
You posed some interesting methods for .dbf tables which I need to test for
the MySQL tables.
| |
| Ivar B. Jessen 2006-10-25, 7:25 am |
| On Mon, 9 Oct 2006 07:48:32 -0500, in dbase.internet,
Subject: Re: Stuck on Select,
Message-ID: <#pLDVH66GHA.1168@news-server>,
"Claus Mygind" <cmygind@tsccorp.com> wrote:
>Ivar
>
>Ok here is the code. The syntax is correct and it works (I have tested it),
>just not the way I like it.
Where is the code to create and populate the tables and a list of hoped for results?
The code and the results are necessary for a demo program which can be copied from the message and
posted into the dBase IDE or whatever in order to run it and look for possible improvements.
>SELECT t1.id, t2.id, t2.jobid, t3.id, t3.propid
>FROM master t1
>left join (
> SELECT distinct j.id, j.jobid, j.jobdate
> FROM job j
> order by jobid desc
> )
>as t2 on t1.id = t2.id
>left join (
> SELECT distinct p.id, p.propid, p.propdate
> FROM prop p
> order by propid desc
> )
>as t3 on t1.id = t3.id
>where t1.collection = "T"
>
>I am only returning one field from the master table for demo purposes. The
>problem here is of the 20K client records only 60 qualify in the "where"
>clause.
>
>When I add the first subquery "t2" for the job table of 46K records the
>returned rowset grows from 60 to 888.
>
>Then when I add the 2nd subquery "t3" for the proposal table of 33K records
>the returend rowset grows to 83,358.
>
>So the question is, is there a way to limit the returned rowset to just 60
>while having included the two fields from jobs (t2.jobId, t2.jobDate) and
>the two fields from proposals (t3.propId, t3.propDate)?
My advice is to use small tables with just enough records to give the hoped for result. With a
minimum number of records (and fields ) you can see what ends up where, with 46K or 33K records you
just loose control ;-) When you eventually get the SQL to work on the small tables it should work on
the production tables too.
>You posed some interesting methods for .dbf tables which I need to test for
>the MySQL tables.
Ivar B. Jessen
| |
| Claus Mygind 2006-10-25, 7:25 am |
| Ok Ivar,
You going to make me do it the hard way. The following example assumes that
you have a MySQL database set up:
here is the code: (see below what the expected result should be)
db = new DATABASE()
db.databaseName = "test"
db.active = true
create table testMast (ID CHAR(1))
insert into testmast values("A")
insert into testmast values("B")
insert into testmast values("C")
create table testJob (ID CHAR(1), JOBID CHAR(2))
insert into testJob values("A", "AA")
insert into testJob values("A", "AB")
insert into testJob values("B", "BA")
insert into testJob values("B", "BB")
create table testProp (ID CHAR(1), PROPID CHAR(2))
insert into testProp values("A", "PA")
insert into testProp values("A", "PB")
insert into testProp values("C", "PC")
q = new QUERY()
q.database = db
q.sql = 'select t1.id,'+;
' concat(t2.jobid) as jId,'+;
' concat(t3.propid) as pId'+;
' from testmast t1'+;
' left join '+;
' ('+;
' select distinct j.id, j.jobid '+;
' from testjob j '+;
' order by jobid desc'+;
' ) as t2 on t1.id=t2.id'+;
' left join '+;
' ('+;
' select distinct p.id, p.propid '+;
' from testprop p '+;
' order by propid desc'+;
' ) as t3 on t1.id=t3.id'
q.requestlive = false
q.active = true
c = q.rowset.fields
do while not q.rowset.endOfSet
? c["ID"].value+" "+c["jId"].value+" "+c["pId"].value
q.rowset.next()
enddo
This is the result I am looking for:
"A", "AB", "PB"
"B", "BB", null
"C", null, "PC"
| |
| David Kerber 2006-10-25, 7:25 am |
| In article <#pLDVH66GHA.1168@news-server>, cmygind@tsccorp.com says...
> Ivar
>
> Ok here is the code. The syntax is correct and it works (I have tested it),
> just not the way I like it.
>
> SELECT t1.id, t2.id, t2.jobid, t3.id, t3.propid
> FROM master t1
> left join (
> SELECT distinct j.id, j.jobid, j.jobdate
> FROM job j
> order by jobid desc
> )
> as t2 on t1.id = t2.id
> left join (
> SELECT distinct p.id, p.propid, p.propdate
> FROM prop p
> order by propid desc
> )
> as t3 on t1.id = t3.id
> where t1.collection = "T"
>
> I am only returning one field from the master table for demo purposes. The
> problem here is of the 20K client records only 60 qualify in the "where"
> clause.
>
> When I add the first subquery "t2" for the job table of 46K records the
> returned rowset grows from 60 to 888.
>
> Then when I add the 2nd subquery "t3" for the proposal table of 33K records
> the returend rowset grows to 83,358.
>
> So the question is, is there a way to limit the returned rowset to just 60
> while having included the two fields from jobs (t2.jobId, t2.jobDate) and
> the two fields from proposals (t3.propId, t3.propDate)?
>
> You posed some interesting methods for .dbf tables which I need to test for
> the MySQL tables.
Since the methods have all used SQL, they will look for any database
that supports the SQL syntax we are using whether it be dBase, mySQL or
Oracle.
This would be my suggestion (UNTESTED!!)
select t1.id, t2.id, t2.jobid, t3.id, t3.propid,
( select j.jobid FROM job j where j.id = t.id and j.jobid =
(select max(jobid) from job where j.id = t.id)) as RecentJobID,
(select j.jobdate from job j where j.id = t.id and j.jobid =
(select max(jobid) from job where j.id = t.id)) as RecentJobDate,
and similar constructs from the other table
--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).
| |
| Claus Mygind 2006-10-25, 7:25 am |
| Intresting concept, execpt sub-queries can only go in the "from" portion of
the the select statment.
To create a new field we have to use "concat" which can also be an
expression, but I have not been able to put a select statment in there that
produces a desired result.
It appears that sub-query can also be put in the "where" clause, but must
only return one row (which of course is what I want, but I have not been
able to construct that either.
>
> Since the methods have all used SQL, they will look for any database
> that supports the SQL syntax we are using whether it be dBase, mySQL or
> Oracle.
>
> This would be my suggestion (UNTESTED!!)
>
> select t1.id, t2.id, t2.jobid, t3.id, t3.propid,
> ( select j.jobid FROM job j where j.id = t.id and j.jobid =
> (select max(jobid) from job where j.id = t.id)) as RecentJobID,
> (select j.jobdate from job j where j.id = t.id and j.jobid =
> (select max(jobid) from job where j.id = t.id)) as RecentJobDate,
>
> and similar constructs from the other table
>
> --
> Remove the ns_ from if replying by e-mail (but keep posts in the
> newsgroups if possible).
| |
| David Kerber 2006-10-25, 7:25 am |
| In article <yyV8cWT7GHA.1744@news-server>, cmygind@tsccorp.com says...
> Intresting concept, execpt sub-queries can only go in the "from" portion of
> the the select statment.
That depends on the SQL engine. The construction below works fine in
Sybase SQLAnywhere. If the one you're using doesn't support that, then
it will be an even tougher problem.
[color=darkred]
> To create a new field we have to use "concat" which can also be an
> expression, but I have not been able to put a select statment in there that
> produces a desired result.
>
> It appears that sub-query can also be put in the "where" clause, but must
> only return one row (which of course is what I want, but I have not been
> able to construct that either.
>
--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).
| |
| Claus Mygind 2006-10-25, 7:25 am |
| Yes it is going to be a tougher problem. I am thinking I will have to
simply make a hit on each of the child tables as I stream out the master
table records until I come up with some other construct for the select.
Thanks for the help anyway. It still makes you learn about different
options.
"David Kerber" < ns_dkerber@ns_Warren
RogersAssociates.com> wrote in message
news:MPG. 1f96bc7bb13af3ba9896
e8@news.dbase.com...
> In article <yyV8cWT7GHA.1744@news-server>, cmygind@tsccorp.com says...
>
> That depends on the SQL engine. The construction below works fine in
> Sybase SQLAnywhere. If the one you're using doesn't support that, then
> it will be an even tougher problem.
>
>
>
>
> --
> Remove the ns_ from if replying by e-mail (but keep posts in the
> newsgroups if possible).
| |
| Ivar B. Jessen 2006-10-25, 7:25 am |
| On Wed, 11 Oct 2006 13:57:50 -0500, in dbase.internet,
Subject: Re: Stuck on Select,
Message-ID: <En4n2eW7GHA.1164@news-server>,
"Claus Mygind" <cmygind@tsccorp.com> wrote:
>Yes it is going to be a tougher problem. I am thinking I will have to
>simply make a hit on each of the child tables as I stream out the master
>table records until I come up with some other construct for the select.
I tried without success to resurrect the MySQL database I had once, and made another attempt using
dbf tables.
Try the code below my signature. With the tables you posted I get the following result, which may
depend upon the language driver used. The SQL will probably work also in MqSQL.
Record# ID Job Prop
1 A AA PB
2 B BB
3 C PC
Is it close enough?
Ivar B. Jessen
//-----
close tables
clear
if file("testMast.dbf")
drop table testMast
endif
if file("testJob.dbf")
drop table testJob
endif
if file("testProp.dbf")
drop table testProp
endif
create table testMast (ID CHAR(1))
insert into testmast values("A")
insert into testmast values("B")
insert into testmast values("C")
create table testJob (ID CHAR(1), JOBID CHAR(2))
insert into testJob values("A", "AA")
insert into testJob values("A", "AB")
insert into testJob values("B", "BA")
insert into testJob values("B", "BB")
create table testProp (ID CHAR(1), PROPID CHAR(2))
insert into testProp values("A", "PA")
insert into testProp values("A", "PB")
insert into testProp values("C", "PC")
select t.id ID, max(j.jobid) Job, max(p.propid) Prop ;
from testmast t ;
left outer join testJob j ;
on t.id = j.id ;
left outer join testProp p ;
on t.id = p.id ;
group by t.id, j.id, p.id
list
use
//-----
| |
| Claus Mygind 2006-10-25, 7:25 am |
| Ivar,
Your solution worked perfectly with .dbf files
I even got it to work correctly in my MySQL administrator and it returned
the desired results.
Then I tried to execute it from in a dBase .prg file accessing my MySQL
database and tables and I ran into some problems. Don't know if this could
be a BDE problem or what.
Here is what I tried:
first
I just ran the code the way you wrote it. That gave an error "Capability
not Supported"
second
I used the "executeSQL", eventhough I know it can only be used when not
returning a rowset. It gave simply "Syntax Error".
/// open database
db = new DATABASE()
db.databaseName = "test"
db.active = true
db.executeSQL(select t.id ID, max(j.jobid) Job, max(p.propid) Prop from
testmast t left outer join testJob j on t.id = j.id left outer join testProp
p on t.id = p.id group by t.id, j.id, p.id)
third (and this was the closest)
I created a query object. But I could only get it to return the first row
with the correct result.
/// open database
db = new DATABASE()
db.databaseName = "test"
db.active = true
q = new QUERY()
q.database = db
q.sql = 'select t.id ID, '+;
' max(j.jobid) Job, '+;
' max(p.propid) Prop '+;
' from testmast t '+;
' left outer join testJob j '+;
' on t.id = j.id '+;
' left outer join testProp p '+;
' on t.id = p.id '+;
' group by t.id, j.id, p.id'
q.requestlive = false
q.active = true
q.rowset.first()
c = q.rowset.fields
do while not q.rowset.endOfSet
? c["ID"].value+" "+c["Job"].value+" "+c["Prop"].value
q.rowset.next()
enddo
"Ivar B. Jessen" <bergishagen@it.notthis.dk> wrote in message
news:1rnqi2tmp7dpp67
h03ve1ds5ajorp6tu9u@
4ax.com...
> On Wed, 11 Oct 2006 13:57:50 -0500, in dbase.internet,
> Subject: Re: Stuck on Select,
> Message-ID: <En4n2eW7GHA.1164@news-server>,
> "Claus Mygind" <cmygind@tsccorp.com> wrote:
>
>
> I tried without success to resurrect the MySQL database I had once, and
> made another attempt using
> dbf tables.
>
> Try the code below my signature. With the tables you posted I get the
> following result, which may
> depend upon the language driver used. The SQL will probably work also in
> MqSQL.
>
>
> Record# ID Job Prop
> 1 A AA PB
> 2 B BB
> 3 C PC
>
>
> Is it close enough?
>
>
> Ivar B. Jessen
>
> //-----
> close tables
> clear
>
> if file("testMast.dbf")
> drop table testMast
> endif
>
> if file("testJob.dbf")
> drop table testJob
> endif
>
> if file("testProp.dbf")
> drop table testProp
> endif
>
> create table testMast (ID CHAR(1))
> insert into testmast values("A")
> insert into testmast values("B")
> insert into testmast values("C")
>
> create table testJob (ID CHAR(1), JOBID CHAR(2))
> insert into testJob values("A", "AA")
> insert into testJob values("A", "AB")
> insert into testJob values("B", "BA")
> insert into testJob values("B", "BB")
>
> create table testProp (ID CHAR(1), PROPID CHAR(2))
> insert into testProp values("A", "PA")
> insert into testProp values("A", "PB")
> insert into testProp values("C", "PC")
>
> select t.id ID, max(j.jobid) Job, max(p.propid) Prop ;
> from testmast t ;
> left outer join testJob j ;
> on t.id = j.id ;
> left outer join testProp p ;
> on t.id = p.id ;
> group by t.id, j.id, p.id
>
> list
> use
> //-----
| |
| Claus Mygind 2006-10-25, 7:25 am |
| third attempt should say
It only returned one row
but at least that one row had the right result.
| |
| Claus Mygind 2006-10-25, 7:25 am |
| I will post this question in the programming NG. Perhaps someone with MySQL
experience can help out as this is not strictly a Internet question
| |
| David Kerber 2006-10-25, 7:25 am |
| In article <4w9nH4f7GHA.1012@news-server>, cmygind@tsccorp.com says...
> I will post this question in the programming NG. Perhaps someone with MySQL
> experience can help out as this is not strictly a Internet question
Actually, SQLservers is probably the best NG.
--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).
| |
| Ivar B. Jessen 2006-10-25, 7:25 am |
| On Thu, 12 Oct 2006 07:53:56 -0500, in dbase.internet,
Subject: Re: Stuck on Select,
Message-ID: <4w9nH4f7GHA.1012@news-server>,
"Claus Mygind" <cmygind@tsccorp.com> wrote:
>I will post this question in the programming NG. Perhaps someone with MySQL
>experience can help out as this is not strictly a Internet question
>
I am glad that it worked woth MySQL also :-)
BTW in my code you should replace the max() funtions with min() functions to get the _first_ child,
assuming the are ordered ascending.
To see this you could replace the JOBID values in the testJob table in the follwing way,
("A", "1")
("A", "2")
("B", "1")
("B", "2")
and make a similar modification in testProp table,
("A", "10")
("A", "20")
("C", "30")
Ivar B. Jessen
| |
| Claus Mygind 2006-10-25, 7:25 am |
| Thanks, I reposted there.
"David Kerber" < ns_dkerber@ns_Warren
RogersAssociates.com> wrote in message
news:MPG. 1f98282de51715d39896
ea@news.dbase.com...
> In article <4w9nH4f7GHA.1012@news-server>, cmygind@tsccorp.com says...
>
> Actually, SQLservers is probably the best NG.
>
> --
> Remove the ns_ from if replying by e-mail (but keep posts in the
> newsgroups if possible).
|
|
|
|
|