quote:

---

Originally posted by Kristen

Homework?

---

quote:

---

Originally posted by RyanRandall

You're testing yourself by getting someone else to give you the answer?

Show us what you've tried...

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

---

quote:

---

Originally posted by chiragkhabaria

oks just a hints
look out for left outer joins and inner joins in Book online i.e. SQL help file


If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

---

quote:

---

Originally posted by RyanRandall

Oh, and why is this URGENT if it's just a personal test?

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

---

Declare @TblFirm Table 
(
ID Int,
Name Varchar(20)
)
Insert @TblFirm 
Select 1, 'Sony' Union All 
Select 2,'Panasonic' Union All 
Select 3, 'Samsung'

Declare @TblPhone Table 
(
FirmID int, 
phone varchar(10)
)
Insert @TblPhone 
Select 1, '88347' Union All 
Select 1, '12454' Union All 
Select 2, '89092'

Select A.Name,IsNull(b.Phone,'') From 
@TblFirm a Left Outer Join (Select FirmID,Max(phone) phone From @Tblphone b Group by FirmID ) as b
on a.id = b.firmid
--Group by b.phone 
 
--Or 

Select A.Name,Max(IsNull(b.Phone,'')) From 
@TblFirm a Left Outer Join @Tblphone b
on a.id = b.firmid
Group by A.Name

quote:

---

Originally posted by mr_mist

What have you come up with so far? You won't learn anything if people just tell you what to do.

-------
Moo. :)

---

quote:

---

Originally posted by RyanRandall

Okay - good to see you've made an effort there. Well done!

Here are my (various) 'answers' (using in, exists or left outer joins). Good luck with your friend

--data
declare @Firms table (ID int, Name varchar(10))
insert @Firms
          select 1, 'Sony'
union all select 2, 'Panasonic'
union all select 3, 'Samsung'

declare @Phones table (FirmID int, Phone varchar(10))
insert @Phones
          select 1, '88347'
union all select 1, '12454'
union all select 2, '89092'

--calculation

--what is the SQL query that displays the firm name with it's phone number, 
--and if a firm has 2 phone numbers it should display only one, 
--and if a firm has no number it should display it with the phonefield emply or stroke out? 
select f.Name, isnull(max(p.Phone), '') as Phone from @Firms f left outer join @Phones p on f.ID = p.FirmID group by f.Name
select f.Name, isnull(min(p.Phone), '') as Phone from @Firms f left outer join @Phones p on f.ID = p.FirmID group by f.Name

--What is the query to return only the firms who have no phone number?
select * from @Firms where ID not in (select FirmID from @Phones)
select f.* from @Firms f where not exists (select * from @Phones p where f.ID = p.FirmID)
select f.* from @Firms f left outer join @Phones p on f.ID = p.FirmID where FirmID is null

--What is the query to return the firms who have not less than 2 phone numbers
select * from @Firms where ID in (select FirmID from @Phones group by FirmID having count(*) >= 2)
select f.* from @Firms f where exists (select * from @Phones p where f.ID = p.FirmID group by FirmID having count(*) >= 2)
select f.* from @Firms f left outer join @Phones p on f.ID = p.FirmID group by f.ID, f.Name having count(*) >= 2

--What is the query to return the firms who have less than 2 phone numbers
select * from @Firms where ID not in (select FirmID from @Phones 


group by FirmID having count(*) >= 2)
select f.* from @Firms f where not exists (select * from @Phones p where f.ID = p.FirmID group by FirmID having count(*) >= 2)
select f.* from @Firms f left outer join @Phones p on f.ID = p.FirmID group by f.ID, f.Name having count(*) < 2

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

---

quote:

---

Originally posted by Kristen

"and if a firm has 2 phone numbers it should display only one"

Bit tricky this as the question doesn't say which, of the multiple entries, should be shown. e.g. Minimum number, random choice, etc.

Tell your friend that its a lousy question and just the sort of useless specification that my clients give me - no chance of guessing what they actually want!

Kristen

---

quote:

---

Originally posted by Kristen

"Random choice"

Hehehe - RyanRandall you'll have the change the MAX() and MIN() solutions to a crafty TOP 1 method instead - with ORDER BY NewID() for good measure!

---