Given the following table structure

ClientID    Points    StaffID1  StaffID2 StaffID3

Each of the staffid<n> fields may be null or contain a staff id - any staffid can only show up once per row (if at all)


can I return a result set that lists (sorted by staff id, subsorted by client id) the client ids and the points per staff id regardless of which field the
staffid was found in?

thanks for any help
darrell

darrell


Two questions

1. What are "points per staff id"?
2. Each row can have up to three distinct staff ids?


Roy Lambert [Team Elevate]

Two questions

1. What are "points per staff id"?

From table 'p' (I cannot  influence table structures here)

ClientID  Points  StaffID1    StaffID2    StaffID3
-------------------------------------------------------
100            4      10            11             12
223            3      17            12             NULL
205            5      11            10             17

I need to create a report

StaffID         ClientID      Points
-------------------------------------
10                100             4
                   205            5
11                100             4
                   205            5
12                100             4
                  223             3
17                223             3



2. Each row can have up to three distinct staff ids?

Yes

darrell


A script that will almost do what you want - the bit it doesn't do is the blank staff ID where its repeated

Select ClientID, Points, StaffID1 AS StaffID INTO "Memory\temp" FROM  P WHERE StaffID1 IS NOT NULL;
INSERT  INTO "Memory\temp" SELECT ClientID, Points, StaffID2 AS StaffID FROM P WHERE StaffID2 IS NOT NULL;
INSERT  INTO "Memory\temp" SELECT ClientID, Points, StaffID3 AS StaffID FROM P WHERE StaffID3 IS NOT NULL;
SELECT StaffID, ClientID, Points FROM "Memory\Temp" ORDER BY StaffID,ClientID;

Roy Lambert [Team Elevate]

Thanks Roy - that works fine and puts an end to my fruitless efforts of trying to do this
in one statement!

Darrell

"darrell" <dfm@gmail.com> wrote in message
news:53441408-BC65-41FD-8080-2D85A5A9C30D@news.elevatesoft.com...
>
>
> Two questions
>
> 1. What are "points per staff id"?
>
> From table 'p' (I cannot  influence table structures here)
>
> ClientID  Points  StaffID1    StaffID2    StaffID3
> -------------------------------------------------------
> 100            4      10            11             12
> 223            3      17            12             NULL
> 205            5      11            10             17
>
> I need to create a report
>
> StaffID         ClientID      Points
> -------------------------------------
> 10                100             4
>                    205            5
> 11                100             4
>                    205            5
> 12                100             4
>                   223             3
> 17                223             3
>

SELECT STAFFID1 AS STAFFID, CLIENTID, POINTS
FROM TABLE
WHERE STAFFID1 <> NULL
UNION
SELECT STAFFID2, CLIENTID, POINTS
FROM TABLE
WHERE STAFFID2 <> NULL
UNION
SELECT STAFFID3, CLIENTID, POINTS
FROM TABLE
WHERE STAFFID3 <> NULL
ORDER BY 1, 2

Robert


>
>
> 2. Each row can have up to three distinct staff ids?
>
> Yes
>

Robert


I thought of using UNION but stopped because I thought the ORDER BY only applied to the SELECT statement it followed.

Roy Lambert

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:9D879AA1-9977-4E47-A20D-FF17D4B56934@news.elevatesoft.com...
> Robert
>
>
> I thought of using UNION but stopped because I thought the ORDER BY only
> applied to the SELECT statement it followed.
>

Would not make much sense. The whole purpose of UNION is e pluribus unum,
create one of many.

The INTO (if present) on the first select, any naming of columns on the
first select, and the ORDER BY at the end all apply to the whole selected
dataset.

BTW, there is an easy way to keep the items extracted from each dataset all
together, as follows

SELECT 1, FIELDA, FIELDB FROM TABLEA
UNION
SELECT 2, FIELDX, FIELDY FROM TABLEB
ORDER BY 1, 2, 3

By creating extracted constants, you can manipulate the final order, as in

SELECT  0, FIELDA, FIELDB FROM TABLEA
UNION
SELECT  IF(FIELDM > 10 THEN 2 ELSE 1), FIELDX, FIELDY FROM TABLEB
ORDER BY 1, 2, 3

Robert

"Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote in message
news:177EF583-FC49-4536-B109-6D2AEDE59C8F@news.elevatesoft.com...

E pluribus unum
Nice one
Rita