Crazy CrossTab problems
 

Hi there,
I'm completely lost.

I have 3 tables:
- work (workID, description)
- machine (machineID, name)
- work_machine (machineID, workID, hours)

What I want is to obtain a result similar to:

work, machine1, machine2
A , 1 ,
B , 3 , 5

The problem is that the number of works and the number of machines is unknown.

Can anyone help?

Re: Crazy CrossTab problems
 

I think what you can do on your work_machine table is have references to the workid and machineid...

work_machineID | workID | machineID
1 | A | 1
2 | B | 3
3 | B | 5

Then just select from this table where workID = 'A';

Re: Crazy CrossTab problems
 

Muke, seems you didn't normalize your database design!!

Re: Crazy CrossTab problems
 

Ok, I think that I haven't explain myself.

In table "work_machine" I have:
workID | machineID | hours
A | M1 | 1
B | M2 | 3
B | M1 | 5

Now I would like to know how to make the query to obtain the data in the format:

work | machine1 | machine2
A | 1 | 0
B | 3 | 5

Any idea?

Re: Crazy CrossTab problems
 

What's machine1 and machine2 ?? and what '0' in the first row in output?

Re: Crazy CrossTab problems
 

Hi,

machine1 and machine2 are rows from machine table
The 0 represents that machine2 don't do any "A" work

Re: Crazy CrossTab problems
 

As I said, you've lack in your design, I am sorry but your database design is wrong. It need some normalization

Re: Crazy CrossTab problems
 

Hi again! Maybe you can try reading this: some-thread-on-sums.

Or this one @ daniweb.

Or ask RamyMahrous to describe the proper table structure. =)

Re: Crazy CrossTab problems
 

Hi RamyMahrus,

my DB design is the following:
I have 3 tables:
- work (workID, description)
- machine (machineID, name)
- work_machine (machineID, workID, hours)

Can you tell me what's wrong?

Re: Crazy CrossTab problems
 

That's correct give me real sample data to all these tables + output you need not table just in english text what you need to extract from these tables.