Venus De Milo and the Phone Dilemma

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Venus De Milo and the Phone Dilemma

Author

Topic

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-18 : 12:01:15

Gotta little SQL dilemma…..I have a listing of agents from Solar System Travel. Some of them: like Venus De Milo and Pluto Earnhardt have multiple office locations. However, I want a listing of each agent and just 1 phone number. In the case of Venus De Milo who has 2 offices. I’d prefer to have the fist of the office phone listings on my new listing. How can I do this? I know there’s a very simply way to do this, I’m just having a brain-fart month.

Here’s me data!

DROP TABLE [Fowler].[dbo].[Agents]
DROP TABLE [Fowler].[dbo].[Off_Num]

CREATE TABLE [Fowler].[dbo].[Agents]
(Name varchar(20) NULL,Agt_Code varchar (4) NULL)
GO
INSERT Agents VALUES('Fred MERCURY ','MERC')
INSERT Agents VALUES('Venus DE MILO ','DEMI')
INSERT Agents VALUES('Earth WINDANDFIRE ','WIND')
INSERT Agents VALUES('Veronica MARS ','MARS')
INSERT Agents VALUES('Elmo P. JUPITER ','JUPI')
INSERT Agents VALUES('Gladys SATURN ','SATU')
INSERT Agents VALUES('Billy Bob URANUS,III','URAN')
INSERT Agents VALUES('Fowler J. NEPTUNE ','NEPT')
INSERT Agents VALUES('PLUTO Earnhardt ','PLUT')
GO
CREATE TABLE [Fowler].[dbo].[Off_Num]
(Agt_Code varchar (4) NULL, Number varchar(8) NULL)
GO
INSERT Off_Num VALUES('MERC','999-1111')
INSERT Off_Num VALUES('DEMI','888-1111')
INSERT Off_Num VALUES('DEMI','888-0000')
INSERT Off_Num VALUES('WIND','777-1111')
INSERT Off_Num VALUES('MARS','555-1111')
INSERT Off_Num VALUES('JUPI','000-1111')
INSERT Off_Num VALUES('SATU','111-1111')
INSERT Off_Num VALUES('URAN','222-1111')
INSERT Off_Num VALUES('URAN','222-1369')
INSERT Off_Num VALUES('URAN','222-7777')
INSERT Off_Num VALUES('NEPT','333-1111')
INSERT Off_Num VALUES('PLUT','444-1111')
INSERT Off_Num VALUES('PLUT','444-4444')
GO

SELECT * FROM [Fowler].[dbo].[Agents] GO

SELECT * FROM [Fowler].[dbo].[Off_Num] GO

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-18 : 12:43:13

The order of data in a database has no meaning. There is no first.

If you add a datetime column or an IDENTITY column, then you might be able to the MIN() of those and define that as "first". Otherwise it's subjective.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-18 : 12:44:53

Xerxes,

Please post the expected result set that you want using the sample data that you provided. That's the last bit of information we need in order to work on your problem.

Tara

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-18 : 12:44:55

quote:
Originally posted by X002548

The order of data in a database has no meaning. There is no first.

If you add a datetime column or an IDENTITY column, then you might be able to the MIN() of those and define that as "first". Otherwise it's subjective.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

So, Brett, what you're saying is there's no way to grab even one---even if it's not the first?

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-18 : 12:47:37

This might be what you want:

SELECT a.[Name], MIN(o.Number)
FROM Agents a
INNER JOIN Off_Num o
ON a.Agt_Code = o.Agt_Code
GROUP BY a.[Name]
ORDER BY a.[Name]
GO

If it's not, then please post the expected result set.

Tara

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-18 : 12:49:20

quote:
Originally posted by tduggan

Xerxes,

Please post the expected result set that you want using the sample data that you provided. That's the last bit of information we need in order to work on your problem.

Tara

Tara, joining them on Agt_Code won't give me one number per agent. All I want is a listing of agents and just ONE of their office numbers. It doesn't matter which.
Thanks

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-18 : 12:53:19

That's what the GROUP BY and MIN aggregate are for!

I take it you didn't even try the code that I posted.

And where's that expected result set?

Tara

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-08-18 : 14:35:28

Hey Xerxes, this is a little off-topic, but you didn't really name a field Number did you? Tell me that was just for quick demonstration purposes, yeah?

---------------------------
EmeraldCityDomains.com

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-18 : 14:40:02

quote:
Originally posted by tduggan

That's what the GROUP BY and MIN aggregate are for!

I take it you didn't even try the code that I posted.

And where's that expected result set?

Tara

Tara..................what you gave me was EXACTLY what I needed. Obviously, we simply crossed paths at my last posting. (I take it you don't give anyone the benefit of the doubt, huh?

). Thanks so much. I KNEW it was a simple process. Sometimes I just can't get my military mind around it. Thanks AGAIN!!!!!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-18 : 14:42:20

quote:
Originally posted by AjarnMark

Hey Xerxes, this is a little off-topic, but you didn't really name a field Number did you? Tell me that was just for quick demonstration purposes, yeah?

---------------------------
EmeraldCityDomains.com

Of course it was for DEMONSTRATION purposes only. I can't imagine how many problems a field called "Number" could generate for real!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-08-18 : 14:54:03

Good. No offense meant. I guess I've seen too many weird things show up here on the site that I just felt compelled to say something.

Carry on.

---------------------------
EmeraldCityDomains.com

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-18 : 15:23:29

quote:
Originally posted by Xerxes
Obviously, we simply crossed paths at my last posting.

Ummm...No...otherwise you wouldn't have mention of JOIN since there was no discussion BEFORE Tara posted her code.

[quote]
(I take it you don't give anyone the benefit of the doubt, huh?

Hmmmm....with 11k posts, I believe she does, and her inquiry to you was if you tried the code, ya know the one with the JOIN.

trust me, big bold letters come across as screaming btw.....

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-18 : 16:59:39

To: Almighty SQL Goddess Tara, AjarnMark, and Brett (the X002548 number guy)

Please accept my humblest apologies for my seemingly "SCREAMING" responses. Unfortunately, I failed to post smiley faces so that you knew my intent as well. I sincerely appreciate your assistance and your humor. I simply failed to respond in kind: . I should be flogged.....but I think this is a family sort of posting , so we won't go there

No more CAPS!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-18 : 17:08:53

BTW.............in applying the JOIN code, I keep getting this message

Column 'p.CODE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

because I set it up with an 'as': [p.CODE as PCODE], because I need the column for later processing.

Any way around this?

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-18 : 17:21:11

Please post the code that you are using as mine doesn't have p.CODE in it.

Tara

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-18 : 17:26:17

quote:
Originally posted by tduggan

Please post the code that you are using as mine doesn't have p.CODE in it.

Tara

Tara......did you read my apology?

Here is what I'm actually doing:

SELECT a.PAYOR, a.MEMBERNUM, a.NAME, a.ADDR1, a.ADDR2, a.CITY, a.STATE, a.ZIP, a.PCPNAME, a.DOB, a.SEX, a.PHONE, a.CODE, p.CODE as PCPCODE, MIN(p.PHONE) as PCP_PH
INTO Fowler.dbo.tab_MEM_SSC3
FROM Fowler.dbo.tab_MEM_SSC2 a
INNER JOIN Fowler.dbo.look_PCP p
ON a.CODE = p.CODE
GROUP BY a.PAYOR, a.MEMBERNUM, a.NAME, a.ADDR1, a.ADDR2, a.CITY, a.STATE, a.ZIP, a.PCPNAME, a.DOB, a.SEX, a.PHONE, a.CODE, p.CODE, p.PHONE
ORDER BY a.PAYOR, a.MEMBERNUM, a.NAME, a.ADDR1, a.ADDR2, a.CITY, a.STATE, a.ZIP, a.PCPNAME, a.DOB, a.SEX, a.PHONE, a.CODE, p.CODE, p.PHONE
GO

SELECT distinct PAYOR, MEMBERNUM, NAME, ADDR1, ADDR2, CITY, STATE, ZIP, PCPNAME, DOB, SEX, PHONE, CODE,
PCPCODE, PCP_PH
INTO [Fowler].[dbo].[tab_MEM_SSC4]
FROM [Fowler].[dbo].[tab_MEM_SSC3]
GROUP BY PAYOR, MEMBERNUM, NAME, ADDR1, ADDR2, CITY, STATE, ZIP, PCPNAME, DOB, SEX, PHONE, CODE,
PCPCODE, PCP_PH
ORDER BY PAYOR, MEMBERNUM, NAME, ADDR1, ADDR2, CITY, STATE, ZIP, PCPNAME, DOB, SEX, PHONE, CODE,
PCPCODE, PCP_PH
GO

SELECT * FROM [Fowler].[dbo].[tab_MEM_SSC4] GO

Does this make more sense? Of course I get this mysterious item, too!

Warning: Null value is eliminated by an aggregate or other SET operation.

Semper fi,
XERXES, Expert at being Hip-deep in muck, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-18 : 17:30:46

Yep I saw it.

Could you repost your DDL and DML that includes all of the columns that your real query needs? Also, make sure to post the expected result set using this new set of sample data as we need that to provide the correct solution.

Oh and leave out the Fowler in your code as we don't have that on our systems.

Tara

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-18 : 17:49:50

I was sincere about that apology....

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-18 : 18:00:40

Your comment didn't bug me anyway. What bugs me sometimes though is the font and color of your posts sometimes and all of the smilies. It can be very distracting to read your posts sometimes. My opinion of course, not sure if others share it.

Tara

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-18 : 18:42:37

quote:
Originally posted by tduggan

Your comment didn't bug me anyway. What bugs me sometimes though is the font and color of your posts sometimes and all of the smilies. It can be very distracting to read your posts sometimes. My opinion of course, not sure if others share it.

Tara

Tell me, then, Tara, what is preferable/acceptable to you. I am looking to be inoffensive and yet be able to communicate my problem succinctly. I'm open to any pointers you'd like to give me on that. Thanks!

No smilies, no fonts, just plain old arial/verdana/tahoma/whatever.

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-08-18 : 18:51:58

quote:
Originally posted by tduggan

Oh and leave out the Fowler in your code as we don't have that on our systems.

Tara, isn't that getting just a little bit picky? Sure it'd be nice to not have to remove it yourself, but with everything else you're asking for, that just struck me as being over the top. But maybe that's just me.

Anyway, Xerxes, if you comment out the INTO... part of your statements, are you still getting the same error? I'm wondering if that's somehow throwing off the grouping. Perhaps you could switch it to an INSERT...SELECT statement?

The NULL value warning is just letting you know that some of the rows had NULL values in the columns that you are aggregating which looks like is only p.phone. In the course of the aggregation, you lose that detail. You can get rid of that message by doing something like MIN(ISNULL(p.phone,'')

---------------------------
EmeraldCityDomains.com

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources