World: r3wp

no, Anton, everything is ok, no problems with columns, or switching 
.... I will describe you one example ....

It's called a "Pseudo-Relational Database" for a good reason. ;)


But in answer to Pekr's previous point; *you* can work out *how* 
to do that aggregation more efficiently than the limited AI of most 
RDBMS systems. I've worked at DB2 and Oracle shops where it was mandated 
that all JOINs be performed inline! SQL optimizers are not trivial 
to write, and the meta-data overhead (indexes, statistics, hints, 
etc) required to get them to make the "right" choices are often a 
false economy.

So, Petr, you solved the original problem which occurred for you 
?

ok, here it is: you have tables called 'orders, 'order-items, 'companies 
.....

in 'companies, you have 'company-id, 'company-name, 'company-address 
....

then, when you enter new order into 'orders, you simply assign it 
to company, by using and storing foreign key, so 'company-id ...

And now you have typical problem - you surely don't want your grid 
to show 'company-id, but 'company-name or other related data - but 
those are not present in 'orders database ...

Orders

Order-Items

Companies
	company-id, company-name, company-address
?

that is imo correct design to simply avoid data inconsistency. Imagine 
if you would put 'company-name into 'oders, and later on company 
changes it's name a bit - you simply want that info to have stored 
only once ...

yes ...

would you store company-name in orders directly? Surely not (although 
we are doing it too for some purposes, as getting grid drawn fast)

Data structure makes sense, what's the problematic SQL statement? 
(in its simplest form)

inner join orders, order items, left outer join companies ....

so the inner join? (agregate)

Just write the SQL statement as you would in MySQL, etc ... then 
we can see where the issues are.

ok, here's my rebol odbc code for ADS (Advantage Database Server):

insert db-port trim/lines {

   SELECT DISTINCT ko.kodfyzak, fk.nazev, fk.ulice, fk.mesto, fk.psc, 
   fk.kodzeme, zk.land1, upper(fk.kodmeny),
                   upper(e.ico),

                   ko.kodfypri1, fp.nazev, fp.ulice, fp.mesto, fp.psc, fp.kodzeme, zp.land1, 
                   upper(fp.kodmeny)
                      
   FROM zakp_exp e

   INNER JOIN Kontjkv5 k5 ON k5.zakazka = concat(e.miv, left(e.czak, 
   6)) 
   LEFT OUTER JOIN kontrakt ko ON ko.ciskontr = k5.ciskontr

   LEFT OUTER JOIN firma fk ON fk.kodfirmy = ko.kodfyzak  AND fk.aktualni 
   = 'A'

   LEFT OUTER JOIN firma fp ON fp.kodfirmy = ko.kodfypri1 AND fp.aktualni 
   = 'A'
   LEFT OUTER JOIN zem zk ON fk.kodzeme = zk.kodzeme
   LEFT OUTER JOIN zem zp ON fp.kodzeme = zp.kodzeme
   LEFT OUTER JOIN delka_psc psc ON zp.land1 = psc.land1
   ORDER BY fk.nazev, fp.nazev
}

so let's forget those aliases, which are nice things to have too 
:-)

left outer join seems like kind of subselect)

while inner join seems to be kind of aggregate ...

but that is maybe unnecessarily complicated example, we were migrating 
data to SAP R3 from our old systems .....

An example based on what you are trying to do with Orders, Items 
and Companies might be better to start with.

that was just an example simple schema, but well, I may try to write 
short SQL query from that ;-)

Stick to basic JOIN syntax in the form:

	select b.name, b.address
	from a, b
	where a.id = b.id
	and ...


those INNER JOIN and LEFT OUTER JOIN statements are unfamiliar to 
me and I get a headache just looking at them. ;)

SELECT o.order-id, o.date, o.amount, o.total, oi.item-id, io.item-price, 
oi.goods-name, c.company-name
FROM orders o
INNER JOIN order-items oi ON oi.order-id =  o.order.id
LEFT OUTER JOIN companies c ON c.company-id = o.company-id
WHERE o.amount > 2000
ORDER BY c.company-name, o.order-id, o-item-id

not sure I got it syntactically 100% correct, but you got the idea 
:-)

so basically select row defines you final format of result block 
...

inner join simply  "adds rows" of order-items per order

Not sure if I'm off topic here.....But you can do pretty much everything 
in SQL without using JOIN. Though you may need UNION, subselects 
and WHERE.

Which makes for more portable SQL as JOIN syntax often used vendor-specific 
extensions to the SQL-92 standard.

outer join just selects company name ...

Yes, that might be possible ... I am not that skilled in SQL yet, 
so maybe it could be done other way ....

I learned before JOIN existed.....Means you are more skilled than 
me :-)

maybe it is a pity rebol can't do union and join on more per record 
base, but per item base ...

that join simply creates one big table for you, which is nice, as 
you simply can traverse in one loop ....

UNION cannot substitute for JOIN. UNION aggregates the rows returned 
by multiple statements while JOIN [can] aggregate the columns returned 
by referring to multiple tables. A subtle but important distinction. 
For example:

Table-A
	Col
	====
	1
	2

Table-B
	Col
	====
	A
	B

	select a.col, b.col from a, b

	1 A
	1 B
	2 A
	2 B

	select * from a union select * from b

	1
	2
	A
	B

do not understand the syntax of select from a,b ... actually - never 
understood it :-) our db allows select, the rest is crosslinked via 
joins :-)

we would need some join/key ... to define key on which to join two 
blocks to create third one ... or maybe union/key ...

ashley, my old dbms3.r supports joins, though probably far from doing 
it efficiently. in case you need the code feel free to use it.

Thanks, I'll take a look at it. (Marco's sql-protocol.r is also a 
good read).

dbms3.r isn't in the library, where can I grab a copy from?

rebol.it is a good bet.

oohh.. looks like a Cobalt Raq.

Back to Pekr's JOIN problem. First, let's reformat the SQL into something 
more readable:


select o.order-id, o.date, o.amount, o.total, oi.item-id, io.item-price, 
oi.goods-name, c.company-name
from   orders o
,      order-items oi
,      companies c
where  o.order.id = oi.order-id
  and  o.company-id = c.company-id
  and  o.amount > 2000
order by c.company-name, o.order-id, o-item-id

not here either http://www.colellachiara.com/soft/

Then break it down into discrete queries and wrap it in some loops:

blk: copy []

foreach [company-id order-id date amount total] sql [

 select [company-id order-id date amount total] from orders where 
 [amount > 2000]
][
	company-name: second sql compose [lookup companies (company-id)]

 foreach [item-id item-price goods-name] sql compose [select * from 
 order-items where (order-id)] [

  insert tail blk reduce [order-id date amount total item-id item-price 
  goods-name company-name]
	]
]

sort/skip/compare blk 8 [8 1 5]

The final [untested] solution is about as efficient as you can get. 
Adding JOIN support to RebDB so it can break the query down into 
similar steps is not a simple task. In all but the most trivial of 
cases you'd be better off coding it yourself.


What might be a good idea is to add something that lets you more 
easily specify the most common JOIN operation - master/detail with 
optional LOV (List Of Values) lookup(s). Your query is a classic 
example of this construct and it accounts for a surprisingly large 
number of queries.


The function would accept two queries, a master query (the orders 
table in your case) and a details query (the order-items table) and 
an optional block of column/LOV-table pairs (that perform substitutions 
such as company-name). The skeleton would look like:


 sql-join [master-query [block!] detail-query [block!] /order /lov 
 [block!] ]  [
		buffer: copy []
		...
		buffer
	]

and would be used as such:

	sql-join/order/lov [

  select [company-id order-id date amount total] from orders where 
  [amount > 2000]
	] [
		select * from order-items where %ID%
	] [8 1 5] [company-id companies]

Would this make things a tad easier?

not sure your sql query rewrite was much clearer, but - your last 
post makes so much sense!

yes, basically it is typical two cases - master-detail, and lookup!!!

IIRC MySQL docs join section describes very nicely the technique 
of optimisation. And after reading it some few monts ago I agree, 
that 'join is not trivial task at all, basically due to get it optimised. 
But you are right that most of the time those two mentioned cases 
are needed. The thing which complicates the optimisation part is 
- is your column a key? primary key? is it at least indexed? etc.

anyway .... maybe a good question for RT, when finally RIF arrives, 
because if I understand you correctly, till then, RebDB will be memory 
only database (although open/seek is working already)

Graham - I noticed you used Firebird for your medical system - is 
there kind of embedded version as with sqlite?