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

SQL Server 2008 Forums

Transact-SQL (2008)

Query Help

Author

Topic

sturner333
Starting Member

22 Posts

Posted - 2011-02-23 : 09:48:36

I have a table where I would like to query the records by 'name'. But the name could be in one of 4 spots, but not more than 1. How could I search the records for this? I do have a separate table with all the possible 'name' sin it if that helps.
Thanks

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-02-23 : 11:58:49

The crystal ball is unclear.

Please provide the table structure and sample data!

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

sturner333
Starting Member

22 Posts

Posted - 2011-02-23 : 12:22:40

I would like to query records(project reports) in a table by the engineers that owns that project. The engineers name can show up in either of the 4 engineer entry columns on the record. So one project can be owned by up to 4 engineers, depending how many were asigned to it. I need to make a report that is sorted by each engineer, that shows which projects they are assigned to. I have a separate table with a list of the engineers if that table would be useful in the query.
Table
Proj number eng1 eng2 eng3 eng4
1 bob jim rick ---
2 rick --- Kerry ----
3 --- bob --- Kerry

So query would result in:
Bob
Proj 1
Proj2

Jim
Proj1

Kerry
Proj2
Proj3

sturner333
Starting Member

22 Posts

Posted - 2011-02-23 : 12:24:25

Table
Proj number eng1 eng2 eng3 eng4
1 bob jim rick ---
2 rick --- Kerry ----
3 --- bob --- Kerry

So query would result in:
Bob
Proj 1
Proj3

Jim
Proj1

Kerry
Proj2
Proj3

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-02-23 : 17:49:17

Ok.

First -- can you redesign this? What happens in the future if you want more than 4 engineers assigned to a project?

In a normalised design you'd have a simple table strucutre like this:


TABLE Engineers (
   [EngineerID]
   , [EngineerName]
   )

TABLE Project (
    [ProjectID]
   , [ProjectName]
   )

TABLE EngneerProjectAssignment (
    [ProjectID]
   , [EngineerID]
    )

If you built that structure then your query would be very, very easy.

Here's a horrible piece of SQL that gives you wat you are looking for though


      SELECT [eng1] AS [Engineer], [Proj] AS [Project] FROM projectDetails WHERE [eng1] IS NOT NULL
UNION SELECT [eng2] AS [Engineer], [Proj] AS [Project] FROM projectDetails WHERE [eng2] IS NOT NULL
UNION SELECT [eng3] AS [Engineer], [Proj] AS [Project] FROM projectDetails WHERE [eng3] IS NOT NULL
UNION SELECT [eng4] AS [Engineer], [Proj] AS [Project] FROM projectDetails WHERE [eng4] IS NOT NULL

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

sturner333
Starting Member

22 Posts

Posted - 2011-02-24 : 08:18:12

But my tables are not like that. Any ideas for:
Table
Proj number eng1 eng2 eng3 eng4
1 bob jim rick ---
2 rick --- Kerry ----
3 --- bob --- Kerry

So query would result in:
Bob
Proj 1
Proj3

Jim
Proj1

Kerry
Proj2
Proj3

Thanks for the help!

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-02-24 : 10:42:58

.............
Did you read all my post?


      SELECT [eng1] AS [Engineer], [Proj] AS [Project] FROM projectDetails WHERE [eng1] IS NOT NULL
UNION SELECT [eng2] AS [Engineer], [Proj] AS [Project] FROM projectDetails WHERE [eng2] IS NOT NULL
UNION SELECT [eng3] AS [Engineer], [Proj] AS [Project] FROM projectDetails WHERE [eng3] IS NOT NULL
UNION SELECT [eng4] AS [Engineer], [Proj] AS [Project] FROM projectDetails WHERE [eng4] IS NOT NULL

(Or similar)

I *know* your tables don't look like what I said -- I was advising you to MAKE them look like that!

C

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources