Help with SQL query!!!

[bazpaul]

Hey everyone! I have been scouring the internet for help on an SQL query i need to run and have been trying for a day or two now to no avail! Can any SQL heroes please help me??? My problem seems simple but may not be so! In this example i have two tables, with their respective columns beside in brackets! 1. SERVERS (ID, NAME, LOCATION, OS) 2. SERVERS_VM (PHYSICAL_ID, VIRTUAL_ID) The columns ID, PHYSICAL_ID and VIRTUAL_ID are all primary keys! Now consider that the SERVERS table contains lots of servers both physical and virtual each with their own unique primary key labelled ID. While the SERVERS_VM table contains a list of all physical servers that have virtual servers installed on them! So each PHYSICAL_ID in SERVERS_VM might have one or more VIRTUAL_ID's associated with it. Below i will try to give an example; SERVERS ------------------------------------------- ID Name Location o/s ------------------------------------------- 1 Bob12 BobOffice Win Server 23 Ash14 AshOffice Virtual 7 Hank89 Hankoffice Win Server 14 Tom98 Tomoffice Virtual 8 Mark45 Markoffice Win Server 93 Bar123 Baroffice Virtual SERVERS_VM --------------------------- PHYSICAL_ID VIRTUAL_ID --------------------------- 7 23 8 14 7 93 Ok so, now not trying to confuse people, but if we see above, Ash14 tom98 and bar123 are virtual servers (os=virtual) located on physical servers (shown in servers_vm) From SERVERS_VM we can see that server number 7 houses two virtual servers given by their VIRTUAL_ID which is the same as the ID i the SERVERS table! (23 & 93) So heres is the big question? How can i run an SQL query to list all the physical servers that have virtual servers alone installed on them?? The results should be something like; --------------------------------------------- ID NAME VIRTUAL_ID --------------------------------------------- 7 Hank89 23 7 Hank89 93 8 Mark45 14 the problem is the fact that there are two primary keys in the SERVERS_VM table, well even though the PHYSICAL_ID repeats in the SERVERS_VM table this is what i have so far; SELECT PHYSICAL_ID, VIRTUAL_ID FROM (SELECT ID, NAME FROM SERVERS WHERE OS like '%Virtual%') WHERE SERVERS.ID=SERVERS_VM.VIRTUAL_ID i have gone to much more de

[Farhan Noor Qureshi]

I can give you the answer but it sounds like a homework kind of question. So, in my attempt to help you but not give you the answer, here is the magic hint, use Group By, Having and count(*). :-D

---

Farhan Noor Qureshi

[bazpaul]

Thanks Sir, Actually im new in a Job and am learnin SQL on my own, so my knowledge of these different commands is limited. Thanks though, anymore hints would be great!

[Eric Dahlvang]

bazpaul wrote:

So heres is the big question? How can i run an SQL query to list all the physical servers that have virtual servers alone installed on them??

select * from servers where id in(select physical_id from servers_vm)

But, the sql to answer your question is different from the results you wanted. To get this: --------------------------------------------- ID NAME VIRTUAL_ID --------------------------------------------- 7 Hank89 23 7 Hank89 93 8 Mark45 14 You would use something like the following sql:

select s.ID,s.name,p.virtual_id from servers s inner join servers_vm p on s.id = p.physical_id

--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters