473,327 Members | 1,967 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Building a View (basic SQL ?)

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
Mar 26 '07 #1
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!
Mar 26 '07 #2
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.
Mar 27 '07 #3
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...
Mar 27 '07 #4
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.
Expand|Select|Wrap|Line Numbers
  1. CREATE VIEW myview AS
  2. SELECT a.id, a.value, b.value FROM data a, data b 
  3. WHERE a.name_id=9 AND a.id=b.id AND a.prefix!=b.prefix;
i made the tables as follows.
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE name(
  2.     id    integer,
  3.     name    varchar(20)
  4. );
  5.  
  6. CREATE TABLE data(
  7.     prefix    varchar(1),
  8.     id    integer,
  9.     name_id    integer,
  10.     value    varchar(20)
  11. );
  12.  
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.
Mar 27 '07 #5
MMcCarthy
14,534 Expert Mod 8TB
Check out this tutorial.

Normalisation and Table structures
Mar 29 '07 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

1
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...
4
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...
8
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)...
1
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...
0
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...
10
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...
2
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...
2
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...
1
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 ...
0
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
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...
0
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...
1
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)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
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....
0
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...
0
isladogs
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.