SQL help

[william humphrey]

I am getting close.

SELECT d_containers_FullIndicator, d_containers_number,
i_containers_owner_ref,s_kcodes_islandname,
MAX(d_containers_billoflading_ref)
FROM data_containers
JOIN info_containers ON container_info
JOIN info_billoflading ON ContainerNumbers
JOIN shadow_kcodes ON port_discharge
WHERE i_containers_owner_ref = '15545'
GROUP BY d_containers_FullIndicator, d_containers_number,
i_containers_owner_ref,s_kcodes_islandname
ORDER BY s_kcodes_islandname

The above works.

On Sun, Feb 1, 2015 at 6:26 PM, william humphrey
<bill at bluewatermaritime.com> wrote:
> Got rid of extra fields as an experiment and tried this
>
> SELECT data_containers.d_containers_number FROM data_containers
> WHERE (data_containers.d_containers_number,data_containers.d_containers_billoflading_ref)
> IN (SELECT data_containers.d_containers_number,
> MAX(data_containers.d_containers_billoflading_ref) FROM
> data_containers GROUP BY data_containers.d_containers_number)
>
> but the SQL returned no records.
>
>
>
> On Sun, Feb 1, 2015 at 5:55 PM, william humphrey
> <bill at bluewatermaritime.com> wrote:
>> Also I have this SQL:
>>
>> SELECT  s_voyages_voyagenumber,s_voyages_date,data_containers.d_containers_number,
>> s_kcodes_islandname, i_billoflading_id
>> FROM info_billoflading
>> JOIN data_containers ON ContainerNumbers
>> JOIN shadow_voyages ON voyage_number
>> JOIN shadow_kcodes ON port_discharge
>> JOIN info_containers ON container_info
>> WHERE s_voyages_voyagenumber <> 'CANC'
>> AND i_billoflading_export_fl = TRUE
>> AND i_containers_owner_ref = '15545'
>> AND s_kcodes_islandname <> 'Puerto Rico'
>> AND i_billoflading_id = (SELECT MAX (i_billoflading_id) FROM info_billoflading)
>> ORDER BY data_containers.d_containers_number, s_voyages_date DESC
>>
>> which returns only the most recent i_billoflading_id when I would like
>> it to return the MAX i_billoflading_id for every d_containers_number (
>> like a group by using MAX?)
>>
>> On Sun, Feb 1, 2015 at 5:36 PM, william humphrey
>> <bill at bluewatermaritime.com> wrote:
>>> Hi again
>>> I'm trying to do an update with JOIN
>>>
>>> UPDATE data_containers
>>> JOIN info_billoflading ON container_info
>>> SET d_containers_FullIndicator = 'FUL'
>>> WHERE i_billoflading_export_fl = TRUE
>>> AND d_containers_FullIndicator = 'EMP'
>>>
>>> This works fine in a select statement but I'm having trouble using it
>>> for the UPDATE
>>>
>>> On Sun, Jan 5, 2014 at 4:29 AM, Ruslan Zasukhin
>>> <ruslan_zasukhin at valentina-db.com> wrote:
>>>> On 1/4/14, 9:32 PM, "william humphrey" <bill at bluewatermaritime.com> wrote:
>>>>
>>>> Hi William,
>>>>
>>>>> Thanks everyone for your help. I think I learned from this that there
>>>>> is always a solution in SQL even if your tables are not of the best
>>>>> design.
>>>>
>>>>> So the lesson is you can work with badly designed tables if you know
>>>>> something about SQL.
>>>>
>>>> SQL is very simple language :)
>>>>
>>>> Once I have try to think,
>>>>     lets assume I hate SQL,
>>>>     lets assume I want create new language similar to HUMAN
>>>>     how it will looks:
>>>>
>>>> And I have @invent@
>>>>
>>>> I need SELECT some records
>>>> FROM table
>>>> WHERE some conditions are
>>>> ORDER BY them
>>>>
>>>> Oops.  Same SQL :-)
>>>>
>>>>
>>>>
>>>> --
>>>> Best regards,
>>>>
>>>> Ruslan Zasukhin
>>>> VP Engineering and New Technology
>>>> Paradigma Software, Inc
>>>>
>>>> Valentina - Joining Worlds of Information
>>>> http://www.paradigmasoft.com
>>>>
>>>> [I feel the need: the need for speed]
>>>>
>>>>
>>>> _______________________________________________
>>>> Valentina mailing list
>>>> Valentina at lists.macserve.net
>>>> http://lists.macserve.net/mailman/listinfo/valentina
>>>
>>>
>>>
>>> --
>>> http://www.bluewatermaritime.com
>>
>>
>>
>> --
>> http://www.bluewatermaritime.com
>
>
>
> --
> http://www.bluewatermaritime.com



-- 
http://www.bluewatermaritime.com