Views - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Views

Author

Topic

kien
Starting Member

27 Posts

Posted - 2002-07-05 : 09:30:38

Hi everyone. Can someone please help me solve a quick question?

I am trying to create a view involving 3 tables (tblRecordSet, tblMaterials, tblFabrics).

The problem I'm having is that the view only displays records with an existing FabricName (the rest being "null"). How do i get it to display all data?

FabricID is the primary key in tblFabrics, but not a primary key in tblMaterials.

Thanks for your help!

Kien

-----
SELECT TOP 100 PERCENT rs.RecordID, rs.Latitude, rs.Longitude, rs.Depth, m.RockNote, rs.FeatureName, dbo.tblFabrics.FabricName

FROM dbo.tblRecordSet rs INNER JOIN
dbo.tblMaterials m ON rs.RecordID = m.RecordID INNER JOIN
dbo.tblFabrics ON m.FabricID = dbo.tblFabrics.FabricID

joshb
Yak Posting Veteran

52 Posts

Posted - 2002-07-05 : 10:31:07

If I understand your question I think you are looking for something like this:

SELECT TOP 100 PERCENT rs.RecordID, rs.Latitude, rs.Longitude, rs.Depth, m.RockNote, rs.FeatureName, dbo.tblFabrics.FabricName

FROM dbo.tblRecordSet rs INNER JOIN
dbo.tblMaterials m ON rs.RecordID = m.RecordID LEFT OUTER JOIN
dbo.tblFabrics ON m.FabricID = dbo.tblFabrics.FabricID

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-07-05 : 10:58:31

Heh, read up on inner vs outer (left or right joins)... Trust me, apparently it's a really basic and fundamental concept (like I had any clue what they were a month ago