How do I create view from two tables having different rows with same ID

Question

0.00/5 (No votes)

See more:

How to creat view from two table having different rows with same ID

Edit (from "Solution 2"):

the tables are
Diagnosis(id,fees,ddate,doct)
Analysis(id,afees,ano,aname,adate)

Edit (from "Solution 3"):

diagnosis values are(1,100,21-6,ali)
Analysis values are(1,50,23,cbc,21-6)
Second row analysis values are(1,70,33,urea,21-6)
The view is
Create view v_accont as(selecy d.id,d.fees,d.ddate,d.doct,a.aname,a.afees from diagnosis d inner join analysis a on d.id=a.id)
I'm getting
Id fees ddate doct aname afees
1 100 21-6 ali cbc 50
1 100 21-6 ali urea 70
I hope this is cleat

Posted 22-Jun-14 22:39pm

ABAKh

Updated 23-Jun-14 9:24am

Blutfaust

v2

Add a Solution

Comments

ABAKh 23-Jun-14 4:42am

i created the view but i got duplicate coulmn fields

Blutfaust 23-Jun-14 15:42pm

Please do not post your additions to your questions as "Solution". Instead please edit your original question and insert the additional information there.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

DamithSL · Answer 1 · 2014-06-22T22:42:00

Solution 1

use SQL join and create view

SQL

CREATE VIEW view_Order AS
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;

Posted 22-Jun-14 22:42pm

DamithSL

Comments

ABAKh 23-Jun-14 4:58am

i appreciate yotr answer but i still getting duplicate fields

DamithSL 23-Jun-14 6:37am

can you update the question with your table structures, View creation script you tried and the details of duplicate fields?

CHill60 24-Jun-14 7:05am

I can't see how DamithSL's solution would give duplicate columns - did you type it in exactly as given in the solution. And do you actually mean duplicate columns or are you getting duplicate rows? Post a couple of lines of your results (use the Improve question link) so we can see what you mean

Jafarinejadvazifehkhorani · Answer 2 · 2014-06-23T09:49:00

Solution 4

for sure you will be given two rows because 'id' column for the two rows of analysis table have the same value. To make it work you have to change the value of second row to (2,70,33,urea,21-6)

Posted 23-Jun-14 9:49am

Jafarinejadvazifehkhorani

Comments

ABAKh 23-Jun-14 17:16pm

I can't change the value of the second row to 2 , because the two rows in the analysis table are rlated to the row in the diagnosis table which has id=1