Select column twice - 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

General SQL Server Forums

New to SQL Server Programming

Select column twice

Author

Topic

fakesy
Starting Member

2 Posts

Posted - 2013-09-23 : 07:37:33

Any help is great on this - thanks!

Using MS SQL 2008.

I have 2 tables, one with pupil details in (names, dob etc) and another with parental contact detail in).

PK: Pupil_id

Pupils
pupil_id (PK)
name
dob

parents
name
phone_no
pupil_id (FK)
rank (1 is mum, 2 is dad)

I want to write a query that will return this:

pupils.name, puplils.dob, parents(rank 1).name, parents (rank 1). parents (rank 1).phone_no, parents(rank 2).name, parents (rank 2). parents (rank 2).phone_no

all as one record.

Here is my syntax so far:


USE database;
GO
SELECT det.Name, det.DOB, con.name, con.phone
FROM pupils AS det
INNER JOIN parents AS con ON det.pupil_id = con.pupil_id
WHERE con.rank = (1,2)

But obviously that returns pupil and parent info on 2 rows, i want it all in one row.

Many thanks!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-23 : 08:23:19

Join to the parents table twice like shown below:

SELECT  det.Name ,
        det.DOB ,
        con.name AS Parent1Name,
        con.RANK AS Parent1Rank,
        con.phone AS Parent1Phone,
        con2.NAME AS Parent2Name,
        con2.RANK AS Parent2Rank,
        con2.phone AS Parent2Phone
FROM    pupils AS det
        LEFT JOIN parents AS con ON det.pupil_id = con.pupil_id AND con.RANK = 1
        LEFT JOIN parents AS con2 ON det.pupil_id = con2.pupil_id AND con2.RANK = 2

fakesy
Starting Member

2 Posts

Posted - 2013-09-23 : 08:35:34

James you are a legend, I was so close but was using two INNER joins. Why didn't that work.

Many thanks!!!!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-23 : 08:40:14

You are very welcome - glad to help. BTW< no one has ever accused me of being a legend. I plead innocent

The reason for the LEFT JOIN rather than INNER JOIN is that some pupils may not have both parents listed. If you use inner joins, that will show only those pupils who have both parents listed in the table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources