Need help on Create View

[kackson]

Hi.

I created a simple view with the following statements:

CREATE VIEW dbo.VIEW1
AS
SELECT dbo.VIEW_ALL.ID ,
dbo.VIEW_ALL.Co de,
Another.dbo.OTH ER_VIEW.Label as SpecialCode
FROM dbo.VIEW_ALL LEFT OUTER JOIN
Another.dbo.OTH ER_VIEW ON
(dbo.VIEW_ALL.C ode + '_0') = Another.dbo.OTH ER_VIEW.Label

When I run :

select * from VIEW1 where code = 'abcde',

I would get all 10 rows that are of the same data where the code is
'abcde'. But what I thought it should return is really just one row
since the rest of the 9 rows are exactly the same! I know that I can
get my result if I use
select DISTINCT * from VIEW1 where code = 'abcde'

But, is there any way I could change my SQL statements(CREA TE VIEW)
above so that I automatically get 1 row instead of 10 rows of exactly
the same value without using DISTINCT?

Thank you for any help.

[Erland Sommarskog]

kackson (ka*****@yahoo. com) writes:

I created a simple view with the following statements:

CREATE VIEW dbo.VIEW1
AS
SELECT dbo.VIEW_ALL.ID ,
dbo.VIEW_ALL.Co de,
Another.dbo.OTH ER_VIEW.Label as SpecialCode
FROM dbo.VIEW_ALL LEFT OUTER JOIN
Another.dbo.OTH ER_VIEW ON
(dbo.VIEW_ALL.C ode + '_0') = Another.dbo.OTH ER_VIEW.Label

When I run :

select * from VIEW1 where code = 'abcde',

I would get all 10 rows that are of the same data where the code is
'abcde'. But what I thought it should return is really just one row
since the rest of the 9 rows are exactly the same! I know that I can
get my result if I use
select DISTINCT * from VIEW1 where code = 'abcde'

But, is there any way I could change my SQL statements(CREA TE VIEW)
above so that I automatically get 1 row instead of 10 rows of exactly
the same value without using DISTINCT?

I'm afraid that you need to supply more information. There is no way
anyone can guess what's in your tables. The normal suggestion for this
type of question is that you proivde:

o CREATE TABLE statements for the involved tables.
o Sample data in form of INSERT statements.
o The expected result give the sample.
o A short narrative of what you want to achieve.

This can give you a tested solution. Providing table and data in other
forms may give you a solution, but probably untested. And not giving
any useful information at all, is not likely to help you at all.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[kackson]

Hi.

If given 2 tables, say tableA and tableB. And 2 fields called
tableA.Code1 and tableB.code2. All data in tableA.Code1 are unique
while data in tableB.code2 are not unique (e.g. there could be 2
entries with data as "DEF"). How do I make a view so that when I list
a view that is composed by joining tableA and tableB, only unique
values are presented?

e.g. I want result of
tableA.Code1 | tableB.code2
-----------------------------
ABC | DEF
XYZ | OPQ

instead of

tableA.Code1 | tableB.code2
-----------------------------
ABC | DEF
ABC | DEF
XYZ | OPQ

Any help please?

Erland Sommarskog <es****@sommars kog.se> wrote in message news:<Xn******* *************** @127.0.0.1>...

kackson (ka*****@yahoo. com) writes:

I created a simple view with the following statements:

CREATE VIEW dbo.VIEW1
AS
SELECT dbo.VIEW_ALL.ID ,
dbo.VIEW_ALL.Co de,
Another.dbo.OTH ER_VIEW.Label as SpecialCode
FROM dbo.VIEW_ALL LEFT OUTER JOIN
Another.dbo.OTH ER_VIEW ON
(dbo.VIEW_ALL.C ode + '_0') = Another.dbo.OTH ER_VIEW.Label

When I run :

select * from VIEW1 where code = 'abcde',

I would get all 10 rows that are of the same data where the code is
'abcde'. But what I thought it should return is really just one row
since the rest of the 9 rows are exactly the same! I know that I can
get my result if I use
select DISTINCT * from VIEW1 where code = 'abcde'

But, is there any way I could change my SQL statements(CREA TE VIEW)
above so that I automatically get 1 row instead of 10 rows of exactly
the same value without using DISTINCT?

I'm afraid that you need to supply more information. There is no way
anyone can guess what's in your tables. The normal suggestion for this
type of question is that you proivde:

o CREATE TABLE statements for the involved tables.
o Sample data in form of INSERT statements.
o The expected result give the sample.
o A short narrative of what you want to achieve.

This can give you a tested solution. Providing table and data in other
forms may give you a solution, but probably untested. And not giving
any useful information at all, is not likely to help you at all.

[Hugo Kornelis]

On 7 Sep 2004 20:41:44 -0700, kackson wrote:

Hi.

If given 2 tables, say tableA and tableB. And 2 fields called
tableA.Code1 and tableB.code2. All data in tableA.Code1 are unique
while data in tableB.code2 are not unique (e.g. there could be 2
entries with data as "DEF"). How do I make a view so that when I list
a view that is composed by joining tableA and tableB, only unique
values are presented?

e.g. I want result of
tableA.Code1 | tableB.code2
-----------------------------
ABC | DEF
XYZ | OPQ

instead of

tableA.Code1 | tableB.code2
-----------------------------
ABC | DEF
ABC | DEF
XYZ | OPQ

Any help please?

Hi Kackson,

As Erland already indicated: "Providing table and data in other forms may
give you a solution, but probably untested".

This is an untested query:

SELECT A.Code1, B.code2
FROM tableA AS A
INNER JOIN (SELECT DISTINCT code2
FROM tableB) AS B
ON whatever your join criteria are
Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Hugo Kornelis]

On 7 Sep 2004 20:41:44 -0700, kackson wrote:

Hi.

If given 2 tables, say tableA and tableB. And 2 fields called
tableA.Code1 and tableB.code2. All data in tableA.Code1 are unique
while data in tableB.code2 are not unique (e.g. there could be 2
entries with data as "DEF"). How do I make a view so that when I list
a view that is composed by joining tableA and tableB, only unique
values are presented?

e.g. I want result of
tableA.Code1 | tableB.code2
-----------------------------
ABC | DEF
XYZ | OPQ

instead of

tableA.Code1 | tableB.code2
-----------------------------
ABC | DEF
ABC | DEF
XYZ | OPQ

Any help please?

Or (also untested)

SELECT DISTINCT A.Code1, B.code2
FROM tableA AS A
INNER JOIN tableB AS B
ON whatever your join criteria are

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[strider5]

ka*****@yahoo.c om (kackson) wrote in message news:<90******* *************** ****@posting.go ogle.com>...

Hi.

I created a simple view with the following statements:

CREATE VIEW dbo.VIEW1
AS
SELECT dbo.VIEW_ALL.ID ,
dbo.VIEW_ALL.Co de,
Another.dbo.OTH ER_VIEW.Label as SpecialCode
FROM dbo.VIEW_ALL LEFT OUTER JOIN
Another.dbo.OTH ER_VIEW ON
(dbo.VIEW_ALL.C ode + '_0') = Another.dbo.OTH ER_VIEW.Label

When I run :

select * from VIEW1 where code = 'abcde',

I would get all 10 rows that are of the same data where the code is
'abcde'. But what I thought it should return is really just one row
since the rest of the 9 rows are exactly the same! I know that I can
get my result if I use
select DISTINCT * from VIEW1 where code = 'abcde'

But, is there any way I could change my SQL statements(CREA TE VIEW)
above so that I automatically get 1 row instead of 10 rows of exactly
the same value without using DISTINCT?

Thank you for any help.

How about using distinct in the create view statement?

[Raju]

ka*****@yahoo.c om (kackson) wrote in message news:<90******* *************** **@posting.goog le.com>...

Hi.

If given 2 tables, say tableA and tableB. And 2 fields called
tableA.Code1 and tableB.code2. All data in tableA.Code1 are unique
while data in tableB.code2 are not unique (e.g. there could be 2
entries with data as "DEF"). How do I make a view so that when I list
a view that is composed by joining tableA and tableB, only unique
values are presented?

e.g. I want result of
tableA.Code1 | tableB.code2
-----------------------------
ABC | DEF
XYZ | OPQ

instead of

tableA.Code1 | tableB.code2
-----------------------------
ABC | DEF
ABC | DEF
XYZ | OPQ

Any help please?

Erland Sommarskog <es****@sommars kog.se> wrote in message news:<Xn******* *************** @127.0.0.1>...

kackson (ka*****@yahoo. com) writes:

I created a simple view with the following statements:

CREATE VIEW dbo.VIEW1
AS
SELECT dbo.VIEW_ALL.ID ,
dbo.VIEW_ALL.Co de,
Another.dbo.OTH ER_VIEW.Label as SpecialCode
FROM dbo.VIEW_ALL LEFT OUTER JOIN
Another.dbo.OTH ER_VIEW ON
(dbo.VIEW_ALL.C ode + '_0') = Another.dbo.OTH ER_VIEW.Label

When I run :

select * from VIEW1 where code = 'abcde',

I would get all 10 rows that are of the same data where the code is
'abcde'. But what I thought it should return is really just one row
since the rest of the 9 rows are exactly the same! I know that I can
get my result if I use
select DISTINCT * from VIEW1 where code = 'abcde'

But, is there any way I could change my SQL statements(CREA TE VIEW)
above so that I automatically get 1 row instead of 10 rows of exactly
the same value without using DISTINCT?

I'm afraid that you need to supply more information. There is no way
anyone can guess what's in your tables. The normal suggestion for this
type of question is that you proivde:

o CREATE TABLE statements for the involved tables.
o Sample data in form of INSERT statements.
o The expected result give the sample.
o A short narrative of what you want to achieve.

This can give you a tested solution. Providing table and data in other
forms may give you a solution, but probably untested. And not giving
any useful information at all, is not likely to help you at all.

Hi kackson,

Can't you create a view with simple join instead of Left outer join to
get unique rows from both the tbls.

Thank you
Raju

[Erland Sommarskog]

kackson (ka*****@yahoo. com) writes:

If given 2 tables, say tableA and tableB. And 2 fields called
tableA.Code1 and tableB.code2. All data in tableA.Code1 are unique
while data in tableB.code2 are not unique (e.g. there could be 2
entries with data as "DEF"). How do I make a view so that when I list
a view that is composed by joining tableA and tableB, only unique
values are presented?

e.g. I want result of
tableA.Code1 | tableB.code2
-----------------------------
ABC | DEF
XYZ | OPQ

instead of

tableA.Code1 | tableB.code2
-----------------------------
ABC | DEF
ABC | DEF
XYZ | OPQ

Any help please?

You didn't seem to care about the advice I gave you. Look, if you insist
on posting vague questions, you will get vague answers back. Here is
a rewrite of you original query, but it is all guesswork on my part:
SELECT dbo.VIEW_ALL.ID ,
dbo.VIEW_ALL.Co de,
(dbo.VIEW_ALL.C ode + '_0') as SpecialCode
FROM dbo.VIEW_ALL
WHERE EXISTS (SELECT *
FROM Another.dbo.OTH ER_VIEW
WHERE (dbo.VIEW_ALL.C ode + '_0') =
Another.dbo.OTH ER_VIEW.Label)

This will not give the same result, because if there is no matching
row in OTHER_VIEW, then the row from VIEW_ALL will not be listed. But
you have not explained why you are using outer join anyway, so I'm
just guess that you were wrong in doing that.

Anyway, I say it again. Include:

o CREATE TABLE statements for the involved tables.
o Sample data in form of INSERT statements.
o The expected result give the sample.
o A short narrative of what you want to achieve.

This can give you a tested solution. Providing table and data in other
forms may give you a solution, but probably untested. And not giving
any useful information at all, is not likely to help you at all.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp