SQL Statement for Accessing Data from Multiple Tab

问题:

I have 7 Tables as per attached following Image. I will either enter Engine Number or Chassis Number and it should show the respective tables information (these tables have only mentioned fields) so all fields can be shown as result. I can use hard coded Engine Number or Chassis Number. Every time of execution of this Query, I will hard code the required Engine/Chassis Number and will get the result. Can anybody please help me to write this query for me? Click Here to See the Tables

回答1:

This might be a starting point for your solution.

SELECT prod.EngineNo AS engNo, prod.ChassisNo, doral.doralNo [, table.column [AS name]]
FROM DOProductSpecsDetais AS prod
INNER JOIN DORAL AS doral  
  ON prod.DOProductSpecsDetailID = doral.DOProductSpecsID
INNER JOIN DOProductDetail AS prodDetail
  ON prod.DOProductDetailID = prodDetail.DOProductDetailID
WHERE prod.ChassisNo = '<input>' OR prod.EngineNo='<input>'

Between the SELECT and the FROM Statement, you can select any column out of your JOIN.

You can cascade as many JOINs as you like...

Which DBMS are you going to use?

One suggestion: Try to simplify the names of your columns, if possible.

One more: If you just started to do Database things, it is always helpful to start a test environment and use a client tool.

回答2:

You can write query something like this:

select * from 
DoProductSpecsDetail tbl1 inner join Doral tbl2 
on tbl1.DoProductSpecsDetailId = tbl2.DoProductSpecsId
inner join DoproductDetail tbl3
on tbl1.DoProductDetailId = tbl3.DoProductDetailId
inner join ProductColor tbl4
on tbl1.ProductColorId = tbl4.ProductColorId
inner join DoDetail tbl5
on tbl3.DeliveryOrderDetailId = tbl5.DeliveryOrderId
inner join ProductMain tbl6
on tbl3.ProductId = tbl6.ProductId
inner join BPMain tbl7
on tbl5.BusinessPartnerId = tbl7.BusinessPartnerId