On 01/23/2012 08:26 AM, Werner F. Bruhin wrote:
I have the following:
tablea
- id
- name
tableb
- id
- type
- total
- tablea_id
In tableb there can be multiple records for a record in tablea.
Like to get to this:
tablea.id, tablea.name, tableb1.total1 (if type = 1), tableb2.total2
(if type = 2)
I tried to setup a view where I link tableb twice to tablea, but I
can't figure out how to define things with regards to the relation.
In SQL it would be something along these lines:
SELECT DISTINCT `table1`.`name`, `tableb1`.`total`, `tableb2`.`total`
FROM `tablea`
LEFT OUTER JOIN `tableb` as `tableb1` ON `tablea`.`id` =
`tableb1`.`tablea_id` AND `tableb1`.`type` = 1
LEFT OUTER JOIN `tableb` as `tableb2` ON `tablea`.`id` =
`tableb2`.`tablea_id` AND `tableb2`.`type` = 1
Is this possible with base in LibrOffice 3.3.2, but could upgrade if a
newer version provides this functionality.
Thanks in advance for any tips.
Werner
I'm no guru, and I just thought about it in my head. So it will
definitely not work...
SELECT
tablea.id,
tablea.name,
(SELECT total FROM tableb WHERE type = 1 LIMIT 1) AS `Type1 Total`,
(SELECT total FROM tableb WHERE type = 2 LIMIT 1) AS `Type2 Total`
FROM
tablea, tableb
WHERE
tablea.id = tableb.tablea_id;
cK
--
For unsubscribe instructions e-mail to: [email protected]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
