Weird Join 1

[TheWebDataGuy]

I am not sure if this is possible:

I have one table with LogID as primary key and another table with LogID & EmployeeID as composite primary key. One-to-Many relationship.

Table 1 Table 2

LogID LogID
----- -----
EmployeeID
----------

Can I create a Tabel 3 three with the following attributes:

Table 3

LogID
-----
EmployeeID1
EmployeeID2
EmployeeID3
EmployeeID4
EmployeeID5

Its equivalent of a 'denormalization'. And I know it for sure that there cannot be more than 5 EmployeeIDs attached to a LogID


Is it possible????


Thanks

 

[tlbroadbent]

I'm not sure I understand your question. This may give you an idea how to proceed.

Select EmployeeID As LogID
Into table3
From table2

Or if you need to join to table1...

Select EmployeeID As LogID
Into table3
From table2
Inner Join table1
On table2.LogId=table2.LogId Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[TheWebDataGuy]

Thanks Terry for your reply. Yeah its difficult to understand what I am trying to do (appropriately named 'Weird'). I will give you an example:

TABLE 1

LogID Date Amount
-----
123 01/12/02 $250
456 11/7/01 $500
789 2/15/02 $1000


TABLE 2

LogID EmployeeID EmployeeName
----- ---------
123 666666 xyz
123 777777 abc
123 888888 ijk
456 555555 qwe
456 444444 zxc
789 333333 jkl

TABLE 3

LogID EmployeeID1 EmployeeID2 EmployeeID3 EmployeeID4
-----
123 666666 777777 888888 <NULL>
456 555555 444444 <NULL> <NULL>
789 333333 <NULL> <NULL> <NULL>

I hope this helps to clearly understand the complications involved here.

 

[tlbroadbent]

Here is a solution.

SELECT
q.LogID,
Max(IIf(q.RecNo=1, q.EmployeeID, 0)) AS EmpId1,
Max(IIf(q.RecNo=2, q.EmployeeID, 0)) AS EmpId2,
Max(IIf(q.RecNo=3, q.EmployeeID, 0)) AS EmpId3,
Max(IIf(q.RecNo=4, q.EmployeeID, 0)) AS EmpId4,
Max(IIf(q.RecNo=5, q.EmployeeID, 0)) AS EmpId5
FROM
(SELECT t.LogId, t.EmployeeID,
(Select count(*) From MyTable
Where LogId=t.LogID
And EmployeeID<=t.EmployeeId) AS RecNo
FROM MyTable AS t) AS q
GROUP BY q.LogId;
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[TheWebDataGuy]

Terry,

Did I tell your that I you are a genius? Your query worked like a magic!! Thanks so much.

I must confess I could not fully understand the logic behind it. But I am working on it.

I have another question for you:

When I enter data in a SQL Server table (directly or by importing)and then open to view it, SQL Server arranges it in the ascending order of the primary key (LogID/EmployeeID). And so the EmployeeID1, EmployeeID2, EmployeeID3...are also in ascending order. This actually messes up the query logic because the first employeeID of any LogID has some significance.

Thanks again.

 

[tlbroadbent]

You can't depend on SQL Server to order the data in a table. By default, relational data is unordered. However, SQL Server does physically order data on a clustered index. Chances are the table has a clustered index that creates the sequence you see. I suggest that you control the sequence with a record ID or another column.

The query that I provided is actually the culprit in putting the EmplyeeID in sequence. Note the test in the count sub-query - EmployeeID<=t.EmployeeId. If you had a RecordID you could change the logic of the count and the EmployeeIDs could be inserted correctly in teh query result. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[tlbroadbent]

As soon as I hit submit, I realized that this is not the SQL Server forum. Some of what I said in the previous applies in Access but Access does order data as inserted. Therefore, you can do some things not available in other SQL languages. However, the count sub-query will still do the same thing in Access as in SQL Server. Sorry for the mixup. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions in the SQL Server forum. Many of the ideas apply to all forums.