Inner Join w/ Multiple results display in one row

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

SQL Server Development (2000)

Inner Join w/ Multiple results display in one row

Author

Topic

ybechar
Starting Member

8 Posts

Posted - 2008-05-11 : 17:49:58

Hi there,

I have two tables, one USER with information about each user and another USR_PHONES (with all the phone numbers for a single user). So it is a 1 to many relationship. I want to show only the first phone for each user (the unique identifier for USER is CPI). So far I have been trying this query:

SELECT U.CPI, U.DIVISION_ID, U.LASTNAME, UP.PHONE, UP.TYPES, UP.SEQ
FROM dbo.USER AS U INNER JOIN
USR_PHONES AS UP ON UP.CPI =
(SELECT TOP (1) CPI
FROM USR_PHONES AS UP1
WHERE (CPI = U.CPI)
ORDER BY SEQ)

So far, this query is giving me ALL phone numbers instead of just the first one.....

Thanks in advance for your help....
ybechar

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-12 : 00:16:23

[code]SELECT U.CPI, U.DIVISION_ID, U.LASTNAME, UP.PHONE, UP.TYPES, UP.SEQ
FROM dbo.USER AS U INNER JOIN
USR_PHONES AS UP ON UP.CPI = U.CPI
INNER JOIN (SELECT MIN(PHONE)AS FirstPhone,CPI
FROM USR_PHONES
GROUP BY CPI) tmp
ON tmp.CPI=UP.CPI
AND tmp.MinPhone=UP.PHONE[/code]

ybechar
Starting Member

8 Posts

Posted - 2008-05-12 : 10:53:31

Hi visakh16,

Thanks for your reply. It worked well.
The problem is that your suggestion is giving me the min phone. I have the SEQ column that dictates which phone the user prefers. So, I really want the MIN(SEQ). That is why I was trying to get the top (1) order by SEQ before. So I could get the prefer phone number for each USER. Any suggestions on how to incorporate that condition?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-12 : 11:19:44

just change it to MIN(SEQ) then

SELECT U.CPI, U.DIVISION_ID, U.LASTNAME, UP.PHONE, UP.TYPES, UP.SEQ
FROM dbo.USER AS U INNER JOIN
USR_PHONES AS UP ON UP.CPI = U.CPI
INNER JOIN (SELECT MIN(SEQ) AS FirstPhone,CPI
            FROM USR_PHONES
            GROUP BY CPI) tmp
ON tmp.CPI=UP.CPI
AND tmp.MinPhone=UP.SEQ

ybechar
Starting Member

8 Posts

Posted - 2008-05-12 : 12:16:21

Great Thanks!!! That is exactly what I was looking for...

I added:

SELECT U.CPI, U.LASTNAME, UP.PHONE, UP.TYPES
FROM (SELECT MIN(SEQ) AS MinPhone, CPI
FROM USR_PHONES
GROUP BY CPI) AS tmp INNER JOIN
USR_PHONES AS UP ON tmp.CPI = UP.CPI AND tmp.MinPhone = UP.SEQ RIGHT OUTER JOIN
USER AS U ON UP.CPI = U.CPI

To be able to get those user with no phone information as well.

Thank you very much,

gtrmitch
Starting Member

3 Posts

Posted - 2008-05-12 : 15:15:18

I'm pretty much in the same exact situation, I adapted the code to work with my SQL tables, but I need to get it to come back with NULL if there are no phones for the user.
So with the code I have (the bottom two put together), if there is at least one phone for a user, it will come back just great. If there are no phones, it won't come back at all.

So I have

SELECT U.CPI, U.DIVISION_ID, U.LASTNAME, UP.PHONE, UP.TYPES, UP.SEQ
FROM dbo.USER AS U INNER JOIN
USR_PHONES AS UP ON UP.CPI =
(SELECT TOP (1) CPI
FROM USR_PHONES AS UP1
WHERE (CPI = U.CPI)
ORDER BY SEQ)

I tried putting in

SELECT U.CPI, U.LASTNAME, UP.PHONE, UP.TYPES
FROM (SELECT MIN(SEQ) AS MinPhone, CPI
FROM USR_PHONES
GROUP BY CPI) AS tmp INNER JOIN
USR_PHONES AS UP ON tmp.CPI = UP.CPI AND tmp.MinPhone = UP.SEQ RIGHT OUTER JOIN
USER AS U ON UP.CPI = U.CPI

but if there isn't a link between the user table and phone table, it won't display null. It still only comes back with the one.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-12 : 15:18:57

quote:
Originally posted by gtrmitch

I'm pretty much in the same exact situation, I adapted the code to work with my SQL tables, but I need to get it to come back with NULL.

So I have

SELECT U.CPI, U.DIVISION_ID, U.LASTNAME, UP.PHONE, UP.TYPES, UP.SEQ
FROM dbo.USER AS U INNER JOIN
USR_PHONES AS UP ON UP.CPI =
(SELECT TOP (1) CPI
FROM USR_PHONES AS UP1
WHERE (CPI = U.CPI)
ORDER BY SEQ)

I tried putting in

SELECT U.CPI, U.LASTNAME, UP.PHONE, UP.TYPES
FROM (SELECT MIN(SEQ) AS MinPhone, CPI
FROM USR_PHONES
GROUP BY CPI) AS tmp INNER JOIN
USR_PHONES AS UP ON tmp.CPI = UP.CPI AND tmp.MinPhone = UP.SEQ RIGHT OUTER JOIN
USER AS U ON UP.CPI = U.CPI

but if there isn't a link between the user table and phone table, it won't display null. It still only comes back with the one.

Try like this:-

SELECT U.CPI, U.DIVISION_ID, U.LASTNAME, UP.PHONE, UP.TYPES, UP.SEQ
FROM dbo.USER AS U LEFT JOIN
USR_PHONES AS UP ON UP.CPI = U.CPI
INNER JOIN (SELECT MIN(SEQ) AS FirstPhone,CPI
            FROM USR_PHONES
            GROUP BY CPI) tmp
ON tmp.CPI=UP.CPI
AND tmp.MinPhone=UP.SEQ

gtrmitch
Starting Member

3 Posts

Posted - 2008-05-12 : 15:27:05

I'm going to keep trying, but let me start from scratch again.

I have a table with the User Info.
I have a table with different styles (about 8 records)
I have a table that link these.

All I want to do is, get the User, and the associated styles, but I only want one style (it's for searching).

So the code works great if it finds a match in the link table. But if the user ID isn't in the link table (no styles associated), I still want the user info to display, but null in the style column.

SELECT LinkPhoto.Style,person.*
FROM PhotographerAgency AS person LEFT JOIN
tbllinkphotostyle AS LinkPhoto ON LinkPhoto.PhotographerAgency = person.ID
INNER JOIN (SELECT MIN(style) AS style,PhotographerAgency
FROM tbllinkphotostyle
group BY PhotographerAgency) tmp
ON tmp.PhotographerAgency=LinkPhoto.PhotographerAgency
AND tmp.style=LinkPhoto.style

gtrmitch
Starting Member

3 Posts

Posted - 2008-05-12 : 16:33:37

Got it.

SELECT tmp.Style ,PhotographerAgency.* FROM PhotographerAgency LEFT OUTER JOIN
(SELECT MIN(style) AS style,PhotographerAgency
FROM tbllinkphotostyle
group BY PhotographerAgency) tmp
ON PhotographerAgency.ID = tmp.PhotographerAgency

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources