-
Select & subquery
I am trying to do select from several tables:
select
(case receivedrowId when 0 then '' else
(select distinct documenttype+document from documentdetails dd
where dd.rowId=receivedRowId)end)as Invoice,
(case receivedRowID when 0 then
(case (select count(*) from Customers c where C.[Name]=[Name])
when 0 then ''
when 1 then (select C.Name from Customers c where C.[Name]=[Name] )
else 'Multiple matches' end)
else
(select distinct d.customer from documents d join documentdetails dd on d.documenttype=dd.documenttype and d.document=dd.document
where dd.rowId=receivedRowID)end)as Account#,
(case receivedRowID when 0 then [Name] else
(select distinct d.[Name] from documents d join documentdetails dd on d.documenttype=dd.documenttype and d.document=dd.document
where dd.rowId=receivedRowID)end) as CustomerName,
(case receivedrowid when 0
then (case (select count(*) from Customers c where C.[Name]=[Name])
when 0 then ''
when 1 then (select C.Phone from Customers c where C.[Name]=[Name] )
else 'Multiple matches' end)
else (select distinct d.busphone from documents d join documentdetails dd on d.documenttype=dd.documenttype and d.document=dd.document
where dd.rowId=receivedRowID) end) as Phone,
StoredTires.Tag,
StoredTires.ReceivedRowID,
StoredTires.ReturnedRowID,
StoredTires.Memo,
StoredTires.Location, StoredTires.Type,
StoredTires.TreadRemaining, StoredTires.NewTread, (CASE StoredTires.Wheel
WHEN 1 THEN 'Yes'
ELSE ''
END) as Wheel,
(case receivedrowId when 0 then StoredTires.[Date] else
(select distinct d.InvoiceDate from documents d join documentdetails dd on d.documenttype=dd.documenttype and d.document=dd.document
where dd.rowId=receivedRowID)end)as InvoiceDate
from storedtires
I am saying 'multiple matches' because it seems that I get not only the particular Phone for this record but all other phones for whole table.
I have difficulties retrieving phone & account number because of that.
Can anyone help?
thank you.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|