StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL Query relationship coding
text
Body
copied!<p>Need some help making this work please. Some help please to ensure I always return just one row per <strong>ProductID</strong>. My Product Table contains base information on Products which can be properties in this case. It links to the Properties table via a ProductCode. </p> <p>What I would like is to have the query return just the one row which it really should. Essentially, the row should display the PropertyLabel whether or not there are values in the PropertyProductValues table for that particular propertyLabel. The code below returns a bunch of rows which really it shouldn't, or I don't want it to. And I would appreciate help fine tuning this please. thanks in Advance. </p> <pre><code>Create Table Product { ProductID uniqueidentifier, ProductName Varchar(50), ProductCode Varchar(50) } Create Table Properties { PropertyID uniqueidentifier, PropertyProductcode Varchar(50) } Create Table PropertyProductValues { ID uniqueidentifier, PropertyID uniqueidentifier, ProductID uniqueidentifier, PropertyLabel Varchar(50), UnitID uniqueidentifier } Create Table unit { UnitID uniqueidentifier, Unit Varchar(50) } Select productid , PropertyLabel PropertyValue PropertyUnit from Product inner join Properties on ProductCode = PropertyProductCode left join PropertyProductValues on Properties.PropertyID = ProductProductValues.PropertyID left join Unit on PropertyValues.UnitID = Unit.UnitID Where ltrim(rtrim(lower(PropertyLabel))) = lower('Special') </code></pre> <h2>Sample Data</h2> <pre><code>Product Table b0359c76-8622-4006-82f2-1b4c91a8f6b3 Brown Building YSHJT aba475c4-5a5c-483a-9faa-67f67ff9672d Sket Building GTJHD54 8f645348-8871-4fad-8c85-9fc6a1169b33 HOUSE 9 DGFS345 Properties Table 27c6485b-f7a5-4243-9304-71939230964d DGFS345 88e911e5-bf40-4f14-89cc-4ed2984c342e GTJHD54 d217cc14-6fcb-4251-a6d7-8fd1b3398a32 YSHJT 689cfd9d-bc87-4e23-afb3-d6cbf1e961f6 FFFSRW bf7ae151-2e3f-4e0a-bf8b-d44ef30cf276 WYSJD PropertyProductValues Table 6cf47434-c834-455d-a606-3d10cb9f7ab3 b0359c76-8622-4006-82f2-1b4c91a8f6b3 b0359c76-8622-4006-82f2-1b4c91a8f6b3 Storey 82ee0f80-afe4-45c2-877c-e79ce286170f 27c6485b-f7a5-4243-9304-71939230964d b0359c76-8622-4006-82f2-1b4c91a8f6b3 Bedrooms 5ff7f809-6b1f-4d6c-a125-29ce67f097d7 27c6485b-f7a5-4243-9304-71939230964d 8f645348-8871-4fad-8c85-9fc6a1169b33 Bathrooms 7f634b81-1212-4223-af42-29b4dc2bafcc 27c6485b-f7a5-4243-9304-71939230964d aba475c4-5a5c-483a-9faa-67f67ff9672d Material cae10884-edf0-4340-bde6-5207e28a07cc 689cfd9d-bc87-4e23-afb3-d6cbf1e961f6 b0359c76-8622-4006-82f2-1b4c91a8f6b3 Basement 83943759-39d7-406e-92ea-4202a9b1d716 bf7ae151-2e3f-4e0a-bf8b-d44ef30cf276 b0359c76-8622-4006-82f2-1b4c91a8f6b3 Storey c045d0ff-34db-4427-9fb7-fd41fc92f310 bf7ae151-2e3f-4e0a-bf8b-d44ef30cf276 aba475c4-5a5c-483a-9faa-67f67ff9672d balcony Unit Table UNITID UNIT 3a4a5216-0704-495e-b0a7-560787e0847a kg 6b4493f6-c2e4-4682-b71d-93cb1893eb73 m 2f2b4fee-9e69-4a71-a098-36e2da71471e l 55c8e5bf-edde-4977-ab7b-8827e337a31e oz 19528647-bd9f-48e0-ba86-b722d5b8ab0e cm cee5cd46-1ae4-4b49-8b1c-9b3e0bd5270f mm </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload