One-to-many query, returns double rows

Hot Topic

SQL Server 2005 Nov 7 GA Installation
Same article in different Publications
SELECT permission denied on object 'database object', database 'databasename', owner 'dbo'.
Out-of-Memory error on Sort Transformation
Scope defined on Member Properties
How to restore SQL 2000 DB to SQL 2005
Only functions and extended stored procedures can be executed from within a function.
Order by clause in View doesn't order.
is it possible to know whether report still executing
Does SSIS have these features????

SQL Server

TRUNCATION OR CODE PAGE
SSIS Package runs in BI but not VB.Net
How to 'catch' the Sql-Error into a Mail
Integration Services
SQL Profiler 2005 showing inaccurate duration
SQL Express Family CTP Now Available
sp_dboption
Stored procedure issues...
Execution not found when using Dynamic Windows Impersonation
How to UPDATE ID in SQL

One-to-many query, returns double rows

 

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%';
 

Answer this question

One-to-many query, returns double rows

  • Airan

    Thanks again, Jens. Works fine!

  • toladyh

    Yes, thanks Jens, that's it! You helped me a lot.

  • SandpointGuy

    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


  • Adam Norsworthy

    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
    ---

  • alvi_du

    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
    ---

  • Saurabh Bhatia - MSFT

    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


  • robrich

    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
    ---
  • One-to-many query, returns double rows
Answer this question