StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POWhat will be the T-sql Query of the scenario?
text
Body
copied!<p>I have a scenario where there is an apartment and each apartment has flats known as "Property" in my table. Each flat can have multiple appliances like AC, TV, fridge etc. We are maintaining a master table of appliances. </p> <p>Say user has purchased some items for that house like TV, AC and CFL Bulbs. So he would , in the very first time, carry a insert operation in which the details like ApplianceId, DateOfPurchase, Serial# of Product, PropertyId (Id of the flat) are inserted. Till this we are fine no issues.</p> <p>Now our QA tossed a situation where user has entered some wrong serial number to TV. Now he wants to update the record. But in between comes the following conditions:</p> <ol> <li><p>The appliance's serial number must be unique but the two different appliances might have similar serial numbers, that If in my flat there are 4 appliances out of which I have two ceiling fans, a fridge and an AC then the serial number of 1 ceiling fan and AC might be same but the two ceiling fan serial numbers can neither be same.</p></li> <li><p>The check should be carried out for all properties. That is in entire table this should be applicable. It should not be propertywise. <strong>That is If i have an AC with Serial#1234 then , it is not possible that SO guru Jon Skeet can also have an AC with Serial#1234. He can have Refridgrator with the same Serial#. And note we are using serial number in plain numeric form like 123456789901</strong>. No characters are allowed.</p></li> </ol> <p>Please help me out, and inform if any thing is not clear.</p>

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