Sql Query Help - 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

Sql Query Help

Author

Topic

caravanpunk
Starting Member

20 Posts

Posted - 2012-01-14 : 11:04:07

I have a table Person--Stores (Employee SSN and Dependent SSN)-column name SSN) PK PID

A table Employee contains Employee Info-- EID Pk
Person and Employee Joined by Person.Pid=Employee.EID

A table Dependent contains Dependent Info with PID FK.
Person.Pid=Dependent.PID

I want to generate output as follows--
Select (Person.SSN),(Person.SSN) from Person Join Employee on
Person.PID=Employee.Eid join Dependent on Person.PID=Dependent.PID

Would the above query return Employee SSN on both fields or return Employee SSN on one field and Dependent SSN on other??

What should be a best solution??

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-14 : 11:47:10

it returns person SSN twice

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

caravanpunk
Starting Member

20 Posts

Posted - 2012-01-14 : 11:53:33

So what should be the best solution???

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-14 : 11:57:29

whats solution that you're looking at?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-14 : 11:57:58

Change one of them to reference the correct table, Dependent instead of Person. (and take the brackets out of the select clause, they're no necessary)

--
Gail Shaw
SQL Server MVP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-14 : 12:00:12

Select (Person.SSN),(Dependent.SSN) from Person Join Employee on
Person.PID=Employee.Eid join Dependent on Person.PID=Dependent.PID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-14 : 12:37:51

and learn how to use aliases. they make your code a lot easier to read. (and shorter to write). this is a re-working of visakh16's example:


SELECT
    p.[SSN] AS [PersonSSN]
    , d.[SSN] AS [DependantSSN]
FROM
    Employee AS e
    JOIN Person AS p ON p.[PID] = e.[EID]
    JOIN Dependant AS d ON d.[PID] = p.[PID]

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

caravanpunk
Starting Member

20 Posts

Posted - 2012-01-14 : 12:57:11

Yes, this was what I was looking for---aliases

Thank you all

caravanpunk
Starting Member

20 Posts

Posted - 2012-01-16 : 12:00:16

SELECT p.[SSN] AS [PersonSSN],d.[SSN] AS [DependantSSN]
FROM Employee AS e
JOIN Person AS p ON p.[PID] = e.[EID]
JOIN Dependant AS d ON d.[PID] = p.[PID]

and
Select (Person.SSN),(Dependent.SSN) from Person Join Employee on
Person.PID=Employee.Eid join Dependent on Person.PID=Dependent.PID

returns only if Dependent has a field called SSN, Right???

But My Problem is, SSN of Employee and SSN of Dependent is both recorded in the Person table.

The Output that I want from the Query is generating
select P.SSN/*SSN Of Employee*/,D.SSN/*SSN of Dependent*/ from
Person P,Person D,Employee E,Dependent De where
P.PID=E.Eid and D.PID=De.PID

-- I dont want to use the above query for Join--Just using Normal Joins in order to get the Output.

Any further help would be much appreciated.

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-16 : 12:07:39

Does employee have dependent id for getting dependent detail?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

caravanpunk
Starting Member

20 Posts

Posted - 2012-01-16 : 12:26:27

Employee Table
(EmployeeID pk, EID FK)

Person
(PID PK, FName,MName,LName,SSN)
Includes Information of Both Employee/Dependent

Elect Table
(ElectID PK,EID FK,DepelectID FK)
(This table includes information of Employee's Plan Data)

Dependent
(DepelectID PK,ElectID FK,PID FK)
(This table includes information of Dependent Plan Data)

Employee and Dependent is connected through Elect Table

My Problem is getting Output as
EmployeeSSN DependentSSN

But both SSN resides in Person Table

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-16 : 12:38:22

hmm...doesnt look like relationship is correct
howzz employee related to person then?
only through dependent?
i dont think you've posted all columns present in tables

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

caravanpunk
Starting Member

20 Posts

Posted - 2012-01-16 : 12:42:29

Employee is Connected to Person Through EID=PID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-16 : 12:47:30

then here you go


SELECT p1.SSN AS EmpSSN ,p2.SSN AS DeptSSN
FROM EMployee e
JOIN Person p1
ON p1.PID = e.EID
JOIN Elect el
ON el.EID = e.EID 
JOIN Dependent d
ON d.ElectID = el.ElectID 
JOIN Person p2
ON p2.PID = d.PID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

caravanpunk
Starting Member

20 Posts

Posted - 2012-01-16 : 12:54:52

This works great!!!

Thank you for you help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-16 : 12:58:13

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources