A simple join - 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

General SQL Server Forums

New to SQL Server Programming

A simple join

Author

Topic

limericklad1974
Starting Member

39 Posts

Posted - 2011-12-06 : 10:44:31

Hi there, I am totally new to SQL and would like help with a simple join

In mysimple database, I have a table called ScoutAssignment, which has a column called MatchID. I also have a table called ScoutingReport which also has a column called MatchID

I would like to write a piece of SQL to tell me how many rows in the Scout Assignment table also have a row in the ScoutingReport table with a similar MatchID.....

Can anyone help please?

Thanks

Damien

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2011-12-06 : 10:46:07

[code]select count(a.*)
from ScoutAssignment a
inner join ScoutingReport b on a.MatchID = b.MatchID[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-06 : 10:47:17

is the relationship between tables one to one?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

limericklad1974
Starting Member

39 Posts

Posted - 2011-12-06 : 10:50:51

quote:

Originally posted by vijayisonly

select count(a.*)
from ScoutAssignment a 
inner join ScoutingReport b on a.MatchID = b.MatchID

when I run this, I get an error
Incorrect syntax near '*'

limericklad1974
Starting Member

39 Posts

Posted - 2011-12-06 : 10:52:36

quote:
Originally posted by visakh16

is the relationship between tables one to one?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sorry, I am totally new to SQL...!!! So I dont fully understand your question.

But let me guess.

In the ScoutAssignment table, there may be multiples rows with the same matchid, and in the ScoutingReport table, there will also be multiple rows with the same matchid

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-06 : 10:52:50

it should be


select count(*)
from ScoutAssignment a 
inner join ScoutingReport b on a.MatchID = b.MatchID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-06 : 10:55:02

quote:
Originally posted by limericklad1974

quote:
Originally posted by visakh16

is the relationship between tables one to one?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sorry, I am totally new to SQL...!!! So I dont fully understand your question.

But let me guess.

In the ScoutAssignment table, there may be multiples rows with the same matchid, and in the ScoutingReport table, there will also be multiple rows with the same matchid

ok that was my point
in that case you cant just take count(*) as it will be much more than actual record count. then what you need is this


SELECT COUNT(MatchID)
FROM ScoutAssignment a
WHERE EXISTS (SELECT 1 FROM ScoutingReport WHERE MatchID= a.MatchID)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

limericklad1974
Starting Member

39 Posts

Posted - 2011-12-06 : 10:57:26

quote:
Originally posted by visakh16

it should be

select count(*)
from ScoutAssignment a
inner join ScoutingReport b on a.MatchID = b.MatchID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ok thats great, many thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-06 : 11:02:36

quote:
Originally posted by limericklad1974

quote:
Originally posted by visakh16

it should be

select count(*)
from ScoutAssignment a
inner join ScoutingReport b on a.MatchID = b.MatchID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ok thats great, many thanks!

please use the latest query posted as your scenario is not a one-to-one relationship

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

limericklad1974
Starting Member

39 Posts

Posted - 2011-12-06 : 11:07:50

quote:
Originally posted by visakh16

quote:
Originally posted by limericklad1974

quote:
Originally posted by visakh16

it should be

select count(*)
from ScoutAssignment a
inner join ScoutingReport b on a.MatchID = b.MatchID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ok thats great, many thanks!

please use the latest query posted as your scenario is not a one-to-one relationship

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sorry, which query should I use? I am confused!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-06 : 11:09:19

[code]
SELECT COUNT(MatchID)
FROM ScoutAssignment a
WHERE EXISTS (SELECT 1 FROM ScoutingReport WHERE MatchID= a.MatchID)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

limericklad1974
Starting Member

39 Posts

Posted - 2011-12-06 : 11:23:16

quote:
Originally posted by visakh16

SELECT COUNT(MatchID)
FROM ScoutAssignment a
WHERE EXISTS (SELECT 1 FROM ScoutingReport WHERE MatchID= a.MatchID)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

In the ScoutAssignment table and the ScoutingReport table, there is also a ScoutID column

So two people could be assigned to the match (i.e. there will be two rows in the ScoutAssignment table) but if only 1 person writes a report, there will only be 1 row in the ScoutingReport table

Will your code show this? Your code should find 1 row.....

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-12-06 : 11:39:03

I was hoping you were going to post your question in limerick form

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-06 : 11:56:05

quote:
Originally posted by limericklad1974

quote:
Originally posted by visakh16

SELECT COUNT(MatchID)
FROM ScoutAssignment a
WHERE EXISTS (SELECT 1 FROM ScoutingReport WHERE MatchID= a.MatchID)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

In the ScoutAssignment table and the ScoutingReport table, there is also a ScoutID column

So two people could be assigned to the match (i.e. there will be two rows in the ScoutAssignment table) but if only 1 person writes a report, there will only be 1 row in the ScoutingReport table

Will your code show this? Your code should find 1 row.....

yep my code will give count as 1 as EXISTS condition excludes person who has not written report

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

limericklad1974
Starting Member

39 Posts

Posted - 2011-12-06 : 12:04:48

quote:
Originally posted by visakh16

quote:
Originally posted by limericklad1974

quote:
Originally posted by visakh16

SELECT COUNT(MatchID)
FROM ScoutAssignment a
WHERE EXISTS (SELECT 1 FROM ScoutingReport WHERE MatchID= a.MatchID)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

In the ScoutAssignment table and the ScoutingReport table, there is also a ScoutID column

So two people could be assigned to the match (i.e. there will be two rows in the ScoutAssignment table) but if only 1 person writes a report, there will only be 1 row in the ScoutingReport table

Will your code show this? Your code should find 1 row.....

yep my code will give count as 1 as EXISTS condition excludes person who has not written report

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sorry I made a change. In your Select statement, I changed it to Select assignmentID, MatchID,ScoutID

I have added two rows to the ScoutAssignment table
AssignmentID = 1 MatchID = 100 ScoutID = 1000
AssignmentID = 2 MatchID = 100 ScoutID = 1001

I added 1 row to the ScoutingReport table
ID = 1 MatchID = 100 ScoutID = 1000

Your code returned two rows
MatchID = 100
MatchID = 100

It should only return 1 row but the code is currently returning two rows

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-06 : 12:13:19

that because of your tweaking. I didnt had any clue that you had other conditions too

then it should be


SELECT COUNT(MatchID)
FROM ScoutAssignment a
WHERE EXISTS (SELECT 1 FROM ScoutingReport WHERE MatchID= a.MatchID AND ID= a.AssignmentID AND ScoutID = a.ScoutID)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

limericklad1974
Starting Member

39 Posts

Posted - 2011-12-06 : 12:19:24

quote:
Originally posted by visakh16

that because of your tweaking. I didnt had any clue that you had other conditions too
then it should be

SELECT COUNT(MatchID)
FROM ScoutAssignment a
WHERE EXISTS (SELECT 1 FROM ScoutingReport WHERE MatchID= a.MatchID AND ID= a.AssignmentID AND ScoutID = a.ScoutID)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ok, sorry for this.....

Here is the SQL I currently have

SELECT AssignmentID, MatchID, ScoutID
FROM ScoutAssignment a
WHERE EXISTS (SELECT 1 FROM ScoutingReport WHERE MatchID= a.MatchID AND ID= a.AssignmentID AND ScoutID = a.ScoutID)
Order by assignmentID

This currently returns no rows!

I am hoping it will show 1 row, to reflect the fact that there are two rows in my ScoutAssignment table and 1 row in the ScoutingReport table which has a similar MatchID and ScoutID as one of the rows in the Scout Assignment table

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-06 : 12:21:45

that means ID is not equal to AssignmentID.
what does this return?


SELECT AssignmentID, MatchID, ScoutID
FROM ScoutAssignment a
WHERE EXISTS (SELECT 1 FROM ScoutingReport WHERE MatchID= a.MatchID AND ScoutID = a.ScoutID)
Order by assignmentID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

limericklad1974
Starting Member

39 Posts

Posted - 2011-12-06 : 12:32:48

quote:
Originally posted by visakh16

that means ID is not equal to AssignmentID.
what does this return?

SELECT AssignmentID, MatchID, ScoutID
FROM ScoutAssignment a
WHERE EXISTS (SELECT 1 FROM ScoutingReport WHERE MatchID= a.MatchID AND ScoutID = a.ScoutID)
Order by assignmentID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Yes thats it.... I am now getting 1 row returned

Can I also ask your help to entend the query one last time?

Each Match is stored in a table, with an ID column and a Date colum

I have a table called Weeks, which has a column called ID, a column called Name, a column called StartDate and a column called EndDate

Is it possible to add some code to the above query, that would allow me to only find the rows which related to matches played in a certain week... for instance, I know that week 4 could be Jan 24th to Jan 30th

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-06 : 12:47:03

[code]
SELECT a.AssignmentID, a.MatchID, a.ScoutID
FROM ScoutAssignment a
INNER JOIN MatchTable m
ON m.MatchID = a.MatchID
INNER JOIN Weeks w
ON m.Date BETWEEN w.StartDate AND w.EndDate
AND w.Week = @yourWeek
WHERE EXISTS (SELECT 1 FROM ScoutingReport WHERE MatchID= a.MatchID AND ScoutID = a.ScoutID)
Order by assignmentID
[/code]

pass any value to @YourWeek for getting required result. Also declare it as a variable/parameter before the query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

limericklad1974
Starting Member

39 Posts

Posted - 2011-12-06 : 13:13:40

quote:
Originally posted by visakh16

SELECT a.AssignmentID, a.MatchID, a.ScoutID
FROM ScoutAssignment a
INNER JOIN MatchTable m
ON m.MatchID = a.MatchID
INNER JOIN Weeks w
ON m.Date BETWEEN w.StartDate AND w.EndDate
AND w.Week = @yourWeek
WHERE EXISTS (SELECT 1 FROM ScoutingReport WHERE MatchID= a.MatchID AND ScoutID = a.ScoutID)
Order by assignmentID

pass any value to @YourWeek for getting required result. Also declare it as a variable/parameter before the query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Seems to work prefectly, many many thanks!!

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources