This part is solved, please see my question at the bottom of this topic
Hi, my problem is the following, doesn't seem too hard but it's been puzzling me for hours now, and haven't solved it yet, so I need your help!
I'm creating an application to read from an existing db running on SQL-server 2000. This db contains the tables: tClient, tCommunication and tAddress. All of the three tables share a field called nClient which is the unique key in tClient, but each client can have multiple adresses and communication (phone/email etc), so several nClient can occur with the same value in tCommunication and tAddress.
I want to run a query which searches for a text (%blah%) in several fields in any of the tables, but I don't want the query to return the same client more than once, which now does happen if a client has more than one address. Help me! I want to to either return the clients with only the address that contained the %blah% or only the first address it finds for the client. Please help!
Simplified version of my query is like this:
SELECT sLastname, sStreet, sCommunication FROM tClient
LEFT JOIN tCommunication ON tClient.nClient = tCommunication.nClient
LEFT JOIN tAddress ON tClient.nClient = tAddress.nClient
WHERE tClient.sLastname LIKE '%blah%'
OR tClient.sCompanyName LIKE '%blah%'
OR tClient.sClient LIKE '%blah%'
OR tClient.sFirstname LIKE '%blah%'
OR tClient.mMemo LIKE '%blah%'
OR tCommunication.sCommunication LIKE '%blah%'
OR tAddress.sStreet LIKE '%blah%'
OR tAddress.sNumber LIKE '%blah%'
OR tAddress.sZip LIKE '%blah%'
OR tAddress.sCity LIKE '%blah%';
Hi,
if you just want to retrieve one rows per customer you have to aggregate the fields you want to display, something like:
SELECT MAX(sLastname) as LastName, MAX(sStreet) as Street, MAX(sCommunication) as Communication
FROM tClient (...)
GROUP BY CustomerId --If you have one of this
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
Thanks, but it doesn't seem to solve my problem. I tried this but all it returns is the last lastname, last streetname from the complete database etc.... (All starting with a 'z')
Maybe I can do something like this:
SELECT * FROM tClient INNER JOIN (just one record from) tAddress ON tAddress.nClient = tClient.nClient WHERE tClient.sLastName LIKE '%blah%';
Is there any function I can use in the red part of this query?
|||Did you forget about the group by ? There is no such thing (like a keyword) for the red part of your query. I guess you misunderstood my explanation. See the samples below that are based ont he northwind data:
--Will get multiple results as your first post was of
Select CompanyName, ContactName,ContactTitle
From CUstomers
Inner join Orders
ON Orders.CustomerId = Customers.CustomerId
GO
--Will get only one result with values at the end of the alphabet
--bacause you didn��t group by customer
Select MAX(CompanyName), MAX(ContactName),MAX(ContactTitle)
From CUstomers
Inner join Orders
ON Orders.CustomerId = Customers.CustomerId
GO
--Will get only one result per customerid, expand the group by if
--you want to be more specific on the single customer
Select MAX(CompanyName), MAX(ContactName),MAX(ContactTitle)
From CUstomers C
Inner join Orders
ON Orders.CustomerId = C.CustomerId
Group by C.CustomerId --Thats the interesting part.
Its hard to tell you a possible solution without knowing your DDL of the tables. Perhpas you could tell us something more about the structure and the data within to get you a more closer solution if the above doesnt work. If you have more than one client per CustomerId (if you have such equivalent you have to deal with more than the max function) But for the easy thing above that should be enough, perhaps this is also transferable to your problem. Tell me if that worked for you.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||Yes, thanks Jens, that's it! You helped me a lot.
Ok, now working on a similar query, let's say like this:
Select MAX(CompanyName), MAX(ContactName),MAX(ContactTitle),ISNOTNULL(MemoField,1)
From CUstomers C
Inner join Orders
ON Orders.CustomerId = C.CustomerId
Group by C.CustomerId
I've added the IsNotNull(...) bit, but it won't work. This field is a LongVarChar-type field in the left table (Customers in the example), and I just want the query to return a TRUE or FALSE or whatsover telling the memofield is filled-in or not. Thing is, the LongVarChar field can't be processed by the agerrate functions MAX, MIN, etc... and won't be accepted by GROUP BY. What can I do?
|||Hi,should be something like
Select MAX(CompanyName), MAX(ContactName),MAX(ContactTitle), (CASE DATALENGTH(MemoField) WHEN 0 THEN 0 ELSE 1 END) AS IsColumnFilled
From CUstomers C
Inner join Orders
ON Orders.CustomerId = C.CustomerId
Group by C.CustomerId
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
No comments:
Post a Comment