sql query help

Question

sql query help

koolme_85

Ten Centuries

Points: 1153
More actions
November 30, 2011 at 2:06 pm

#260612

select
pricelist_header_guid
,[customer_name]
,'BKS_US_CONTACT'
,[Contract_Number]
,'Y'
,''
,'null'
,'null'
,'US'
,'CONVERSION'
,EBS_LOB
,'null'
,'Y'
,'USD'
,'-5'
,''
,'US'
from table1
where
high_value is not null
and low_value >= 0 and cust_rate_guid
in
(select distinct cust_rate_guid from BRINKS.PRICING_TEST
where
high_value is not null
and low_value >= 0)
when i am executing the whole query i am getting like 163 records
but when i execute just
select distinct cust_rate_guid from BRINKS.PRICING_TEST
where
high_value is not null
and low_value >= 0
i am getting 75 records
and i need those 75 distinct records in my result set
need help on this one

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ben Teraberry SSChampion Points: 11845 More actions · Answer 1

If you only want a distinct row for each guid then you need to add some sort of aggregating logic to your selection from table1. You're getting more than 75 records because at least some of the guids are duplicated in table1.

Also, see the link before to learn how to help people trying to help you.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

koolme_85 Ten Centuries Points: 1153 More actions · Answer 2

i have 75 guids that are distinct and the records associated to that 75 records are also distinct and the rest 85 are not distinct (assuming there are total 160 records in the table ) but the records corresponding to the duplicate guids are distinct , so i need help with the how to get only those 75 records as a result set of the query a mentioned in my first post !!!

thanks

regards

greg

Possinator Hall of Fame Points: 3422 More actions · Answer 3

Without more info this is just a stab in the dark, but as you are trying to find matches for the rows in the brinks table, perhaps you should try starting with that table then joining table1 to it. Add a group by to limit the records to unique.

Something like (untested):

select

p.cust_rate_guid

,t.pricelist_header_guid

,t.[customer_name]

,'BKS_US_CONTACT'

,t.[Contract_Number]

,'Y'

,''

,'null'

,'US'

,'CONVERSION'

,t.EBS_LOB

,'null'

,'Y'

,'USD'

,'-5'

,''

,'US'

,RecordsInTable1=COUNT(t.cust_rate_guid)

from brinks.pricing_test p

left outer join table1 t on p.cust_rate_guid = t.cust_rate_guid

and t.high_value is not null

and t.low_value >= 0

group by

p.cust_rate_guid

,t.pricelist_header_guid

,t.[customer_name]

,t.[Contract_Number]

,t.EBS_LOB

[font="Courier New"]Looking for a Deadlock Victim Support Group..[/font]

Ben Teraberry SSChampion Points: 11845 More actions · Answer 4

koolme_85 (11/30/2011)
i have 75 guids that are distinct and the records associated to that 75 records are also distinct and the rest 85 are not distinct (assuming there are total 160 records in the table ) but the records corresponding to the duplicate guids are distinct , so i need help with the how to get only those 75 records as a result set of the query a mentioned in my first post !!!
thanks
regards
greg

You want us to guess as to which records you want and which you don't? If you only want one record from table1 for each distinct guid, you need to have some sort of logical basis by which you're choosing which one record that is in the cases where multiple records can be associated with the same guid.

**Edit ... with no table definitions it's hard to say what you're trying to do, but maybe what you're looking for is as simple as this:

selectdistinct

t.pricelist_header_guid

,t.customer_name

,t.BKS_US_CONTACT

,t.Contract_Number

,'Y'

,''

,'null'

,'US'

,'CONVERSION'

,t.EBS_LOB

,'null'

,'Y'

,'USD'

,'-5'

,''

,'US'

from table1 as t

join BRINKS.PRICING_TEST as pt

on pt.cust_rate_guid = t.cust_rate_guid

where t.high_value is not null

and t.low_value >= 0

and pt.high_value is not null

and pt.low_value >= 0;

If this is the case, I would suggest spending some time really trying to understand relational design because this sort of query will be inefficient over larger datasets.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

koolme_85 Ten Centuries Points: 1153 More actions · Answer 5

thanks for all the help guys , i finally figured it out

regards

greg