Re: [libreoffice-users] base - join same table multiple times

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: users+help@global.libreoffice.org
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