f



Query to Always Provide Data from First Table With Optional Values From Second Table

I have two tables that has the following data:

NameTable:

ID    Name
-----------
1     foo
2     bar
3     doe
4     john

NotesTable:

ID    Date      Value
----------------------------------
1     6/9/06    "test 1"
1     6/9/06    "test 2"
2     6/1/06    ""
2     6/2/06    "   "
3     6/9/06    "test 3"

Where NameTable.id = NotesTable.id

And I want the query to return the following.  Note that values that are 
null, zero length, or nothing but spaces are not included in the 
results, but "bar" and "john" are still listed at least once in the results.

Name     Date      Value
----------------------------
foo      6/9/06    "test 1"
foo      6/9/06    "test 2"
bar      null      null
doe      6/9/06    "test 3"
john     null      null
0
O
7/6/2006 8:42:46 PM
comp.databases.postgresql 709 articles. 0 followers. Post Follow

2 Replies
560 Views

Similar Articles

[PageSpeed] 36

O.B. napsal(a):
> I have two tables that has the following data:
> 
> NameTable:
> 
> ID    Name
> -----------
> 1     foo
> 2     bar
> 3     doe
> 4     john
> 
> NotesTable:
> 
> ID    Date      Value
> ----------------------------------
> 1     6/9/06    "test 1"
> 1     6/9/06    "test 2"
> 2     6/1/06    ""
> 2     6/2/06    "   "
> 3     6/9/06    "test 3"
> 
> Where NameTable.id = NotesTable.id
> 
> And I want the query to return the following.  Note that values that are 
> null, zero length, or nothing but spaces are not included in the 
> results, but "bar" and "john" are still listed at least once in the 
> results.
> 
> Name     Date      Value
> ----------------------------
> foo      6/9/06    "test 1"
> foo      6/9/06    "test 2"
> bar      null      null
> doe      6/9/06    "test 3"
> john     null      null

Hi, perhaps

SELECT
   t1.id,
   t2.date,
   t2.value
FROM
   nametable t1
   LEFT JOIN notestable t2 ON
     t1.id = t2.id
     AND t2.value IS NOT NULL
     AND trim(t2.value) != ''

--
Milo
0
UTF
7/6/2006 9:10:38 PM
Miloslav Hůla wrote:
> O.B. napsal(a):
>> I have two tables that has the following data:
>>
>> NameTable:
>>
>> ID    Name
>> -----------
>> 1     foo
>> 2     bar
>> 3     doe
>> 4     john
>>
>> NotesTable:
>>
>> ID    Date      Value
>> ----------------------------------
>> 1     6/9/06    "test 1"
>> 1     6/9/06    "test 2"
>> 2     6/1/06    ""
>> 2     6/2/06    "   "
>> 3     6/9/06    "test 3"
>>
>> Where NameTable.id = NotesTable.id
>>
>> And I want the query to return the following.  Note that values that 
>> are null, zero length, or nothing but spaces are not included in the 
>> results, but "bar" and "john" are still listed at least once in the 
>> results.
>>
>> Name     Date      Value
>> ----------------------------
>> foo      6/9/06    "test 1"
>> foo      6/9/06    "test 2"
>> bar      null      null
>> doe      6/9/06    "test 3"
>> john     null      null
> 
> Hi, perhaps
> 
> SELECT
>   t1.id,
>   t2.date,
>   t2.value
> FROM
>   nametable t1
>   LEFT JOIN notestable t2 ON
>     t1.id = t2.id
>     AND t2.value IS NOT NULL
>     AND trim(t2.value) != ''
> 
> -- 
> Milo

This got me going in the right direction.  Thank you!
0
Funky
7/7/2006 11:02:45 PM
Reply: