using count with Joins - 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

SQL Server 2008 Forums

Transact-SQL (2008)

using count with Joins

Author

Topic

andrew a
Starting Member

6 Posts

Posted - 2012-04-10 : 17:38:42

Hello, After developing in Filemaker for many years I thought it was about time I tried a bit a SQL. It's not going exactly to plan and I'm struggling with SQL joins.

To expand a little more on why I'm going down this route, I'm inserting some data into another table, there are many more case statements enclosed within this one select statement but they are straight forward decisions based on columns within the same table. I've split it into two tables now to see if it was a self join issue, it's not. I get the same results. I'm obviously not using the correct join statement though I've tried varying combinations of joins to try and sort it out. I've also tried count(*), Count(distinct, expression), count(all, expression)

This operation is straight forward in Filemaker, but I think it's my lack knowledge of how SQL joins two tables together that's the issue here.

Run this and you'll see what I mean. Run as is below, then run with the single commented out parts introduced to see it going wrong. The double commented out parts are just for experimentation and really mess it up but ultimately show it’s the joining that is the issue. Enjoy :-)

use LEIS

CREATE TABLE dbo.LEISprim
( spell bigint not null,
spellindicator tinyint not null,
episode tinyint not null,
);

INSERT INTO dbo.LEISprim (spell, spellindicator, episode)
VALUES (7032357, 1 , 1 )
INSERT INTO dbo.LEISprim (spell, spellindicator, episode)
VALUES (7032357, 2 , 3 )
INSERT INTO dbo.LEISprim (spell, spellindicator, episode)
VALUES (7032357, 1 , 2 )
INSERT INTO dbo.LEISprim (spell, spellindicator, episode)
VALUES (7034826, 1 , 2 )
INSERT INTO dbo.LEISprim (spell, spellindicator, episode)
VALUES (7034826, 2 , 1 )
INSERT INTO dbo.LEISprim (spell, spellindicator, episode)
VALUES (7088650, 1 , 2 )
INSERT INTO dbo.LEISprim (spell, spellindicator, episode)
VALUES (7088650, 1 , 1 )
INSERT INTO dbo.LEISprim (spell, spellindicator, episode)
VALUES (7088650, 2 , 3 )
INSERT INTO dbo.LEISprim (spell, spellindicator, episode)
VALUES (7088650, 1 , 4 )
INSERT INTO dbo.LEISprim (spell, spellindicator, episode)
VALUES (7088650, 2 , 5 )
INSERT INTO dbo.LEISprim (spell, spellindicator, episode)
VALUES (7171003, 1 , 3 )
INSERT INTO dbo.LEISprim (spell, spellindicator, episode)
VALUES (7171003, 2 , 1 )
INSERT INTO dbo.LEISprim (spell, spellindicator, episode)
VALUES (7171003, 2 , 2 )
INSERT INTO dbo.LEISprim (spell, spellindicator, episode)
VALUES (7175553, 1 , 1 )

CREATE TABLE dbo.LEISsecon
( spell bigint not null,
spellindicator tinyint not null,
episode tinyint not null,
fk_spellindicator varchar(9) not null
);

INSERT INTO dbo.LEISsecon (spell, spellindicator, episode, fk_spellindicator)
VALUES (7032357, 1 , 1 , '7032357:1')
INSERT INTO dbo.LEISsecon (spell, spellindicator, episode, fk_spellindicator)
VALUES (7032357, 2 , 3 , '7032357:2')
INSERT INTO dbo.LEISsecon (spell, spellindicator, episode, fk_spellindicator)
VALUES (7032357, 1 , 2 , '7032357:1')
INSERT INTO dbo.LEISsecon (spell, spellindicator, episode, fk_spellindicator)
VALUES (7034826, 1 , 2 , '7034826:1')
INSERT INTO dbo.LEISsecon (spell, spellindicator, episode, fk_spellindicator)
VALUES (7034826, 2 , 1 , '7034826:2')
INSERT INTO dbo.LEISsecon (spell, spellindicator, episode, fk_spellindicator)
VALUES (7088650, 1 , 2 , '7088650:1')
INSERT INTO dbo.LEISsecon (spell, spellindicator, episode, fk_spellindicator)
VALUES (7088650, 1 , 1 , '7088650:1')
INSERT INTO dbo.LEISsecon (spell, spellindicator, episode, fk_spellindicator)
VALUES (7088650, 2 , 3 , '7088650:2')
INSERT INTO dbo.LEISsecon (spell, spellindicator, episode, fk_spellindicator)
VALUES (7088650, 1 , 4 , '7088650:1')
INSERT INTO dbo.LEISsecon (spell, spellindicator, episode, fk_spellindicator)
VALUES (7088650, 2 , 5 , '7088650:2')
INSERT INTO dbo.LEISsecon (spell, spellindicator, episode, fk_spellindicator)
VALUES (7171003, 1 , 3 , '7171003:1')
INSERT INTO dbo.LEISsecon (spell, spellindicator, episode, fk_spellindicator)
VALUES (7171003, 2 , 1 , '7171003:2')
INSERT INTO dbo.LEISsecon (spell, spellindicator, episode, fk_spellindicator)
VALUES (7171003, 2 , 2 , '7171003:2')
INSERT INTO dbo.LEISsecon (spell, spellindicator, episode, fk_spellindicator)
VALUES (7175553, 1 , 1 , '7175553:1')

Select l1.spell, l1.spellindicator, l1.episode

,case
when COUNT(l2.fk_spellindicator) > 1
then 'invalid'
else 'valid'
end LEISCheck

--,case
-- when MAX(e2.episode) > l1.episode
-- then 'not last'
-- else 'last'
-- end lastepisode

,COUNT(l2.fk_spellindicator) indcount

--,maxepisode = MAX(e2.episode)
--,minepisode = MIN(e2.episode)
----,e2.fk_spellindicator

from dbo.LEISprim l1

inner join dbo.LEISsecon as l2
on cast(l1.Spell as nvarchar) + ':1' = l2.fk_spellindicator

--inner join dbo.LEISsecon as e2
--on l1.spell = e2.spell

group by l1.spell, l1.spellindicator, l1.episode
----,e2.fk_spellindicator
order by l1.spell

drop table dbo.LEISprim
drop table dbo.LEISsecon
;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources