I've the following question about making views, I suppose it's standard SQL but I cant find a solution. Perhaps someone can help.
- Suppose I've a table Name (ID, Name)
- And a table Data (ID, Name_ID, Value)
The question is how to make a view showing Data.Id, Name.name, Data.Value
Example:
- 2 records in Name; 9 , Book and 10, Car
- 4 records in Data; (1, 9, All trains 2007) ; (2, 9, Cars 2006) ; (4, 10, Ford); (5, 10, Citroen)
The result should be 4 record:
- (1, Book, All trains 2007) ; (2, Book, Cars 2006) ; (4, Car, Ford) ; (5, Car, Citroen)
Hope you can help, thanks
5 1468
CREATE VIEW myview AS
SELECT Data.id, Name.name, Data.value
FROM Data, Name
WHERE Data.name_id=Name.id;
next time please try to post with "what you have done so far and what is causing you problem" because it seems you are doing just your homework assignment here by depending upon others. i am not offending you, but you should provide what you have done so far and what is causing you problem.
Cheers!
First, thanks rushdishams for your answer; but I've made a mistake in the example. Made it to simplified.
Therefor the solution is not correct (my fault !), and not solving my problem. I'll try to explain it better.
1) the 2 records in Name are not all the records in Name; I need a subset
2) The result should be 2 records
So:
- 3 records in Name; (9, Book) ; (10, Car) and (11, Boat)
- 5 records in Data; (x, 1, 9, "All trains 2007") ; (y, 4, 9, "Cars 2006") ; (z, 1, 10, "Ford"); (w, 4, 10, "Citroen"); (r, 3, 11,"Valk")
- the second column in data is an id connecting all the records for one case; the third in data the key to table Name
Should result in:
- 2 records (1, "All trains 2007", "Ford") and (4, "Cars 2006", "Citroen")
Showing my homework ;-) based on your answer
CREATE VIEW myview AS
SELECT Data.id, Data.value (book), Data.value (car)
FROM Data, Name
WHERE Data.name_id=Name.id and (Name.name IN ("Book", "Car"));
My problem is how to point to the Data.values for both Book and Car, resulting in 1 record in the view.
mate, why dont you make more tables? because with those 2 tables, it is really getting complecated to make such query....
you can make a table to categorize items like book, car, boat and their ids
you can then make a table which has only book entries
then a table that has only car entries
and a table that has only boat entries
you can then join tables to make such queries.
i am suggesting that because, you are now actually querying columns which are identical things by matching other columns which are identical things and big problem is both the columns are in the same table...
Should result in:
- 2 records (1, "All trains 2007", "Ford") and (4, "Cars 2006", "Citroen")
if this is the desired output by you from the query over the view, then the view is as follows. - CREATE VIEW myview AS
-
SELECT a.id, a.value, b.value FROM data a, data b
-
WHERE a.name_id=9 AND a.id=b.id AND a.prefix!=b.prefix;
i made the tables as follows. -
CREATE TABLE name(
-
id integer,
-
name varchar(20)
-
);
-
-
CREATE TABLE data(
-
prefix varchar(1),
-
id integer,
-
name_id integer,
-
value varchar(20)
-
);
-
but mate, still i am asking, if this is not just for playing purpose, then it is better to normalize your tables....
hope this helps you.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Arun Nair |
last post by:
Hi,
I have the following jsp page. I am able to view only a part of
it(until the occurence of the first City text box). If i remove the
first few fields from the page, I am able to see a few...
|
by: Rasjid Wilcox |
last post by:
I am wanting to write a database application using Python.
I want it to be GUI agnostic. The GUI will probably be Python/wxPython, but
I would also like the option of a Webbased (PHP?) gui, and...
|
by: Jef Driesen |
last post by:
I'm implementing some image processing algorithms in C++. I created a
class called 'image' (see declaration below), that will take care of the
memory allocations and some basic (mathematical)...
|
by: Kelly B. |
last post by:
Hello
I've created a customer database for the company I work for. Now I've
started on building a work order, which will be related to It.
Basically, I need to build a form to accept shirt size...
|
by: ken |
last post by:
Hi all,
I'm using the MS VB example "How to: Receive Strings From Serial Ports
in Visual Basic" and would like to see the data being received. I have
two virtual serial ports COM5 and COM6 setup...
|
by: Jen |
last post by:
I have a form that has two radio buttons. When the first one is
clicked, I would like the page to refresh (keeping the form data in
tact) and then displaying 2 new fields that need to be filled...
|
by: Faraz Meghani |
last post by:
Hello,
I am trying to build an SQL in Visual Basic and need some help with
quotation mark(s).
I have two text fields on a form where the user enters a sales group
range to view records...
|
by: grant |
last post by:
Man do I struggle with Enterprise Manager as a graphical tool for
building views etc.
Its flaky and hangs frequenlty so i have to kill it with the task
manager and re open it.
You cant debug...
|
by: nitdesh |
last post by:
Hi , I have Just Registered to This Site and Deeply Require a Help For my Software .
I am Into Warehousing Business
I Have Front End as Visual Basic 6.0 and Backend as SQl 7.0
Database ...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |