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 2000 Forums

SQL Server Development (2000)

query help

Author

Topic

keen1
Starting Member

21 Posts

Posted - 2003-09-17 : 13:39:08

i have 4 tables managing the users of an application.
Users have their id and their roles and they have some production lines to administer.
Also there is a table listing the all the production lines.

i need to write a stored proc. returning the lines for them to administer.
i need to return all the production lines but i want the production lines they administer at the top of the list!
A simple union only brings back all the prod. lines but in no specific order...

Below is the tables' genarated scripts
CREATE TABLE [dbo].[DTProdLines] (
[ProdLineID] [smallint] NOT NULL ,
[ProdLineName] [nvarchar] (4) COLLATE Turkish_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Roles] (
[RoleID] [int] NOT NULL ,
[Name] [nvarchar] (50) COLLATE Turkish_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Users] (
[UserID] [int] IDENTITY (1, 1) NOT NULL ,
[UserName] [nvarchar] (50) COLLATE Turkish_CI_AS NOT NULL ,
[RoleID] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[UsersProdLineAdmin] (
[UserID] [int] NOT NULL ,
[UserProdLineAdmin] [smallint] NOT NULL
) ON [PRIMARY]
GO

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-17 : 13:43:01

the only relationship i see between users and product lines is in the "UsersProdLineAdmin" table. What other kind of relationship is there? you haven't provided enough information.

sample data would help to.

INSERT INTO Tble (col1, col2)
SELECT 'something', 'Somethingelse' UNION
SELECT 'something', 'Somethingelse' UNION
...etc ...

Finally, if you want to order a UNION queries results based on which table it came from, create your own field:


SELECT A.* 
FROM
(SELECT 1 as Sort, * 
 FROM tbl1
 UNION 
 SELECT 2 as Sort, *
 FROM tbl2
) A
ORDER BY Sort

that will ensure all results from tbl1 are returned BEFORE tbl2.

- Jeff

keen1
Starting Member

21 Posts

Posted - 2003-09-17 : 15:29:47

Hi Jeff,

that's right; the only relationship between users and product lines is in the "UsersProdLineAdmin" table.

my stored proc will return back all the production lines for all the times but what i want is, i want the production lines in the UsersProdLineAdmin table to appear at the top of the list.

When i try to use a union, i get the production lines in the UsersProdLineAdmin table at the top of the list but since this is a union i get the same production lines in the return list two times.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-17 : 15:44:19

you still have no relationship. how do Users relate to product lines based on the tables you have provided?

and PLEASE give some sample data .

it sounds like you want to return all rows from 1 table, but if there is a match in another table you want matching rows first.

If so:


SELECT
  tbl1.*
FROM
  tbl1
LEFT OUTER JOIN
  tbl2
ON
  tbl1.ID = tbl2.ID
ORDER BY CASE WHEN tbl2.ID IS NULL THEN 1 ELSE 0 END ASC

do you see how this process might be easier if you give some sample data and what you have tried so far instead of me just guessing over and over ....

- Jeff

keen1
Starting Member

21 Posts

Posted - 2003-09-18 : 02:24:57

Hi Jeff,
i give sample data below:

Users table:
UserID UserName RoleID
------ -------- ------
1 oaydogan 2
2 ntuncel 5

UsersProdLineAdmin table:
UserID UserProdLineAdmin
------ -----------------
1 5
1 6
1 7
1 2
1 4
1 1
2 4
2 5

DTProdLines table:
ProdLineID ProdLineName
---------- ------------
0 CPL
1 RCM1
2 ECL
3 BAF
4 SPM
5 CSL
6 CTL
7 HDG
8 RCM2
9 CGL

Now i want to be able to get the lists below:

When i query with the user id=1:
UserID ProdLineID ProdLineName LineAdmin
------ ---------- ------------ ---------
1 5 CSL 1
1 6 CTL 1
1 7 HDG 1
1 2 ECL 1
1 4 SPM 1
1 1 RCM1 1
1 0 CPL 0
1 3 BAF 0
1 8 RCM2 0
1 9 CGL 0

And when i query with the user id=2:
UserID ProdLineID ProdLineName LineAdmin
------ ---------- ------------ ---------
2 4 SPM 1
2 5 CSL 1
2 0 CPL 0
2 1 RCM1 0
2 2 ECL 0
2 3 BAF 0
2 6 CTL 0
2 7 HDG 0
2 8 RCM2 0
2 9 CGL 0

You see, i want to lines from the UsersProdLineAdmin table at the top and the remaining ones from the DTProdLines table.

Regards

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-18 : 15:11:22

[code]
SELECT
@UserID as UserID, A.ProdlineID, A.ProdLineName,
CASE WHEN b.UserProdLineAdmin is null THEN 0 ELSE 1 END as LineAdmin
FROM
ProdLines A
LEFT OUTER JOIN
UsersProdLineAdmin B
ON
B.UserID = @UserID AND
B.UserProdLineAdmin = A.ProdLineID
ORDER BY
LineAdmin DESC, ProdLineID ASC
[/code]

- Jeff

keen1
Starting Member

21 Posts

Posted - 2003-09-19 : 03:41:05

Thank you Jeff :) this is so helpful to me.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources