Returning results from the same table in one row

[slobad23]

I have a table called UDF which contains data linked to the data found in another table called CLIENT.

This data in UDF is in groups specified by the column UDF.UDFINDEX. I have linked these two tables by

WHERE udf.udjoin = client.clnum

This has not been a problem in that I have asked it to return results where UDF.UDFINDEX = '7' and I get a single result back for each client record.

What I want now is to return a result where UDF.UDFINDEX = '7' and '56' but there is no record that will contain both of those numbers as each one is an individual record.

So you might have a layout like this

UDJOIN UDFINDEX UDDATE UDVALUE
10001 7 08072009 NULL
10001 56 NULL TMS
10002 56 NULL TMS

When I query this:
...WHERE udf.udfindex = '7' or udf.udfindex = '56'
There are thousands of rows where the UDFINDEX will equate to 56 and I get a load of results back I do not want.

I want a single row for each UDJOIN that contains the value of 56 and 7 in the results.

This has been really hard to try and explain. Please let me know if you have NO idea what I am talking about.

Thanks

 

[simian101]

Perhaps you could show us what you want to see based on the previous example...

Simi

 

[slobad23]

Sounds like a reasonable request

UDJOIN UDFINDEX UDDATE UDVALUE
10001 7 08072009 NULL
10001 56 NULL TMS
10002 56 NULL TMS
10002 56 NULL TMS
10005 56 NULL TMS

There will be records that come back for UDF.UDFINDEX = 56 because it is listed a million times. Here is what I would like to see:

UDJOIN (UDFINDEX) UDDATE UDVALUE
10001 08072009 TMS
10002 06042009 RXR

For the UDDATE there is only one entry listed against the UDJOIN value. There will however be a lot of values that are the same in UDJOIN that contain the same value in UDVALUE which is why when I ask for that, I get a lot more results back than I want. I only want one record per UDJOIN value.

I have been on here before and asked a similar question and I was given sum(case when... which I can't use because it says it won't allow date fields.

The values per record are all dependant on what appears in a field called UDF.UDFINDEX. UDVALUE appears when it is a string and UDDATE has a value other than NULL when a date is present. The table has an individual record per UDFINDEX value.

I hope this makes a little more sense.

 

[simian101]

Try

WHERE udf.udjoin = client.clnum and uddate is not null

Simi

 

[PDreyer]

You are probably mixing 'and' and 'or' without brackets
'and' is evaluated before 'or'

Operator Precedence:
1
~ (Bitwise NOT)

2
* (Multiply), / (Division), % (Modulo)

3
+ (Positive), - (Negative), + (Add), (+ Concatenate), - (Subtract), & (Bitwise AND)

4
=, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)

5
^ (Bitwise Exlusive OR), | (Bitwise OR)

6
NOT

7
AND

8
ALL, ANY, BETWEEN, IN, LIKE, OR, SOME

9
= (Assignment)

 

[r937]

SELECT t.udjoin
     , t.udfindex
     , t.uddate
     , t.udvalue
  FROM ( SELECT udjoin
           FROM udf
          [RED]WHERE udfindex IN ( 7 , 56 )[/RED]
         GROUP
             BY udjoin 
         [RED]HAVING COUNT(*) = 2[/RED] ) AS b
INNER
  JOIN udf AS t
    ON t.udjoin = b.udjoin

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[PDreyer]

You need to be a lot clearer

You want[tt]

UDJOIN (UDFINDEX) UDDATE UDVALUE
10001 08072009 TMS
10002 06042009 RXR[/tt]

where does UDJOIN=10002 get its info from your test data:[tt]

UDJOIN UDFINDEX UDDATE UDVALUE
10001 7 08072009 NULL
10001 56 NULL TMS
10002 56 NULL TMS
10002 56 NULL TMS
10005 56 NULL TMS[/tt]

 

[PDreyer]

Maybe this is what you want?

select UDJOIN, max(UDDATE), max(UDVALUE) 
from udf
join client
  on UDJOIN=clnum
where UDFINDEX in (7,56)
group by UDJOIN