?
Solved

Only one unique instance of ID field required from query

Posted on 2009-02-21
10
Medium Priority
?
357 Views
Last Modified: 2012-05-06
How can I restrict the query to show only one unique instance of IDMasterdrawings. Right now this query produces over 1000 records because of the joined tables, but the ID field is repeated many times. Ive tried DISTINCT and GROUP BY but I cant seem to get it to work.
SELECT MasterDrawings.IDMasterDrawings, PipeSizesList.PipeSizeID, OptionList.OptionID, WellsiteList.WellsiteID
FROM (((MasterDrawings INNER JOIN MasterPackagesList ON MasterDrawings.IDMasterDrawings = MasterPackagesList.MasterDrawingID) LEFT JOIN OptionList ON MasterDrawings.IDMasterDrawings = OptionList.MasterDrawingID) LEFT JOIN PipeSizesList ON MasterDrawings.IDMasterDrawings = PipeSizesList.MasterDrawingID) LEFT JOIN WellsiteList ON MasterDrawings.IDMasterDrawings = WellsiteList.MasterDrawingID
ORDER BY MasterDrawings.IDMasterDrawings;

Open in new window

0
Comment
Question by:stevejasper
  • 4
  • 3
  • 3
10 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 23702820
If all you need is IDMasterDrawings, then how about:

SELECT DISTINCT MasterDrawings.IDMasterDrawings
FROM (((MasterDrawings INNER JOIN MasterPackagesList ON MasterDrawings.IDMasterDrawings = MasterPackagesList.MasterDrawingID) LEFT JOIN OptionList ON MasterDrawings.IDMasterDrawings = OptionList.MasterDrawingID) LEFT JOIN PipeSizesList ON MasterDrawings.IDMasterDrawings = PipeSizesList.MasterDrawingID) LEFT JOIN WellsiteList ON MasterDrawings.IDMasterDrawings = WellsiteList.MasterDrawingID
ORDER BY MasterDrawings.IDMasterDrawings;
0
 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 400 total points
ID: 23702821
Otherwise you will have to do something like:

SELECT MasterDrawings.IDMasterDrawings, Max(PipeSizesList.PipeSizeID), Max(OptionList.OptionID), Max(WellsiteList.WellsiteID)
FROM (((MasterDrawings INNER JOIN MasterPackagesList ON MasterDrawings.IDMasterDrawings = MasterPackagesList.MasterDrawingID) LEFT JOIN OptionList ON MasterDrawings.IDMasterDrawings = OptionList.MasterDrawingID) LEFT JOIN PipeSizesList ON MasterDrawings.IDMasterDrawings = PipeSizesList.MasterDrawingID) LEFT JOIN WellsiteList ON MasterDrawings.IDMasterDrawings = WellsiteList.MasterDrawingID
GROUP BY MasterDrawings.IDMasterDrawings
ORDER BY MasterDrawings.IDMasterDrawings;


or:

SELECT MasterDrawings.IDMasterDrawings, Min(PipeSizesList.PipeSizeID), Min(OptionList.OptionID), Min(WellsiteList.WellsiteID)
FROM (((MasterDrawings INNER JOIN MasterPackagesList ON MasterDrawings.IDMasterDrawings = MasterPackagesList.MasterDrawingID) LEFT JOIN OptionList ON MasterDrawings.IDMasterDrawings = OptionList.MasterDrawingID) LEFT JOIN PipeSizesList ON MasterDrawings.IDMasterDrawings = PipeSizesList.MasterDrawingID) LEFT JOIN WellsiteList ON MasterDrawings.IDMasterDrawings = WellsiteList.MasterDrawingID
GROUP BY MasterDrawings.IDMasterDrawings
ORDER BY MasterDrawings.IDMasterDrawings;
0
 
LVL 11

Expert Comment

by:mildurait
ID: 23702822
You select statement would seem to include Identity columns from the tables that you are joining on.
a) PipeSizesList.PipeSizeID
b) OptionList.OptionID
c) WellsiteList.WellsiteID
A good place to start would to be  to remove these columns and then try the GROUP BY clause or DISTINCT option.
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 

Author Comment

by:stevejasper
ID: 23703142
OK using Min or Max is halfway there - only works if there are no criteria chosen for the other ID fields. The reason I've included the other table fields is that I want the option to select records based on their values (or leave blank).
0
 
LVL 11

Accepted Solution

by:
mildurait earned 1600 total points
ID: 23703935
Try dropping DISTINCT And GROUP BY Clauses and Adding WHERE clause with IN SELECT Clauses where neccessary.
SELECT IDMasterDrawings
FROM MasterDrawings
WHERE 
  IDMasterDrawrings IN (
    SELECT MasterDrawingID FROM MasterPackagesList WHERE MasterPackagesListID=[value]
  )
 AND 
  IDMasterDrawings IN (
  SELECT MasterDrawingID FROM OptionsList WHERE OptionsID=[value]
  )
  AND
 IDMasterDrawrings IN (
 SELECT MasterDrawingID FROM PipeSizesList=[value]
 )
 AND 
 IDMasterDrawrings IN (
 SELECT MasterDrawingID FROM WellSiteList=[value]
 )
 

Open in new window

0
 
LVL 11

Expert Comment

by:mildurait
ID: 23703943
SteveJasper,
You are familiar with VBA and being able to set row sources / data sources on the fly based on values selected in forms etc- yes?
Mildura IT
0
 

Author Comment

by:stevejasper
ID: 23705236
mildurail - thanks for the idea. THis seems like an elegant solution.
Yes I am familiar with VBA and constructing rowsource on the fly. I'll see if I can get it to work.
0
 

Author Comment

by:stevejasper
ID: 23705368
Still doesnt work. It selects records but there are still duplicate IDMasterdrawings, which is the problem I had in the first place. I tried using DISTINCT and GROUP BY but still gives too many records eg:

SELECT DISTINCT IDMasterDrawings FROM MasterDrawings WHERE IDMasterDrawings IN (SELECT MasterDrawingID FROM PipesizesList WHERE PipesizeID=1) AND IDMasterDrawings IN (SELECT MasterDrawingID FROM WellsiteList WHERE WellsiteID=1) GROUP BY IDMasterDrawings ORDER BY IDMasterDrawings

There are also other fields I need to get for the report besides IDMasterdrawings so even if DISTINCT can be made to work I dont know how to select these additional fields eg:
SELECT DISTINCT IDMasterDrawings, FieldA, FieldB, FieldC ......FROM MasterDrawings ...........

0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 23706003
stevejasper,

It would be useful to see some sample data and/or a sample file.  EE now allows you
to directly upload files to your question.

Please be advised that once you upload a file, it can be publicly accessed, and that it
may not be possible to fully and permanently delete it.  Therefore, be very careful about
posting proprietary, confidential, or other sensitive information.  If necessary, use "fake"
and/or obfuscated data in your sample.

Please note that at present EE restricts uploads to certain file types.  If your file type
does not match those in the list, you can use www.ee-stuff.com instead, which is not
officially an EE site, but is run by people connected to EE.

Regards,

Patrick
0
 

Author Comment

by:stevejasper
ID: 23706373
mildurait
Using IN SELECT -
OK I have it working! I guess the first time I must have got some brackets in the wrong place. But now I definately dont have any duplicate IDs and can get all the fields I need.

Thanks very much for pointing me in the right direction.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

850 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