-1

Hello,

I'm using MySQL for a database containing 1 table with 10 columns.
The point is to get a view of a few columns and use it in a web application
So, I need 2 columns from this table - Pad(varchar) and Sequence(integer), then I create 8 views like this:

CREATE VIEW vv1 AS
SELECT pad, sequence as lift1 FROM management
WHERE LEFT(activity_id, 9) = 'MOUN.SECB';

create view vv2 as
SELECT sequence as lift2 from management
where left(activity_id, 9) = 'MOUN.SECT';

And so forth until vv8 which represents Task8. I end up with 8 views - lift1, lift2, lift3, task4, task5, task6, task7, task8.
Then I simply SELECT * FROM vv1, vv2 ... vv8; - BUT I get a huge result of data which is pretty much a 'truth table', i.e. combination of all the numbers (sequence) I have in the column. Basically, I need the very first and the very last row of this 9-page result. I can't understand why I get this data instead of:

Pad|Lift1|Lift2|Lift3|Task4|...|Task8|
A1 1 1 1 1 1
A2 2 2 2 2 2

(sorry for the ugly representation of the wanted result)

Hope you guys understand what I mean and give some ideas.

Thanks in advance
Pepys

2
Contributors
5
Replies
6
Views
6 Years
Discussion Span
Last Post by pepyrs
1

try this
1. add the column pad to all the views.
2. join the views to select all the desired columns on pad.

without join the record will be a Cartesian product so the number will be huge.

0

I just did this yesterday and then I SELECT the pad only once ;)
Thanks a lot anyway dude.

best regards

0

One more question though.
Now when I got the wanted result I want to use these views in my WEB app. I used the datagrid and it's working just fine. But as far as I read until now it's not possible to UPDATE the views when some changes in the application happen (i.e changing the sequence) Actually it is the database table getting the updates not the views. Am I right? So I'm trying to convert the views into one table but of course it says "duplicate entry for Pad".
How can I create this table out of the views so I can use all the features of Microsoft Visual Studio (Edit, delete update)?

Thanks

0

When data in the source table changes all the dependent views will also change. so you need not try to update the views again.

0

Yes but the table is the important one. It has to get the updates otherwise it's like nothing happened.
Do you know how to convert the 8 views I have into one table without getting the 'duplicates' error?

Edited by pepyrs: n/a

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.