-
Search Query Help
So I am in a bit of a jam and dont know how to do this.
I have a search screen where people will enter textboxes to search a parent table. But this parent table has a child table that it can have a one to many relationship with. The search screen needs to be able to return the parents reference if a field of the child is searched.
So the tables are
Investigation -> Parent
Clients -> reference/Child
Investigation
-------------
InvestigationID
Field1
Field2
Field3
.....
Clients
--------------
ClientID
InvestigationID
ClientNumber
ClientName
................
So from the search screen there are say 4 text boxes.
Poeple can do a search for Field1, Field2 as well as ClientNumber or ClientName.
I would like to return
InvestigationID, Field1, Field2, ClientNumber, ClientName.
My SQL looks like this. But I am not doing it right because I did a join. What I need to do is a subquery but I dont know how.
-------------------------------------------
SELECT matters.matterID AS matterID,
IsNull(dbo.udf_getClientMatter(matters.matterID), '') AS clientMatter,
IsNull(matters.title, '') AS Title,
IsNull(descriptionSubject,'') AS Description,
IsNull((Select RTrim(statusDescription) FROM status WHERE status.statusID = matters.statusID ), '') AS Status,
IsNull((Select RTrim(lastActionDescription) FROM lastAction WHERE lastAction.lastActionID = Matters.lastActionID ), '') AS [Last Action],
IsNull(CONVERT(char(19), dateOpened, 101), '') AS dateOpened,
IsNull(CONVERT(char(19), dateOfLastAction, 101), '') AS dateOfLastAction,
IsNull(dbo.udf_getCaseTypes(matters.matterID), '') AS caseTypes,
IsNull(dbo.udf_getForumTypes(matters.matterID), '') AS forumTypes
FROM Matters
FROM matters JOIN clients
ON clients.matterID = matters.matterID
WHERE 1=1
ORDER BY matterID, dateOpened DESC
-----------------------------------------------
This return 4 results for my investigation when its supposed to return 1 becase I have 4 clients connected to my investigation table.
Any help would be appreciated.
Tags for this Thread
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
|
|