Showing the telephone - SQL Server Forums

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)

Showing the telephone

Author

Topic

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2003-08-19 : 04:34:16

SQL is like solving puzzles. Sometimes I just don't see it.

I have users, and they have media (telephone numerbers, e-mail adresses, fax numbers, etc)

I am using the table media not only for Users, but also customers, and creditors, etc.

I have three tables:

Users
Media
UserMedia

Users : UserId, UserName
Media : MediaId, Content, ContentCategorie (where content is the number and Categorie is the kind of number (fax, telephone, e-mail)

UserMedia: ID, User, Media

What I want is make a query which returns a user only once, and with a business telephone if available

SELECT u.UserName, m.Content
FROM User u
LEFT JOIN UserMedia um ON um.User = u.UserId
LEFT JOIN Media m ON m.MediaId = um.Media AND m.ContentCategory = 2
GROUP BY u.Username, m.Content
ORDER BY m.Content DESC

My result would be like

Dick NULL
Henri 12345678
Henri NULL
Carl 67832647
Carl NULL
Michel NULL

Of course I want a name only to show up once, and with a telephone number prefferably.

( m.ContentCategory = 2 is businessphone #)

WHILE writing this I think I got it.

instead of m.Contact I make a subquery like this
(SELECT TOP 1 m.Content FROM Media m JOIN UserContent uc ON uc.Media = m.MediaId WHERE ContentCategory = 2 AND uc.User = u.User)

It's this the way?

Henri

~~~
Success is the ability to go from one failure to another with no loss of enthusiasm

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-08-19 : 05:03:34

Hi there,


SELECT u.UserName, m.Content
FROM User u
JOIN UserMedia um ON um.User = u.UserId 
LEFT JOIN Media m ON m.MediaId = um.Media AND m.ContentCategory = 2
GROUP BY u.Username, m.Content
ORDER BY m.Content DESC

No need for a LEFT JOIN on USERMEDIA.
Also try and avoid keywords such as USER for your column/table names.

------------------------------------------------------------------------------
I enjoy using SQL Server but I am not part of the Microsoft fanboy club! NEVER!!

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2003-08-19 : 05:17:29

thx Amethystium, but this does not the trick. Some users don't have telephones, then no records shows up. And when there's a specific record, the name shows up twice.

My problem is a little more complicated and I only wrote the 'gist' of the problem. I won't use reserved words

Henri

~~~
Success is the ability to go from one failure to another with no loss of enthusiasm

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-08-19 : 06:00:08

Try this:

SELECT u.UserName, MAX(m.Content)
FROM User u
LEFT JOIN UserMedia um ON um.User = u.UserId
LEFT JOIN Media m ON m.MediaId = um.Media AND m.ContentCategory = 2
GROUP BY u.Username
ORDER BY MAX(m.Content) DESC

Owais

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2003-08-19 : 09:15:31

Amethystium,

That's working as should be, thx!!

Henri

~~~
Success is the ability to go from one failure to another with no loss of enthusiasm

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources