Tricky SQL statement. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Tricky SQL statement.

Author

Topic

mfazio
Starting Member

17 Posts

Posted - 2007-10-07 : 04:12:12

Hi guys,

Im trying to devise a statement with will return a true/false value based on if a field in a related table is null or not.

That is I have two tables: ServiceRepairOrders (Parent Table), LoanPhones (Child Table) related by the field "IMEI_ESN", which is a 15 character string.

When returning a "LoanPhone" record, i need one field "InStore", to return true/false based on if one or more related "CompletionDate" fields in ServiceRepairOrders is null or not.

At the moment I have the following:

SELECT LoanPhones.IMEI_ESN, LoanPhones.BoxNumber, LoanPhones.Make, LoanPhones.Model, [ServiceRepairOrders].[CompletionDate] Is Null AS InStore
FROM LoanPhones INNER JOIN ServiceRepairOrders ON LoanPhones.IMEI_ESN = ServiceRepairOrders.LoanPhoneIMEI_ESN;

But i need to ensure its a "one or more" relationship. That is, if all related service repair orders are complete (i.e. a completion date is supplied - not null), then the IsStore should be "True", otherwise false.

Kristen
Test

22859 Posts

Posted - 2007-10-07 : 12:44:30

SELECT LoanPhones.IMEI_ESN, LoanPhones.BoxNumber, LoanPhones.Make, LoanPhones.Model,
CASE WHEN EXISTS (SELECT * FROM ServiceRepairOrders WHERE LoanPhones.IMEI_ESN = ServiceRepairOrders.LoanPhoneIMEI_ESN) THEN True ELSE False END AS InStore
FROM LoanPhones

Note that your INNER JOIN would have only included rows from LoanPhones where one, or more, row(s) existed in ServiceRepairOrders, which from the way I read your requirement was NOT what you wanted, but I mention it in case I have misunderstood

Kristen

renu
Starting Member

47 Posts

Posted - 2007-10-08 : 02:16:11

select distinct s1.IMEI_ESN ,s.completiondate,case when s.completiondate is not null then 'true' else 'false' end as isstore
from ServiceRepairOrders as s join LoanPhones as s1
on s.IMEI_ESN =s1.IMEI_ESN

renu
Starting Member

47 Posts

Posted - 2007-10-08 : 02:17:27

is the above query is correct?

Kristen
Test

22859 Posts

Posted - 2007-10-08 : 02:19:27

Note that the DISTINCT requires a sort and duplicate removal.

If the EXISTS in the SELECT is "slow" then you could try:

SELECT LoanPhones.IMEI_ESN, LoanPhones.BoxNumber, LoanPhones.Make, LoanPhones.Model,
True AS InStore
FROM LoanPhones
WHERE EXISTS (SELECT * FROM ServiceRepairOrders WHERE LoanPhones.IMEI_ESN = ServiceRepairOrders.LoanPhoneIMEI_ESN)
UNION ALL
SELECT LoanPhones.IMEI_ESN, LoanPhones.BoxNumber, LoanPhones.Make, LoanPhones.Model,
False AS InStore
FROM LoanPhones
WHERE NOT EXISTS (SELECT * FROM ServiceRepairOrders WHERE LoanPhones.IMEI_ESN = ServiceRepairOrders.LoanPhoneIMEI_ESN)

Kristen

Kristen
Test

22859 Posts

Posted - 2007-10-08 : 02:21:09

"is the above query is correct?"

select distinct s1.IMEI_ESN ,s.completiondate,case when s.completiondate is not null then 'true' else 'false' end as isstore
from ServiceRepairOrders as s RIGHT OUTER JOIN join LoanPhones as s1
on s.IMEI_ESN =s1.IMEI_ESN

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources