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
477 Views

Similar Articles

[PageSpeed] 7

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:

Similar Artilces:

value from first table = name of the second table
I don't know if that's possible or not. SELECT t1.*,t2.name FROM table1 as t1 INNER JOIN CONCAT_WS('','txt_',t1.field) as t2 ON t1.idt=t2.idt Above query is only notation of what I mean (it doesn;t work ;-) ) I want to join table1 with tabel which name will be made of 'txt_' and value from table1.field and then, I want to join table1 with 'CONCAT_WS('','txt_',t1.field)' table. Is that possible? How can I do that? Greetings kojot On 22 Nov 2006 08:45:08 -0800, kojot wrote: > I don't know if that's possible or not. > &g...

getting a value from a second table if the first table has not matching records
I have two tables that both contain names. There is no common index between the two tables (they are independent of each other). Both tables have 'name' fields, for example table TA has a column called a_name and table TB has a column called b_name. What I'm trying to do is to search table A for a name matching a specific value, if that does NOT match any records in table A, I want to see if there are any records in table B that match. Whichever matches, I would like the name and some other associated columns returned with a common name, e.g. final_name. I've tried experem...

TABLES TABLES TABLES
How would you best describe to a retiscent SAS student that the concept of TABLES isn't limited to SQL? This came up during a discussion on table lookup methods - and lookup tables in particular (I mean, lookup tables pre- date SQL). Even SAS data sets are referred to as tables. This made the student apoplectic. Words of wisdom most welcome!!! Thanks, Howard sasbum@AOL.COM wrote: >How would you best describe to a retiscent SAS student that the concept of >TABLES isn't limited to SQL? This came up during a discussion on table >lookup methods - and lookup tables in partic...

Appending from first table where not in second table
I want to verify that the following SQL insert statement is correct: sSQL = "INSERT INTO [BACKUP_TABLE] (FIELD1, FIELD2, FIELD3, FIELD4, FIELD5)" & _ " SELECT A.FIELD1, A.FIELD2, A.FIELD3, A.FIELD4, A.FIELD5" & _ " FROM [MAIN_TABLE] As A LEFT JOIN [BACKUP_TABLE] AS B ON" & _ " (A.FIELD1= B.FIELD1 AND" & _ " A.FIELD2 = B.FIELD2 AND" & _ " A.FIELD3 = B.FIELD3)" & _ " WHERE B.FIELD1 Is Null" & _ " AND B.FIELD2 Is Null" & _ " AND B.FIELD3 Is Nu...

Name of query/table from where values are pass into table
There is a form where it ask the two dates and then run a query then report. Private Sub Command36_Click() DoCmd.OpenQuery "qryResultsReport" DoCmd.OpenReport "rpt_TopTen", acPreview, "", "" end sub qryResultsReport SELECT tblCallSummary.* INTO tblResults FROM tblCallSummary WHERE (((tblCallSummary.[Date/Time]) Between [forms]![MainMenu]![txtStartDate] And [forms]![MainMenu]![txtEndDate]+1)); When i open the report rpt_TopTen the values which pass into the report is totall different the values which are in that table. How to i know the name of the ta...

How to display database table data in a html table.
Hi, I'm trying to display data from a mysql database in a HTML table but for some reason my code isn't working. At the moment I have got it to read and display the headers and the first row of the table and it actually creates the remaining rows in the html table but it doesn't put any data in them. This is my code so far: <?php $con = mysql_connect("localhost","REMOVED","REMOVED"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("db_03009319", $con); ?> <?php $selectedTable=$_GET["sel...

return a value from a table if value from another table is between two values
I'm really unsure about what this might be called: i have a table (tblMdfRate) with feilds MdfRateUpper, MdfRateLower and MdfRateAmt i want to return MdfRateAmt when a customer's sales (SalesAmt in table tblSalesByDateRange) are between MdfRateUpper and MdfRateLower. I think i have done this before but i dont work with access on a daily basis and i have forgotten.... im sure it is a simple thing? SELECT tblSalesByDateRange.SalesAmt, tblMdfRate.MdfRateAmt FROM tblSalesByDateRange INNER JOIN tblMdfRate ON ( tblSalesByDateRange.SalesAmt >= tblMdfRate.MdfRateLower A...

Simple one-table query always scans table
We have a one-table-query than _insists_ on a full table scan, regardless of any indexes, stats, optimizer options, etc. that we try. This is the query with sample where clause: select count(xref_reason) as num_links from case_xref where court='CREW' and court_no='72' and yr=2008 and insol_type='B' OR ( xref_court='CREW' and xref_court_no='72' and xref_yr=2008 and xref_insol_type='B' ) We have tried adding indexes of various structures, with w/o the xref_reason column, -zcpk compound key stats etc. When run individually, each 'side' of the OR uses its appropriate index, however when bolted together, the statement invariably scans the table. You will see that the table is quite small at ~8Mb, however this is a 1800 concurrent user application and the query is run a gazillion times per day. Unfortunately due to the lifespan of the application we can't easily make code changes. We are running Ingres II 2.6/0305 (rs4.us5/00), on AIX 5.2 Help! TIA Steve Table: 1> help table case_xref Name: case_xref Owner: ingres Created: 06/10/08 07:56:27 Location: custmerge Type: user table Version: II2.6 Page size: 4096 Cache priority: 0 Alter table version: 0 Alter table totwidth: 102 Row width: ...

Updating table from data in two other tables via a query
I have a table of 'Customers', a table of 'Newsletters' and a table for storing which customers have recieved which letters 'CustLett'. By using a query, I have pulled a list of customers who qualify for newsletter No.1. The are displayed using a subform for viewing purposes. What I want to do now (in one swift action) is to add the customer account no's to the table 'CustLett' along with the ID of Newsletter No.1. I think I can do this one at a time, but with 10,000 customers on the database, this would take a long time. Please let me know if you have ...

Re: TABLES TABLES TABLES
sasbum@AOL.COM wrote: >How would you best describe to a retiscent SAS student that the concept of >TABLES isn't limited to SQL? This came up during a discussion on table >lookup methods - and lookup tables in particular (I mean, lookup tables >pre- >date SQL). Even SAS data sets are referred to as tables. This made the >student apoplectic. I find a visual works well here. If you show a grid with rows and columns, then you can show a picture of a SAS data set and a SQL table and a lookup table, and show that (regardless of terminology and buzzwords) they're all pr...

How to read every data in the first column corresponding to the every data in the second column in the TABLE CONTROL?
Hello everyone &nbsp; like this table &nbsp; 0.0001&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -4.6 0.0002&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -4.3 0.0004&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -4.0 0.0006 0.0008 0.001 &nbsp; I want to get&nbsp; pairs (0.0001, -4.6) (0.0001, -4.3)&nbsp; (0.0001,&nbsp; -4.0) (0.0002,&nbsp; -4.6) (0.0002,&nbsp; -4.3) (0.0002,&nbsp; -4.0) (0.0004,&nbsp; -4.6) (0.0004,&n...

Queries --> Finding what is not in table 2 from the data in table 1
So if I were to take table 1 and relate it to table 2 (one-to-one), I could then run a query which would display the records that are in both tables. I'm looking to run a query that would display the records in table 2 that are not in table 1. What is the easiest/best way to do this? Thanks, Ian Does this help? http://www.fabalou.com/access/Queries/isnullquery.asp john "boston_dude" <ian.ganley@gmail.com> schreef in bericht news:1184779374.420330.218340@e16g2000pri.googlegroups.com... > So if I were to take table 1 and relate it to table 2 (one-to-one), I > co...

Updating data in table depending on data in same table
Hey everyone, First of all apologies for posting this in the oracle.server section I meant to post it in here first. I'm still a relative newb so go easy please! I've inherited a system from someone that is in a bit of a mess and I need to tidy it up. It's an Oracle 10g database. The part i have to clean up basically has two tables. Table A attributes are: SOURCEID, NAME, DESCRIPTION, DEPARTMENT, LANGUAGES. Table B attributes are: SOURCEID, LANGID, ROLE, STATUS. Now the way this system is supposed to work is that a row is entered into table A and X amount of languages are added to the LANGUAGES field. This field is then split up into a row for each language into table B with the corresponding SOURCEID. e.g. Table A: 123 -- Homer -- lazy -- HR -- French;Spanish;German Table B: 123 -- French -- manager -- approved 123 -- Spanish -- admin -- pending 123 -- German -- teaboy -- proposed This system had been working fine until a new person started and instead of just adding a new language to the original row in table A she added 1000+ rows that are identical to those already in the table except for the fact the language is Japanese. So now the table looks like Table A: 123 -- Homer -- lazy -- HR -- French;Spanish;German 456 -- Homer -- lazy -- HR -- Japanese; Table B: 123 -- French -- manager -- approved 123 -- Spanish -- admin -- pending 123 -- German -- teaboy -- proposed 456 -- Japanese -- janitor -- approved I have...

From table to table....
I want to create a new table containing only the results from a select statement of another table. How is that done? select a, b, c into destTable from sourceTable where a='x' or a='y' or a='z'; Does one have to create the destTable or is it created for you with the correct structure? Can the rows of one table be used in the select of another table? eg: The above select statement has three choices. could those 'choices' have been rows of a column of a table? On Wed, 1 Feb 2012 10:29:45 -0800 (PST), SpreadTooThin wrote: > I want to create a new table co...

Table of tables
Given t = {{{-1, -1, -2+2I}, {-1, -1, 3-I}}, {{-1, -1, 4+I}, {-1, -1, -5-5I}}}; how can I extract the imaginary part of the complex elements to obtain {{{-1, -1, 2}, {-1, -1, -1}}, {{-1, -1, 1}, {-1, -1, -5}}}; thank you. Luiz Melo -- On Oct 20, 1:07 am, Luiz Melo <luiz.m...@polymtl.ca> wrote: > Given > > t = {{{-1, -1, -2+2I}, {-1, -1, 3-I}}, {{-1, -1, 4+I}, {-1, -1, -5-5I}}}; > > how can I extract the imaginary part of the complex elements to obtain > > {{{-1, -1, 2}, {-1, -1, -1}}, {{-1, -1, 1}, {-1, -1, -5}}}; > > thank yo...

Copy data from one table to another table with change in identity column values
HI, I have a table Create table test(a int identity(1,1), b int) insert into test(b) values(12) insert into test(b) values(30) insert into test(b) values(65) insert into test(b) values(78) insert into test(b) values(36) o/p a b 1 12 2 30 3 65 4 78 5 36 i need to copy the table into another table with creating the new table we had a option select * into newtable from oldtable here the data in the oldtable will e copied to new table without creating new table but i need to copy the data as shown below with out creating new table o/p a b 11 12 12 30 13 65 14 78 15 36 Thanks, Sat...

Form to select the data from a table using multiple queries, save to another table
Hi all, I am new to access and am finding it a bit unintuitive having worked with SQL server in the past... And I am in a bit of a hurry because my employer wants me to crank something out which at first seemed like a piece of cake but I realize now I should have done a little more research before I got started. Here is the issue: I have a table with data relating to our pastry products. I built a form with multiple combo boxes that are limited by category etc so that the pastry chef can use the form to create his production schedule for the week. This worked great and he can print the sched...

Query filter - Joined Tables
Hello, I have a Database setup that has two tables that are linked. Table1 contains a list of people with their basic contact details (name & phone number etc.) and Table2 contains a list of all marketing calls made to the people on Table1. The tables are linked via an ID # field. What I would like to create is a query that shows all contacts on Table1 who we have not made a call to. The problem I'm experiencing is that as these people have never been called, they don't have any records (calls) on Table2. I'm struggling to find a filter that will filter out people who have re...

To retrive a table from one database to and another database(same table)
hi i have emp table in one database , how to retrieve same table another database through query analizer give code ,,, Hi, use the three part notation: SELECT * FROM database.owner.Objectname (replace owner by schema for SQL2k5) HTH, Jens Suessmeyer. --- http://www.sqlserver2005.de --- Hi surya While the first database is "Current" as would result from: Use FirstDatabaseName select * from OtherDatabaseName..Emp -- -Dick Christoph "surya" <suryaitha@gmail.com> wrote in message news:1142588469.061525.268810@u72g2000cwu.googlegr...

Drawing table values linked to values in design table?
Hello Is there a way to have values in a generic table in a drawing driven by values in a design table of a part/configuration? I have created a generic drawing and dimension table for each configuration of an assembly with general dimensions A, B, C, D etc. All the dimesnions are already in teh assembly design table. I currently have to manually fill in the table on the drawing. It would be better if I could directly have them linked to the values in the design table. Thaks in advance Bull We insert the Design Table into the drawing and format it using hide columns and rows and the other format tools in Excel. There are rules in help on which row to use for column headings. If the dimension shows in the drawing, you can add it to your generic SW table. Double-click the cell in the table to begin editing it. Then, click once on the dimension you'd like to appear in the cell. YOU MUST THEN add some sort of extra text. If you click to exit the cell without adding anything else, the dimension value disappears. You might add a unit suffix, or if you were doing the overall dimensions for a sheet metal flat pattern, add an "x" between the two dimensions. On 2 Sep 2006 11:25:37 -0700, "TOP" <kellnerp@cbd.net> wrote: >We insert the Design Table into the drawing and format it using hide >columns and rows and the other format tools in Excel. There are rules >in help on which row to use for column headings. That is possible too. ...

table of frequnce to table of data
i have a table of frequence like: id freq 1 3 2 5 3 4 4 4 5 5 6 8 7 8 8 9 9 5 10 6 ; i want to transforme this table on a serial data like: 1 1 1 2 2 2 2 2 3 3 3 3 ............. can you help me ; Dear Sousimou, Try something like this: DATA want(keep=id); SET have(KEEP=id freq); DO i = 1 TO freq; OUTPUT; END; RUN; LouisBB. (I don't have Sas at home, the code is untested) <sousimou@gmail.com> wrote in message news:1172694076.726871.181840@m58g2000cwm.googlegroups.com... >i have a table of frequence like: > id freq > 1 3 > 2 ...

Update a Table from a Second Table
This is an easy way to update a table from a second table in Sql Server. update table1 set table1.col2 = table2.col2 from table1, table2 where table1.keycol = table2.keycol I know you can do something like this in Oracle: update table1 a set table1.col2 = (select col2 from table2 where table2.keycol = a.keycol) but that example will update every row in table1, not just the ones that match the rows in table2. The sql server version not only gets the values it needs from the second table, it also limits the rows updated to those the matching rows in table2. Is there an easy way to do this in Oracle? thanks much Greg wrote: > This is an easy way to update a table from a second table in Sql > Server. > > update table1 > set table1.col2 = table2.col2 > from table1, table2 > where table1.keycol = table2.keycol > > I know you can do something like this in Oracle: > > update table1 a > set table1.col2 = (select col2 from table2 where table2.keycol = > a.keycol) > > but that example will update every row in table1, not just the ones > that match the rows in table2. > > The sql server version not only gets the values it needs from the > second table, it also limits the rows updated to those the matching > rows in table2. > > Is there an easy way to do this in Oracle? > > thanks much It never fails to amaze me that people coming to Oracle from SQL Server ...

Is it possible to split the table so that the first colum always staty on screen and the rest of the table is horizontally scrollable?
Please help me with this. Is it possible to split the table so that the first colum always stay on screen and the rest of the table is horizontally scrollable? On 06-02-2005 12:25, Alexey Litvinuke wrote: > Please help me with this. > Is it possible to split the table so that the first colum always stay on > screen and the rest of the table is horizontally scrollable? > > Try this: <http://www.codeguru.com/java/articles/128.shtml>. Paul. Thanks! "Paul van Rossem" <paul@timeware.nl> wrote in message news:420609f3$0$28984$e4fe514c@news.xs4all.n...

How can I convert an SQLite3 database table which contains data in Arabic language to an HTML table on Windows?
Hi. How can I convert an SQLite3 database table which contains data in Arabic language to an HTML table? The Tcl script I am using must run on Windows. and here is the Tcl code I am using: [code=tcl] package require sqlite3 sqlite3 dbcmd dbfile.sqlite3 proc save_current_date_total_income_report { args } { set filetype { { {Report as HTML File} {.html} } } set filename [tk_getSaveFile -filetypes $filetype] set fp [ open $filename "w"] set report_query { select * from purchase where purchase_date >=date() } puts $fp "<table border=1>\n" ...

Web resources about - Query to Always Provide Data from First Table With Optional Values From Second Table - comp.databases.postgresql

Second Optional Protocol to the International Covenant on Civil and Political Rights - Wikipedia, the ...
The Second Optional Protocol to the International Covenant on Civil and Political Rights, aiming at the abolition of the death penalty is a side ...

Toys R Us Big Book Available On Facebook As App From Context Optional
Toys R Us has introduced up its annual Big Book catalog of toys — on Facebook, in a new application developed by Context Optional . The application ...

Fatal Frame II Chapter 8 Optional Ghosts - Kiryu House - YouTube
You don't have to start at the entrance, it's just easier to get the ghosts on the way there to the crest battle. I'd be more scared fighting ...

Lower emissions, batteries optional
Lower emissions, batteries optional

Helmets video - Graham Quirk wants helmets to be optional, Brisbane
Lord Mayor Graham Quirk says bicycle helmets should be optional on Brisbane bikeways but the "laws are set by the state".

Driving experience optional for a fender-bending teen
Driving experience optional for a fender-bending teen

JIWA Financials Optional Modules
CFOworld.com.au covers accounting, risk management, taxation, technology and financial news from ASX-listed companies.

Party time, dress optional
THE SCENE is set for a city to throw its inhibitions away as the Hookers Ball returns to Darwin for its 29th year.

Finding high-tech flaws for fun and an optional profit
NATHANIEL CAREW makes a living protecting computer systems. In his spare time, he hacks into Google.

Pulp Fiction (3D, HD) - Overdose Needle Scene in optional Analglyph 3D (b) - YouTube
Pulp Fiction (1994) 'Overdose' scene (color corrected) in optional Analglyph 3D (3D only works in 360p resolution). Intracardiac injection of ...

Resources last updated: 2/23/2016 10:46:56 PM