Solved

SQL Query help required

Posted on 2011-09-08
6
244 Views
Last Modified: 2012-05-12
Hi

I'm trying to return data from a SQL Server databae using the following query:

SELECT id,thing1,ISNULL(Monitor, 0) AS Monitor
FROM Things as t  
LEFT OUTER JOIN ThingRelationships as t  
ON t.ID=t.ChildDeviceID  
WHERE t.AccountID =1
AND r.ParentDeviceID= (27)

Where I want all the things with the account Id of 1 to be returned whether there is a record in the ThingRelationships table or not, with a ParentDeviceID=27.

I'd expect the following, for example.

id    thing1            Monitor
12   something     0
23   something     0
34   something     0
56   something     0
2     something     0
4     something     0
56   something     0
43   something     1

as 43 is currently the only one with a relationship.

Thanks in advance!


0
Comment
Question by:Spike_66
6 Comments
 
LVL 5

Accepted Solution

by:
zvytas earned 500 total points
ID: 36501909
Try the following:

SELECT id,thing1,ISNULL(Monitor, 0) AS Monitor
FROM Things as t  left JOIN ThingRelationships as r ON t.ID=r.ChildDeviceID AND r.ParentDeviceID= (27)
WHERE t.AccountID =1
0
 
LVL 18

Expert Comment

by:deighton
ID: 36501964
you seem mixed up as to what t and r are, there seems to be 2 t's defined, but no r

try something like

SELECT id,thing1,ISNULL(Monitor, 0) AS Monitor
FROM Things as t  
LEFT OUTER JOIN ThingRelationships as r  
ON r.ID=t.ChildDeviceID  
WHERE t.AccountID =1
AND COALESCE(r.ParentDeviceID,27) = 27
0
 
LVL 9

Expert Comment

by:mimran18
ID: 36501985
Hi,
   Both the table is alias as "t". You need to change "Things as t  " or "ThingRelationships as t "
to as "r"
 
SELECT id,thing1,ISNULL(Monitor, 0) AS Monitor
FROM Things as t
LEFT OUTER JOIN ThingRelationships as rON t.ID=r.ChildDeviceID  
WHERE t.AccountID =1
AND r.ParentDeviceID= (27)
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

Author Closing Comment

by:Spike_66
ID: 36502020
Easy when you know how!
0
 

Author Comment

by:Spike_66
ID: 36502091
Not that it matters now but

"...Things as t
LEFT OUTER JOIN ThingRelationships as r..."

both tables aren't define as t
0
 

Author Comment

by:Spike_66
ID: 36502102
Oops- sorry - you were right- both were t- my bad!
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

809 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