?
Solved

Joining 2 SQL querys together on the same row

Posted on 2010-01-12
8
Medium Priority
?
289 Views
Last Modified: 2012-05-08
Hi all,

I have 3 tables with the links highlighted:

CustomPollers (PK = CustomPollerid, PollerID)
CustomPollerAssignment (PK = CustomPollerAssignmentid, PollerID)
CustomPollerStatistics_detail (PK = CustomPollerAssignmentid)

I need to get out details from the statistics table and bring back the poller details (from the customerpoller table) - fine.

But I need to get 2 different pollers and thier corresponding status' (eg - Pollername = WilliamHill and RawStatus = 1). The only thing I can think of to join these 2 pollers together as they exist as seperate entities in the CustomPoller table is on the DateTime field that is present in the Statistic Detail table.

As the statistics are recorded (at the same time) it would be similar to (Poller name = WilliamHill datetime = 01012010 rawvalue = WilliamHillPLC and a similar row would be Poller name = PortUP datetime = 01012010 Rawvalue= 1)

I need it to display in a simple table that says WilliamHill = 1 or slam to 2 datasets together and match them on the DateTime field.

Here is what I have done so far:

select nodes.caption, custompollers.uniquename,CustomPollerStatistics_Detail.rawstatus,
custompollers.uniquename, CustomPollerStatistics_Detail.[datetime], CustomPollerStatistics_Detail.*

from CustomPollerStatistics_Detail

inner join CustomPollerAssignment on
CustomPollerStatistics_Detail.custompollerassignmentid = CustomPollerAssignment.custompollerassignmentid

inner join custompollers on
CustomPollerAssignment.pollerid = custompollers.pollerid

inner join nodes on
CustomPollerAssignment.nodeid = nodes.nodeid

where custompollers.uniquename = 'PortUp' and nodes.caption = 'SI2076'

order by CustomPollerStatistics_Detail.[datetime] desc


This retrieves the PortUP field and all that is needed to change is the uniquename = ACSPort for the other poller details.

All we are trying to achieve is to have these side my side where the DateTime field is matching.

Any ideas?

Thanks if you can help
Spin
select nodes.caption, custompollers.uniquename,CustomPollerStatistics_Detail.rawstatus,
custompollers.uniquename, CustomPollerStatistics_Detail.[datetime], CustomPollerStatistics_Detail.*

from CustomPollerStatistics_Detail

inner join CustomPollerAssignment on
CustomPollerStatistics_Detail.custompollerassignmentid = CustomPollerAssignment.custompollerassignmentid

inner join custompollers on 
CustomPollerAssignment.pollerid = custompollers.pollerid

inner join nodes on
CustomPollerAssignment.nodeid = nodes.nodeid

where custompollers.uniquename = 'PortUp' and nodes.caption = 'SI2076'

order by CustomPollerStatistics_Detail.[datetime] desc

Open in new window

0
Comment
Question by:Spin2009
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 41

Expert Comment

by:ralmada
ID: 26295821
Can you post some sample data and the expected result?
0
 
LVL 10

Expert Comment

by:lof
ID: 26295865
try using Common Table Expression like that:
with cte as (
	select nodes.caption, custompollers.uniquename,CustomPollerStatistics_Detail.rawstatus,
	custompollers.uniquename, CustomPollerStatistics_Detail.[datetime], CustomPollerStatistics_Detail.*
	from CustomPollerStatistics_Detail
	inner join CustomPollerAssignment on
	CustomPollerStatistics_Detail.custompollerassignmentid = CustomPollerAssignment.custompollerassignmentid
	inner join custompollers on 
	CustomPollerAssignment.pollerid = custompollers.pollerid
	inner join nodes on
	CustomPollerAssignment.nodeid = nodes.nodeid
	where custompollers.uniquename in ('PortUp', 'ACSPort') and nodes.caption = 'SI2076'
)
select * from (
	select * from cte where uniquename = 'PortUp'
) PortUp
full outer join (
	select * from cte where uniquename = 'ACSPort'
) AcsPort
on PortUp.[Datetime] = AcsPort.[DateTime]
order by PortUp.[datetime] desc

Open in new window

0
 

Author Comment

by:Spin2009
ID: 26302476
Lof:

The column 'uniquename' was specified multiple times for 'cte'.
0
Congratulations! You’re Certified – Now What?

Starting a new career can be overwhelming. Becoming certified in your field of expertise is a great start, but where do you go from here?  Here are some tips to help you on your career journey.

 
LVL 10

Expert Comment

by:lof
ID: 26302632
i don't know your exact table structure. I see that the same field is declared in two rows.
try removing custompollers.uniquename from the second  or third line
if it doesn't help in third use explicit names of columns you need instaed of CustomPollerStatistics_Detail.*
0
 
LVL 41

Expert Comment

by:ralmada
ID: 26304285
Aside from explicitly  declaring the columns from CustomPollerStatistics_Detail, you can also remove part of the where clause there. See below:

with cte as ( 
        select 	nodes.caption, 
		custompollers.uniquename,
		CustomPollerStatistics_Detail.rawstatus, 
		CustomPollerStatistics_Detail.[datetime] 
--		,CustomPollerStatistics_Detail.* 
        from CustomPollerStatistics_Detail 
        inner join CustomPollerAssignment on 
        CustomPollerStatistics_Detail.custompollerassignmentid = CustomPollerAssignment.custompollerassignmentid 
        inner join custompollers on  
        CustomPollerAssignment.pollerid = custompollers.pollerid 
        inner join nodes on 
        CustomPollerAssignment.nodeid = nodes.nodeid 
        where nodes.caption = 'SI2076' 
) 
select * from ( 
        select * from cte where uniquename = 'PortUp' 
) PortUp 
full outer join ( 
        select * from cte where uniquename = 'ACSPort' 
) AcsPort 
on PortUp.[Datetime] = AcsPort.[DateTime] 
order by PortUp.[datetime] desc

Open in new window

0
 
LVL 10

Expert Comment

by:lof
ID: 26304497
ralmada, if you remove the condition from the where and there is more names in the table you will end up with much bigger tables taking part in joins in the CTE itself. I agree that the end result will be similar but the time you will get there will differ.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 26304956
I kind of disagree with you lof, First the result will be the same not similar and also because you're making SQL to filter by the same column twice I would tend to say that your query will be slower. But the best way to be sure is to test it. :)
Also I'm not sure if this is what the asker wanted, that's why I firstly suggested if he could provide some sample data and the expected result.
 
0
 
LVL 10

Accepted Solution

by:
lof earned 2000 total points
ID: 26306475
Hi ralmada.

Happy to see somebody wants to discuss more complicated cases rather than just race answering the obvious ones :)

First let me explain what I exactly meant by [b]similar[/b] results. The number of rows and values in their columns would be exactly the sam but the time needed to produce the result would be different thus to me, the results - outcomes of the query would be slightly differ.

You are right, the best way to test such things is to test them and I probably will do just that. Not tonight but as I don't have much time but hopefully soon. If you are interested in the way I test things have a look at my article about the best way of querying hierarchies.

regards
lof
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question