Need SQL Query Help: Select on 2nd Table count

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

General SQL Server Forums

New to SQL Server Programming

Need SQL Query Help: Select on 2nd Table count

Author

Topic

RichardMcCarty
Starting Member

3 Posts

Posted - 2009-02-17 : 18:10:33

I'm new to T-SQL and trying to program a simple query:

Table1
Location ID
Flag

Table2 (many to Table1)
Location ID
Code

Select Table1 Location_IDs where the count of Table2's Code=value is 0

Thanks for your help!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-17 : 18:16:55

SELECT t1.LocationID
FROM Table1 AS t1
LEFT JOIN Table2 AS t2 ON t2.LocationID = t1.LocationID
AND t2.Code = @Value
WHERE t2.LocationID IS NULL

E 12°55'05.63"
N 56°04'39.26"

RichardMcCarty
Starting Member

3 Posts

Posted - 2009-02-17 : 18:31:25

Peso... thank you!

Now... one more step to this newbie mystery. I need to insert a row into Table2 where my count is 0.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-17 : 18:35:28

Which row?
I can't mind read that well.

E 12°55'05.63"
N 56°04'39.26"

RichardMcCarty
Starting Member

3 Posts

Posted - 2009-02-17 : 18:49:39

Sorry I'm being vague...

If my count of related records with additional selection criteria is 0 in Table2, I need to enter a record in Table2.

So...

Table1
Location ID
Flag

Table2 (many to Table1)
Location ID
Code

Select Table1 Location_IDs with Flag set
If count of related Table2 rows with Code=value is 0,
then insert a row in Table2

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-18 : 00:53:48

And which code should be used?
Remember, we don't know your business rules...

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources