Add a 3rd table and another column to my query

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)

Add a 3rd table and another column to my query

Author

Topic

snots34
Starting Member

12 Posts

Posted - 2014-02-03 : 22:33:20

This is my current query:

SELECT title1, 
    [precinct percent] AS [PrecinctPercent], 
    leader, 
    [leader percent] AS [LeaderPercent], 
    Winner, 
    WinningVotes, 
    leader2, 
    [leader2 percent] AS [Leader2Percent], 
    Loser, 
    LosingVotes 
    FROM dbo.[RACE]  r inner join
     (select rc.[race number],
             max(case when seqnum = 1 then [candidate num] end) as Winner,
             max(case when seqnum = 1 then Votes end) as WinningVotes,
             max(case when seqnum = 2 then [candidate num] end) as Loser,
             max(case when seqnum = 2 then Votes end) as LosingVotes
      from (select rc.*,
                   row_number() over (partition by rc.[race number] order by votes desc) as seqnum
            from dbo.[RACE CANDIDATES] rc
           ) rc
      group by rc.[race number]
     ) rc
     on r.[race number] = rc.[race number]

This outputs the data I need. It puts 2 race candidates into one single rowset. candidate 1 being winner and candidate2 being loser. The winning and losing votes follow each candidate correctly.

What I am now trying to do, is retrieve the PARTY value from a 3rd table named CANDIDATE. I am not exactly sure on how to write the query to just include a reference to this third table, CANDIDATE. Also, I am trying to get the party value (D or R) to follow each candidate, Such as Winner, WinnerVotes, WinnerParty..Loser, LoserVotes, LoserParty...something like that.

Tables CANDIDATE and RACE CANDIDATE are related.

Does anyone have any ideas? I have another field I need to include now too, but I am trying to take it one step at a time.

I have the TABLES and DATABASE Schema available if it would help.

Any input would be appreciated. Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-02-03 : 23:00:09

[code]select rc.[race number],
max(case when seqnum = 1 then [candidate num] end) as Winner,
max(case when seqnum = 1 then Votes end) as WinningVotes,
max(case when seqnum = 1 then party end) as WinningParty,
max(case when seqnum = 2 then [candidate num] end) as Loser,
max(case when seqnum = 2 then Votes end) as LosingVotes,
max(case when seqnum = 2 then party end) as LosingParty
from
(
select rc.[race number],
rc.[candidate num],
rc.[Votes],
c.[party],
row_number() over (partition by rc.[race number] order by votes desc) as seqnum
from dbo.[RACE CANDIDATES] rc
inner join dbo.[CANDIDATES] c on rc.[candidate num] = c.[candidate number]
) rc
group by rc.[race number][/code]

Note : i changed the rc.* to specific column name

KH
[spoiler]Time is always against us[/spoiler]

snots34
Starting Member

12 Posts

Posted - 2014-02-03 : 23:56:43

Hi, this is great!

I had to change one of the candidate num's to candidate number, but the parties showed up.

How do I incorporate my other data? Such as title1, leader percent, precinct percentage? While keeping it all in the same row?

Just curious. I can't wait to get to work tomorrow!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-02-04 : 00:17:06

just add it to the query similarly to those line in red

KH
[spoiler]Time is always against us[/spoiler]

snots34
Starting Member

12 Posts

Posted - 2014-02-04 : 00:26:53

I will need to try it for sure. Thanks!

Adding the additional column query will work even if they are coming from a different table not referenced in your post?

Tables: RACE, RACE CANDIDATES and CANDIDATE.

Thank you very much for your time.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-02-04 : 00:38:33

if it is from different table, just INNER JOIN to it as in the green codes

KH
[spoiler]Time is always against us[/spoiler]

snots34
Starting Member

12 Posts

Posted - 2014-02-04 : 00:41:26

I will let you know what my results are tomorrow.

Thanks again!!!

snots34
Starting Member

12 Posts

Posted - 2014-02-04 : 11:33:45

I have been unable to join the 3rd table. I have tried MANY ways of doing this, but here's what I have that I'd like to post.

select	rc.[race number]
	max(case when seqnum = 1 then [candidate num] end) as Winner,
	max(case when seqnum = 1 then Votes end) as WinningVotes,
	max(case when seqnum = 1 then party end) as WinningParty,
	max(case when seqnum = 2 then [candidate num] end) as Loser,
	max(case when seqnum = 2 then Votes end) as LosingVotes,
	max(case when seqnum = 2 then party end) as LosingParty
from 
(
	select 	rc.[race number],
			rc.[candidate num],
			rc.[Votes],
			c.[party]
                row_number() over (partition by rc.[race number] order by votes desc) as seqnum
        from 	dbo.[RACE CANDIDATES] rc
		inner join dbo.[CANDIDATE] c	on	rc.[candidate num]	= c.[candidate number]
		from	dbo.[RACE CANDIDATES] rc
		inner join dbo.[RACE] on r.[race number] = rc.[race number]			
) rc 
group by rc.[race number]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources