Problem Creating View from Management Studio

Hi:

I have a parent table and 5 child tables with a PK,FK reference. I designed a view in Management Studio by dropping the master and Child tables, so that I could see the parent data, and all details from child rows.

My view does not contain any data though my tables do. What have I done wrong?

To describe what I want:

I have Parent table with PK, ParentFields.

I have Child1 with FK, Details1, Child2 with FK, Details2 and so on.

I want a view that shows PK, ParentFields, Details1, Details2,..Details5.

Seems simple enough, I dont know what is wrong. I am a beginner and only use the Designer to do this kind of stuff.

Appreciate any help.

TIA

Kar

You can create a simple query such as

CREATE VIEW [dbo].[vVesselsCargo]
AS
SELECT
VC.VesselCargoID,
CMP.CompanyName Shipper
FROM
VesselCargo VC,
Companies CMP
WHERE
CMP.CompanieID = VC.ShipperID

Then you can see how Management Studio was create that View in design mode.

|||

Your problem needs to be described better including (1) the definition of the parent table and the 5 child tables and (2) the definition of the view as it presently exists. My knee-jerk guess would be that you have used an inner join in a situation that merits a left join. Examine this sequence:

Code Snippet

declare @.parent table (pk int, pkInfo varchar(20))
insert into @.parent
select 1, '1st Parent' union all select 2, '2nd Parent'
--select * from @.parent

declare @.child1 table (pk int, detail_1 varchar(30))
insert into @.child1
select 1, 'Pk #1: Detail Rec #1' union all
select 2, 'Pk #2: Detail Rec #2'

declare @.child2 table (pk int, detail_2 varchar(30))
insert into @.child2
select 2, 'Pk #2: Detail Rec #2'

declare @.child3 table (pk int, detail_3 varchar(30))
insert into @.child3
select 1, 'Pk #1: Detail Rec #3'

declare @.child4 table(pk int, detail_4 varchar(30))
insert into @.child4
select 1, 'Pk #1: detail Rec #4'

declare @.child5 table(pk int, detail_5 varchar(30))
insert into @.child5
select 1, 'Pk #1: detail Rec #5'

select p.pk,
p.pkInfo,
detail_1,
detail_2,
detail_3,
detail_4,
detail_5
from @.parent p
join @.child1 c1
on p.pk = c1.pk
join @.child2 c2
on p.pk = c2.pk
join @.child3 c3
on p.pk = c3.pk
join @.child4 c4
on p.pk = c4.pk
join @.child5 c5
on p.pk = c5.pk

/*
pk pkInfo detail_1 detail_2 detail_3 detail_4 detail_5
- -

(0 row(s) affected)

*/

Because there are cases in which one of the detail records is missing, no rows are returned; however, if you change the JOINs into LEFT JOINS, you get results for both PKs:

Code Snippet

select p.pk,
p.pkInfo,
detail_1,
detail_2,
detail_3,
detail_4,
detail_5
from @.parent p
left join @.child1 c1
on p.pk = c1.pk
left join @.child2 c2
on p.pk = c2.pk
left join @.child3 c3
on p.pk = c3.pk
left join @.child4 c4
on p.pk = c4.pk
left join @.child5 c5
on p.pk = c5.pk

/*
pk pkInfo detail_1 detail_2 detail_3 detail_4 detail_5
- - - - - -
1 1st Parent Pk #1: Detail Rec #1 NULL Pk #1: Detail Rec #3 Pk #1: detail Rec #4 Pk #1: detail Rec #5
2 2nd Parent Pk #2: Detail Rec #2 Pk #2: Detail Rec #2 NULL NULL NULL
*/

Perhaps you need to use a left join to return your data; however, you really need to give a better description of your problem.

|||

Hi:

Thanks for the replies. I am a beginner, and I did not use or create any SQL. I just used Management Studio, drag-dropped tables and selected the fields that I want.

The tables already have PK and FK relationships defined, and the Designer automatically created SQL that roughly looks like:

Select Primary.PK, Primary.<Other_Fields>, Child1.<Details>, Child2.<Details> ,Child3.<Details> etc

from primary inner join Child1 on Primary.PK=Secondary.FK etc.

I tried modifying it to Left join as you suggested, but Management Studio automatically changes it to Left Outer Join when I save the view.

My data is as follows:

Primary contains all the Transactions that I need, and the Txn_ID.

Each child could contain details of a particular type. It is not mandatory that a row in Primary will have a row in a child.

So if I have 100 rows in Primary, each Child table could contain 0-100 rows.

I want my output to have 100 rows with the details from the child rows added. So am trying to make my data horizontal.

How do I do that?

Thanks a lot again.

Kar

|||

I would suggest that you learn to create your views and queries from text and not use the GUI tool.

|||

Outer Left Join is actually same as Left join. So either you can use TSQL to create the view or if you want to use the UI then u can right click on the link joining the two tables and chose to select all rows from the parent table - this will automatically modify the join from INNER JOIN to the corresponding OUTER JOIN.

Hope that helps,

Kuntal

|||

Thanks for your replies, I tried left outer joins too, but result isnt what I want.

What I want is like a lookup. Please see example below:

Parent table has 1000 rows.

Child 1 has optional details1 for 50 rows.

Child 2 has optional details2 for 50 rows etc.

My view should contian only 1000 rows, with Details1 lookup from Child 1 and so on.

Left Outer join gives me 1100 rows in this example.

TIA

Kar

|||

Your query should look like -

select p.parentColPrimaryKey, c1.child1optkey1, c2.child2optkey2...

from parentTable as p

left outer join child1 as c1 on c1.primaryKey = p.parentColPrimaryKey

left outer join child2 as c2 on c2.primaryKey = p.parentColPrimaryKey

...

This should return you 1000 rows if your parentTable has 1000 rows and if there is no value in c2 corresponds to some p.parentColPrimaryKey, c2.child2optkey2 will be null for that row.

Hope that helps,

Kuntal

|||

Nope, this is what Management Studio generates, but this query does not give me 1000 rows, it gives me 1000 rows, plus rows in c1, plus rows in c2 etc.