如何在sql join查询中列出一个表中的所有数据... [英] How to list all data in one table in sql join query ...

问题描述

如何在sql join查询中列出一个表中的所有数据...我根据类别绑定assetid，assetstatus等作为以下sql查询：

  SELECT  a.AssetStatusId，s .AssetStatus，COUNT（*） as  [Assets]  from  TblAssetStatus s 
  left   join  TblAsset a  on  a.AssetStatusId = s.AssetStatusId 
  INNER  加入 dbo.TblStoreStock  ON  a.StoreStockId = dbo.TblStoreStock.StoreStockId 
  INNER   JOIN  dbo.TblStock  ON  dbo.TblStoreStock.StockId = dbo.TblStock.StockId 
  INNER   JOIN  dbo.TblItem  ON  dbo.TblStock.ItemId = dbo.TblItem.ItemId 
  INNER   JOIN  dbo.TblModel  ON  dbo.TblItem.ModelId = dbo.TblModel.ModelId 
  INNER   JOIN  dbo.TblIemType  ON  dbo.TblModel.ItemTypeId = dbo.TblIemType.ItemTypeId 
  INNER   JOIN  dbo.TblCategory  ON  dbo.TblIemType.CategoryId = dbo.TblCategory.CategoryId 
 其中 TblCategory.CategoryId = '  1' 
  group   by  a.AssetStatusId，s .AssetStatus 

我想显示所有资产状态，即使类别condtn没有。 ..如何 做同样的事情......

解决方案

查看以下链接.... U将获得关于SQL连接的想法。



不同类型的SQL连接 [ ^ ]

SQL - 加入 [ ^ ]

SQL SERVER - JOIN简介 - 基础知识JOIN [ ^ ]

加入sql server，内连接，交叉连接，左外连接，Equi连接，右外连接，全外连接 [ ^ ]

问候，

GVPrabu

试试这个...

  SELECT  a.AssetStatusId，s .AssetStatus，COUNT（*） as  [Assets] 来自 TblAssetStatus s 
  left   join  TblAsset a < span class =code-keyword> on  a.AssetStatusId = s.AssetStatusId 
  INNER   JOIN  dbo.TblStoreStock  ON  a.StoreStockId = dbo.TblStoreStock.StoreStockId 
  INNER   JOI N  dbo.TblStock  ON  dbo.TblStoreStock.StockId = dbo.TblStock.StockId 
  INNER   JOIN  dbo.TblItem  ON  dbo.TblStock.ItemId = dbo.TblItem.ItemId 
  INNER   JOIN  dbo.TblModel  ON  dbo .TblItem.ModelId = dbo.TblModel.ModelId 
  INNER   JOIN  dbo.TblIemType  ON  dbo.TblModel.ItemTypeId = dbo.TblIemType.ItemTypeId 
   Left   < span class =code-keyword> JOIN  dbo.TblCategory  ON  dbo.TblIemType.CategoryId = dbo.TblCategory.CategoryId 
   -   其中TblCategory.CategoryId ='1' 
  group   by  a.AssetStatusId，s .AssetStatus 

快乐编码！

:)

How to list all data in one table in sql join query ...I am binding assetid,assetstatus etc based on category as the following sql query:

SELECT    a.AssetStatusId, s.AssetStatus, COUNT(*) as [Assets] from TblAssetStatus s
left join TblAsset a on a.AssetStatusId = s.AssetStatusId
INNER JOIN  dbo.TblStoreStock ON a.StoreStockId = dbo.TblStoreStock.StoreStockId
INNER JOIN  dbo.TblStock ON dbo.TblStoreStock.StockId = dbo.TblStock.StockId
INNER JOIN  dbo.TblItem ON dbo.TblStock.ItemId = dbo.TblItem.ItemId
INNER JOIN dbo.TblModel ON dbo.TblItem.ModelId = dbo.TblModel.ModelId
INNER JOIN dbo.TblIemType ON dbo.TblModel.ItemTypeId = dbo.TblIemType.ItemTypeId
INNER JOIN dbo.TblCategory ON dbo.TblIemType.CategoryId = dbo.TblCategory.CategoryId
where TblCategory.CategoryId='1'
 group by a.AssetStatusId, s.AssetStatus

I want to show all asset status even if the category condtn isn''t having it...How to do the same...

解决方案

Hi,

Check the following links.... U will get Ideas about SQL Joins.

Different Types of SQL Joins[^]
SQL - Joins [^]
SQL SERVER – Introduction to JOINs – Basic of JOINs[^]
Joins in sql server, Inner Join,Cross Join,Left Outer Join,Equi join, Right Outer Join, Full Outer Join [^]
Regards,
GVPrabu

Try this...

SELECT    a.AssetStatusId, s.AssetStatus, COUNT(*) as [Assets] from TblAssetStatus s
left join TblAsset a on a.AssetStatusId = s.AssetStatusId
INNER JOIN  dbo.TblStoreStock ON a.StoreStockId = dbo.TblStoreStock.StoreStockId
INNER JOIN  dbo.TblStock ON dbo.TblStoreStock.StockId = dbo.TblStock.StockId
INNER JOIN  dbo.TblItem ON dbo.TblStock.ItemId = dbo.TblItem.ItemId
INNER JOIN dbo.TblModel ON dbo.TblItem.ModelId = dbo.TblModel.ModelId
INNER JOIN dbo.TblIemType ON dbo.TblModel.ItemTypeId = dbo.TblIemType.ItemTypeId
Left JOIN dbo.TblCategory ON dbo.TblIemType.CategoryId = dbo.TblCategory.CategoryId
--where TblCategory.CategoryId='1'
 group by a.AssetStatusId, s.AssetStatus

Happy Coding!
:)

这篇关于如何在sql join查询中列出一个表中的所有数据...的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！