Displaying results for when have null results in multiple queries

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?
kdr2003Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
kdr2003Author Commented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

kdr2003Author Commented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
kdr2003Author Commented:
They have distinct names.   That is not enough?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what are you trying to achieve with the results?
0
kdr2003Author Commented:
I need the following results: Count, dollars, Pesos, date  but I need to know which product  the data belong to.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
kdr2003Author Commented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kdr2003Author Commented:
Okay I got it.  I run each of the queries separately but when I run the Union query I get ODBC call failed.
0
kdr2003Author Commented:
I got it I took out the group by for the product in union query.

thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.