NIRANJAN G NIRANJAN G - 3 months ago 14
SQL Question

Data from multiple tables returns to many records

I am trying to get data from different tables.
below are the table structure and values and what i tried so far.

uploadtable
uploadId doctypeId
1 1
2 1

MetadataTable
Id doctypeId contentLable controlType fieldtype
1 1 docnumber Textbox varchar
2 1 expiryDate Textbox Datetime

contentTable
Id uploadId doctypeId Label Value
1 1 1 docnumber 123
2 1 1 expiryDate 1/1/2016


When I pass
uploadId
as 1 in where condition then Output should be

uploadId contentLable controlType fieldtype Label Value
1 docnumber Textbox varchar docnumber 123
1 expiryDate Textbox Datetime expiryDate 1/1/2016


Actually O tried in my sql server as below. Actually I need linq query. If I get correct output in sql then I thought of converting it ti linq.

select
upd.uploadId,
meta.contentLable,
meta.controlType,
meta.fieldtype,
content.Label,
content.value
from MetadataTable meta,
contentTable content,
uploadtable upd
where upd.uploadId = content.uploadId
and meta.doctypeId = content.doctypeId
and upd.uploadId = 1


I am getting incorrect output as below.

uploadId contentLable controlType fieldtype Label Value
1 docnumber Textbox varchar docnumber 123
1 docnumber Textbox varchar expiryDate 1/1/2016
1 expiryDate Textbox Datetime docnumber 123
1 expiryDate Textbox Datetime expiryDate 1/1/2016

Answer

Hope this helps. I think this sql should perform what you need:

select upd.uploadId,
    meta.contentLable,
    meta.controlType,
    meta.fieldtype,
    content.Label,
    content.value
from uploadtable upd
join metadataTable meta
on upd.doctypeId = meta.doctypeId
join contentTable content
on upd.doctypeId = content.doctypeId
and upd.uploadId = content.uploadId    <---------- This is what you are missing
where upd.uploadId = 1
  1. Avoid selecting from several tables using from x,y,z - use instead proper join syntax - it will make your queries more readable and will reduce chances of forgetting joining conditions.
  2. When joining to contentTable you need to also join by both the uploadId and the doctypeId - otherwise you will get a Cartesian join/cross join like you did.