Select ??? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Select ???

Author

Topic

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2002-08-05 : 09:49:24

Is there a way to select distinct records from a table and get an occurance count using the same select statement?

Mike B

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-08-05 : 10:04:03

Yes....use a GROUP BY clause....


select
   col1,
   count(47)
from
   mytable
group by 
   col1

Jay White
{0}

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2002-08-05 : 10:31:28

Thanks for the reply. I will have to try a different way I think I am using c++, Ado, Access database and I didn't look at it close enough before posting the question. New question though:

I want to select the distinct records from one table base on field values in two tables.


Table 1
ProjectID | ComponentID | ComponentType

Table 2
ProjectID | ComponentID | IssuedDate | InPour

I need to select the distinct ComponentID's from Table2 where the ProjectID = 'SomeVal', ComponentID = 'SomeVal', IssuedDate IS NOT NULL, InPour = FALSE, and finally ComponentType = SomeVal

I should be able to do this using a join but I am not sure how this works:


SELECT DISTINCT Table2.ComponentID
FROM Table2 LEFT JOIN Table1 
  ON Table2.ProjectID = Table1.ProjectID
  AND Table2.ComponentID = Table1.ComponentID
WHERE Table2.ProjectID = 'SomeVal'
  AND Table2.IssuedDate IS NOT NULL
  AND Table2.InPour = False
  AND Table1.TypeName = SomeVal

I tried this and got an error message "Syntax error in Join"

Any help?
Mike B

<edit>to fix display problem</edit>

Edited by - robvolk on 08/05/2002 10:48:58

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2002-08-05 : 11:34:05

Access is a bit picky.

SELECT DISTINCT Table2.ComponentID
FROM Table2 LEFT outer JOIN Table1
ON (Table2.ProjectID = Table1.ProjectID
AND Table2.ComponentID = Table1.ComponentID)
WHERE Table2.ProjectID = 'SomeVal'
AND Table2.IssuedDate IS NOT NULL
AND Table2.InPour = False
AND Table1.TypeName = SomeVal

Why an outer join?

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2002-08-05 : 12:13:42

Thank you, I will try the new SQL statement. I just looked up the answer to your question "why outer join?" and I was able to assume the correct answer from what I read.

You used outer join because the inner join only combines the fields from the 2 tables that match, where outer join will include fields that have no matching fields in the second table. This I did not know and I am actually glad you made me look it up!

Mike B

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2002-08-05 : 12:18:04

LarsG, I tried the SQL Statement and I now get the following error:


{"No value given for one or more required parameters." (1)}

The following is the actual SQL statement I use:


csQuery.Format("SELECT DISTINCT tblStructuralSchedule.ComponentID
                FROM tblStructuralSchedule LEFT outer JOIN tblStructuralComponents
                ON (tblStructuralSchedule.ProjectID = tblStructuralComponents.ProjectID
                AND tblStructuralSchedule.ComponentID = tblStructuralComponents.ComponentID)
                WHERE tblStructuralSchedule.ProjectID = '%s'
                AND tblStructuralSchedule.IssuedDate IS NOT NULL
                AND tblStructuralSchedule.InPour = False
                AND tblStructuralComponents.TypeName = '%s'", 
                csProject, csType);

I assure you the csProject, and csType values are valid. I don't know what else to look at

Mike B

Edited by - MikeB on 08/05/2002 12:19:15

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2002-08-05 : 12:29:55

The error indicates that there is some columnname in your statement that is incorrect. Could you test the resulting query in Access? Access would prompt for the missing values and thus you could get an indication of which name that are faulty.

It can also be that you have missed some quotes for the second someval

SELECT DISTINCT Table2.ComponentID
FROM Table2 LEFT outer JOIN Table1
ON (Table2.ProjectID = Table1.ProjectID
AND Table2.ComponentID = Table1.ComponentID)
WHERE Table2.ProjectID = 'SomeVal'
AND Table2.IssuedDate IS NOT NULL
AND Table2.InPour = False
AND Table1.TypeName = 'SomeVal'

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2002-08-05 : 12:42:34

Very smart LarsG. You helped a great deal!!!!!!

The problem was in the SQL statement field name TypeName should have been Type. Amazing how many times you can look at something and make the same mistake every time :).I am really trying hard not to duplicate data. I know this is one of the "GOLDEN" rules! :)

Mike B

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources