Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Joining 2 SQL querys together on the same row

Posted on 2010-01-12
8
Medium Priority
?
290 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
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

578 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