Help with hard query - 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

Help with hard query

Author

Topic

vertroa
Starting Member

3 Posts

Posted - 2012-04-26 : 10:14:59

Hello, I am really new to SQL and am having trouble figuring out this query I have to complete. I have been working on it for a hour and I am completely lost. So help would be very much appreciated.

List all of the Trainee's Name, City, State and Phone Number from the Trainee table for any pair of Trainees who live in the same city and state and worked for the same companies. Note: Only list the city and state once.

Table Structure:
Trainee_TBL (TraineeID, LastName, FirstName, PermStreet, PermCity, PermState, PermZip, PermPhone, EmployerID)
Employer_TBL (EmployerID, CompanyName, Street, City, State, Zip, Phone, ContractDiscount)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-26 : 10:46:18

can you show what you tried yet?
It looks like an assignment question and we would like to see an attempt from your end before we help you out.

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

vertroa
Starting Member

3 Posts

Posted - 2012-04-26 : 11:25:02

Sure I have tried many different things.

Here is the latest failure...

Select RTRIM(FIRSTNAME)||' '||RTRIM(LASTNAME) as Name, PERMCITY, PERMSTATE, PERMPHONE
From Trainee_TBL
Where permstate in (
Select permstate
From trainee_tbl
Group by permstate
Having count(permstate)>1);

It shows PermStates that are duplicates but not for city or EmployerID. Also it shows city and state more than once. So yea I'm at a loss.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-26 : 12:29:30

Are you using sql server? it seems you're not as || is not concatenation operator in t-sql

Please note that this forum specialises in SQL Server and solutions given are guaranteed to work only in sql server. So please post in relevant forum to get accurate solution
you can try below query though if it works in your RDBMS


SELECT RTRIM(t.FIRSTNAME)||' '||RTRIM(t.LASTNAME) as Name, 
t.PERMCITY, 
t.PERMSTATE, 
t.PERMPHONE
FROM Trainee_TBL t
INNER JOIN (SELECT EmployerID,PermCity, PermState
            FROM Trainee_TBL 
            GROUP BY EmployerID,PermCity, PermState
            HAVING COUNT(TraineeID) > 1
            )g
ON g.EmployerID = t.EmployerID
AND g.PermCity = t.PermCity
AND g.PermState = t.PermState

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

vertroa
Starting Member

3 Posts

Posted - 2012-04-26 : 13:10:30

Perhaps I am in the wrong forum. I am taking a class at school and we are using SQL plus to connect to the database. However your query does seem to work. I am going to study what you did and try to figure it all out. Thank you very much for the help, much appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-26 : 15:25:06

Ok..If its working fine..What I gave was ANSI based query so it should work fine in most RDBMS. But if it has some SQL Server specific syntax it wont work

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources