Solved

Displaying results for when have null results in multiple queries

Posted on 2007-04-02
13
205 Views
Last Modified: 2010-03-20
I have one query pulling multiple queries.  If one of the queries has null records and another query has results, no results will display.   How can I get the results to display for all queries that have records and the queries that have null just 0 records?
0
Comment
Question by:kdr2003
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18838295
you will have to use a left/right outer join query.

select * from table1
left outer join table2
 on table1.keyfield = table2.keyfield

will return all rows from table1, even if there is no matching row in table2.
in case there is a match, it will return the columns of table2, otherwise there will be null values instead
0
 

Author Comment

by:kdr2003
ID: 18838524
I don't have any fields that match from table to table.   Each query is pull from its own table.   I have over ten queries in one query.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18838556
then you actually want to use a UNION.
do you want to have compatible columns from the different tables?
 -> the same number of columns, and each time compatible data types?
0
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 

Author Comment

by:kdr2003
ID: 18839048
I have different column names but same types.   I tried the union but it doesn't specify along one row the column descriptions.   It places the information in consecutive rows.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18839057
>I tried the union but it doesn't specify along one row the column descriptions.
that is normal. as to your description, you actually need really distinct queries.
0
 

Author Comment

by:kdr2003
ID: 18839453
They have distinct names.   That is not enough?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18839470
what are you trying to achieve with the results?
0
 

Author Comment

by:kdr2003
ID: 18839497
I need the following results: Count, dollars, Pesos, date  but I need to know which product  the data belong to.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18839515
without more details about the tables it will be impossible to put a concrete table.

anyhow, a suggestion:

SELECT 'table1' as product, col1, col2, col3 FROM table1
UNION
SELECT 'table2' , col1, col2, col3 FROM table2
UNION
SELECT 'table3' , col1, col2, col3 FROM table3

etc.

0
 

Author Comment

by:kdr2003
ID: 18839615
I don't have a column designated a product.   Each table is its own product.  That is why I have 15 separate queries.   Currently, I have one query pulling all 15 queries but the problem is when I table doesn't have any records for the designated date, no results appear for anything.  Some fields are date fields other are text fields as dates displayed as 1070402.  I created a form with each date displayed to pull each query.    When I tried the Union I don't get the description of the product.  In what I currently doing I have 45 columns displaying which the description in the column heading.  
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 18839736
>I don't have a column designated a product.
yes, that is exactly what my suggestion is doing. each query generates 1 virtual column identifying the table from which the row is coming from. so, in the output, you can determine from that column (called product in my above sample), from which table the rows originally came from.
0
 

Author Comment

by:kdr2003
ID: 18839838
Okay I got it.  I run each of the queries separately but when I run the Union query I get ODBC call failed.
0
 

Author Comment

by:kdr2003
ID: 18839864
I got it I took out the group by for the product in union query.

thanks
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question