Help with Stored Procedure

[Bigced21]

I'm having a problem displaying to different phone #'s, one for the contractor and one for the owner. Actually the results I get on the phone# is from the owner instead of one from the Contractor and one from the Owner.
Here's my code:

select a.license_number, h.value, e.fname, e.lname, c.phone_1, c.phone_2, a.date_issued, c.public_building_name, c.line_1, c.line_2, c.subdivision,
c.city, c.State_ID, c.Zip, b.Owner_or_Company_Name, b.Owner_or_Company_Phone, f.name, g.name business_name
from lic_license_holderinfo a,
lic_location_link b,
hbc_core.dbo.c_contact_address c,
lic_license_contact_link d,
hbc_core.dbo.c_contact_people e,
hbc_core.dbo.c_county f,
hbc_core.dbo.c_contact_business g,
lic_condition_data h
where c.id = b.c_contact_address_id
and h.license_holder_info_id = a.id
and a.id = b.license_holderinfo_id
and e.id = d.c_contact_people_id
and d.id = a.primary_contact_link_id
and g.id = d.c_contact_business_id
and f.id = c.county_id
and a.id = 26235
and h.condition_data_def_id = 14

 

[nigelrivett]

A clue as to what data you are trying to get would be good.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[r937]

well, i think i understand what you're trying to get (two separate phone numbers), but it's not clear how your tables are related, specifically, how the Owners and Contractors are related to the other tables

i think you'll need another join

by the way, your table list syntax is awfully difficult to follow

here's your query re-written with JOIN syntax

this might make it easier to add that additional join

select a.license_number
     , h.value
     , e.fname
     , e.lname
     , c.phone_1
     , c.phone_2
     , a.date_issued
     , c.public_building_name
     , c.line_1
     , c.line_2
     , c.subdivision
     , c.city
     , c.State_ID
     , c.Zip
     , b.Owner_or_Company_Name
     , b.Owner_or_Company_Phone
     , f.name
     , g.name business_name
  from lic_license_holderinfo  a
inner
  join lic_location_link  b
    on a.id 
     = b.license_holderinfo_id
inner
  join hbc_core.dbo.c_contact_address  c
    on b.c_contact_address_id 
     = c.id  
inner
  join lic_license_contact_link  d
    on a.primary_contact_link_id
     = d.id 
inner
  join hbc_core.dbo.c_contact_people  e
    on d.c_contact_people_id
     = e.id 
inner
  join hbc_core.dbo.c_county  f
    on c.county_id
     = f.id
inner
  join hbc_core.dbo.c_contact_business  g
    on d.c_contact_business_id
     = g.id
inner
  join lic_condition_data h
    on a.id
     = h.license_holder_info_id 
   and h.condition_data_def_id 
     = 14
 where a.id = 26235

rudy

http://r937.com/

SQL Consulting

 

[Bigced21]

I'll have to agree, it is mind boggling, but I figured it out the problem.
Thanks you guys!!!!
Seriously!!!!

 

[PHV]

Bigced21, thanks for sharing.
Can please tell the members how you solved your problem ?

 

[Bigced21]

I had to double up on a table and change a couple of where clauses around. Changes/Additions I made will have 2 stars.
Here ya go:
select
a.license_number,
h.value,
e.fname,
e.lname,
a.date_issued,
c.public_building_name,
** - i.line_1, i.line_2,
c.subdivision, c.phone_1, c.phone_2,
** - i.city, i.State_ID, i.Zip,
b.Owner_or_Company_Name, b.Owner_or_Company_Phone, f.[name] as County, g.[name] as business_name
from lic_license_holderinfo a,
lic_location_link b,
hbc_core.dbo.c_contact_address c,
lic_license_contact_link d,
hbc_core.dbo.c_contact_people e,
hbc_core.dbo.c_county f,
hbc_core.dbo.c_contact_business g,
lic_condition_data h,
** - hbc_core.dbo.c_contact_address i
where ** - i.id = b.c_contact_address_id
and ** - b.license_holderinfo_id = a.id
and ** - c.id = d.c_contact_address_id
and d.id = a.primary_contact_link_id
and e.id = d.c_contact_people_id
and g.id = d.c_contact_business_id
and ** - f.id = i.county_id
and h.license_holder_info_id = a.id
and h.condition_data_def_id = 14
and a.id = 26235