If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
union query without "blank" recordsl
I have a union query between 2 related tables, and where they may be records
in one table [FixtureTypes] with no value in the other [FixtureCatalogsPages ]. I've got this (below) query written, which successfully joins the 2; however, it still provides a record when there is no match. I want the query to provide ONLY records for where there is a match between the 2 tables. I suspect I should be using some sort of property or filter? SELECT FixtureTypes.Type, FixtureTypes.Manufacturer, FixtureTypes.CatalogNo AS CatalogNumber, FixtureCatalogsPages.CatalogSheetLink, FixtureCatalogsPages.printOrder FROM FixtureTypes LEFT JOIN FixtureCatalogsPages ON (FixtureTypes.CatalogNo = FixtureCatalogsPages.CatalogNumber) AND (FixtureTypes.Manufacturer = FixtureCatalogsPages.Manufacturer); any thoughts and suggestions will be greatly appreciated in advance, thanks, mark |
#2
|
|||
|
|||
union query without "blank" recordsl
Mark Kubicki wrote:
I have a union query between 2 related tables, and where they may be records in one table [FixtureTypes] with no value in the other [FixtureCatalogsPages ]. I've got this (below) query written, which successfully joins the 2; however, it still provides a record when there is no match. I want the query to provide ONLY records for where there is a match between the 2 tables. I suspect I should be using some sort of property or filter? SELECT FixtureTypes.Type, FixtureTypes.Manufacturer, FixtureTypes.CatalogNo AS CatalogNumber, FixtureCatalogsPages.CatalogSheetLink, FixtureCatalogsPages.printOrder FROM FixtureTypes LEFT JOIN FixtureCatalogsPages ON (FixtureTypes.CatalogNo = FixtureCatalogsPages.CatalogNumber) AND (FixtureTypes.Manufacturer = FixtureCatalogsPages.Manufacturer); That is NOT a union query. A union query has more than one SELECT statemenr with UNION or UNION ALL between them. To avoid retrieving records from FixtureTypes when there is no matching record in FixtureCatalogsPages, change the LEFT JOIN to INNER JOIN -- Marsh MVP [MS Access] |
#3
|
|||
|
|||
union query without "blank" recordsl
That is not a UNION query. It is a joined query.
As a guess, try changing LEFT JOIN to INNER JOIN and see if that gives you the desired results. The LEFT JOIN will return all records in the first table and any matching data in the second table. If there is no matching data in the second table then it will return blanks for all the fields in the second table. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Mark Kubicki wrote: I have a union query between 2 related tables, and where they may be records in one table [FixtureTypes] with no value in the other [FixtureCatalogsPages ]. I've got this (below) query written, which successfully joins the 2; however, it still provides a record when there is no match. I want the query to provide ONLY records for where there is a match between the 2 tables. I suspect I should be using some sort of property or filter? SELECT FixtureTypes.Type, FixtureTypes.Manufacturer, FixtureTypes.CatalogNo AS CatalogNumber, FixtureCatalogsPages.CatalogSheetLink, FixtureCatalogsPages.printOrder FROM FixtureTypes LEFT JOIN FixtureCatalogsPages ON (FixtureTypes.CatalogNo = FixtureCatalogsPages.CatalogNumber) AND (FixtureTypes.Manufacturer = FixtureCatalogsPages.Manufacturer); any thoughts and suggestions will be greatly appreciated in advance, thanks, mark |
#4
|
|||
|
|||
union query without "blank" recordsl
Hello Mark.
Mark Kubicki wrote: I have a union query between 2 related tables, and where they may be records in one table [FixtureTypes] with no value in the other [FixtureCatalogsPages]. I've got this (below) query written, which successfully joins the 2; however, it still provides a record when there is no match. I want the query to provide ONLY records for where there is a match between the 2 tables. I suspect I should be using some sort of property or filter? SELECT FixtureTypes.Type, FixtureTypes.Manufacturer, FixtureTypes.CatalogNo AS CatalogNumber, FixtureCatalogsPages.CatalogSheetLink, FixtureCatalogsPages.printOrder FROM FixtureTypes LEFT JOIN FixtureCatalogsPages ON (FixtureTypes.CatalogNo = FixtureCatalogsPages.CatalogNumber) AND (FixtureTypes.Manufacturer = FixtureCatalogsPages.Manufacturer); any thoughts and suggestions will be greatly appreciated in advance, thanks, mark You mean that you're joining two tables in a query, this is not a union query. For some reason, you have chosen to use a LEFT JOIN, which returns all rows from the left side (FixtureTypes), also those with no matching record in the right table (FixtureCatalogsPages). To only display the matching rows, change the LEFT JOIN to a simple INNER JOIN. -- Regards, Wolfgang |
#5
|
|||
|
|||
union query without "blank" recordsl
Use INNER join instead of LEFT.
-- Build a little, test a little. "Mark Kubicki" wrote: I have a union query between 2 related tables, and where they may be records in one table [FixtureTypes] with no value in the other [FixtureCatalogsPages ]. I've got this (below) query written, which successfully joins the 2; however, it still provides a record when there is no match. I want the query to provide ONLY records for where there is a match between the 2 tables. I suspect I should be using some sort of property or filter? SELECT FixtureTypes.Type, FixtureTypes.Manufacturer, FixtureTypes.CatalogNo AS CatalogNumber, FixtureCatalogsPages.CatalogSheetLink, FixtureCatalogsPages.printOrder FROM FixtureTypes LEFT JOIN FixtureCatalogsPages ON (FixtureTypes.CatalogNo = FixtureCatalogsPages.CatalogNumber) AND (FixtureTypes.Manufacturer = FixtureCatalogsPages.Manufacturer); any thoughts and suggestions will be greatly appreciated in advance, thanks, mark . |
Thread Tools | |
Display Modes | |
|
|