?
Solved

Select Rows In Table Where Another Table Contains?

Posted on 2011-10-27
9
Medium Priority
?
314 Views
Last Modified: 2012-08-14
Seems like a simple query, but I'm not sure how to use only 'Exists'...

Select Domain,Computer,Log
 From Logs
 Where Exists (Select Alert From Alerts Where Alert = 1)
 
[Logs table]
Domain
Computer
Log

[Alerts table]
Domain
Computer
Alert

0
Comment
Question by:pointeman
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 21

Accepted Solution

by:
JestersGrind earned 1000 total points
ID: 37037582
You have to correlate the subquery with the main query.  Try this.

Select Domain,Computer,Log
 From Logs a
 Where Exists (Select Alert From Alerts b Where a.Domain = b.Domain AND a.Computer = b.Computer AND Alert = 1)
 
Greg

0
 
LVL 3

Assisted Solution

by:hspoulsen
hspoulsen earned 500 total points
ID: 37037588
I think I would do something like this:

Select L.Domain,L.Computer,L.Log
From Logs L
inner join Alerts A on L.Domain=A.Domain and L.Computer=A.Computer and A.Alert=1

If your tables are big, you need proper indexing in order to get a fast reply.
If you need help with this, you need to provide more information like correct column types.  

HIH,
Henrik
0
 
LVL 5

Assisted Solution

by:mcs0506
mcs0506 earned 500 total points
ID: 37037589
Hi,
Can you please tell me the purpose of the query?
If you are going to join both tables then you can use following query

Select Logs.Domain,Logs.Computer,Logs.Log
 From Logs
Inner join Alerts ON Alerts .Domain=Logs.Domain AND Alerts .Computer=Logs.Computer
Where Alerts.Alert = 1

regards
Dani
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 21

Assisted Solution

by:JestersGrind
JestersGrind earned 1000 total points
ID: 37037590
One more thing to add.  You don't need to return Alert in your sub query.  Because it's solely checking for existence, it isn't really returning anything.  So, (SELECT * FROM Alert... or (SELECT 1 FROM Alert...  are both acceptable.

Greg

0
 
LVL 3

Expert Comment

by:hspoulsen
ID: 37037596
I think my suggestion will compile to the same execution plan as the suggestion by JertersGrind
0
 

Author Comment

by:pointeman
ID: 37037851
No success with any code examples. This is the problem I'm having as well, I don't get it...
0
 
LVL 5

Expert Comment

by:mcs0506
ID: 37037888
what is your required output from this query?

Dani
0
 

Author Comment

by:pointeman
ID: 37037930
Alerts table has 2 = True and 30 = False
Log table has 500 entries

The return needs to be all Domain and Computer also listed in Alerts table where Alerts.Alert = 1 or True.

Kinda hard to explain...
0
 

Author Closing Comment

by:pointeman
ID: 37041208
My mistake they do work fine!
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

807 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