FIELDS FROM TWO TABLES

[netrusher]

I have to Queries. What I am trying to do is have fields from both of these queries show in another Query.
Example:

I have SOECODE, Product, & Description Fields in both Queries. I would like for these three fields to show up side by side in another Query. It would be like this:

SOECODE SOECODE PRODUCT PRODUCT DESCRIPTION DESCRIPTION.

How can I do this? and thanks.

 

[Remou]

From the above, it seems to be a matter of creating a new query based on the two existing queries and creating a link by dragging one SOECODE over the other SOECODE, in the query design window. Is this what you mean, or have I missed your point?

 

[netrusher]

Well, when I create a link it is going to limit me to records that match in both tables. I want to seen all the records from each table side by side.

 

[Remou]

You must have a link, but it need not limit you. Right-click on the link and choose either all records from Query A or all records from Query B. Here is an often-quoted link:

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

You may wish to look at:
Compare two queries please
thread701-906483

 

[netrusher]

You are right but that is still only going to give me records from on Query that match the other. I want all records from both queries to appear side by side.

 

[Remou]

In that case, all I can think of is a third table or query containg all the references that could occur in each query. You can then join both queries to this table (or query). However, it will not be editable.

 

[netrusher]

I do not know what you mean about joining them to a table or query. Can you explain a little better for me.

 

[Remou]

If both these queries are coming from the same table, you could set up a query:

[tt]Table Queries
SEOCODE - LEFT J. -> QueryA SEOCODE
- LEFT J. -> QueryB SEOCODE[/tt]

If the queries are from two different tables, you can get a set of IDs from a Union Query, for example:
[tt]Select distinct SEOCODE from (SELECT QueryA.SEOCODE
FROM QueryA
UNION
SELECT QueryB.SEOCODE
FROM QueryB) As C;[/tt]

This query could then be used in the place of 'Table'.

 

[PHV]

Something like this ?
SELECT a.SOECODE, b.SOECODE, a.PRODUCT, b.PRODUCT, a.DESCRIPTION, b.DESCRIPTION
FROM Query1 a LEFT JOIN Query2 b ON a.SOECODE = b.SOECODE
UNION SELECT a.SOECODE, b.SOECODE, a.PRODUCT, b.PRODUCT, a.DESCRIPTION, b.DESCRIPTION
FROM Query1 a RIGHT JOIN Query2 b ON a.SOECODE = b.SOECODE
WHERE a.SOECODE IS NULL

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886