SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Give results even if two tables used dont match
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mawtris
Starting Member

USA
2 Posts

Posted - 08/12/2014 :  11:13:05  Show Profile  Reply with Quote
Hi,

I have a table of customer information (ci), but I have to link to another table in order to get the street address (sa).

If I exclude the street address I get 33 results. If I make the link to street address I will only get 30 results.

What I found is that if the ci table field for country is blank and I try linking to sa table then that result will not be shown.

Is there a way to get all 33 results while linking the two tables?

sz1
Constraint Violating Yak Guru

United Kingdom
451 Posts

Posted - 08/12/2014 :  11:14:34  Show Profile  Reply with Quote
Can you provide the query, you will need the correct join depending on what you want to see. You will have 3 addresses blank.

We are the creators of our own reality!

Edited by - sz1 on 08/12/2014 11:16:40
Go to Top of Page

mawtris
Starting Member

USA
2 Posts

Posted - 08/12/2014 :  16:15:09  Show Profile  Reply with Quote
If you didn't guess I am not the SQL wizard..... and am told by my reports guy there isn't anything we can do. I didn't believe him and came here. One question about JOIN. Does it manipulate the data in the system or is it like a temporary thing. I know INSERT or DELETE is something I should never touch in my code as IT would kill me. Thanks in advance!

Here is the code.
select xgv.room_resort, COALESCE(deposit_paid, 0) as deposit_paid,
xgv.deposit_request, (case
when xgv.insert_user = '295' then 'Online'
when xgv.insert_user = '296' then 'Online'
when xgv.insert_user = '54' then 'Property'
else 'CRES'
end) "Booked By", xgv.payment_method, xgv.insert_date,
xgv.confirmation_no, xgv.arrival, xgv.guest_first_name,
xgv.guest_last_name, xgv.email, xgv.guest_phone, xgv.state,
xgv.country, xgv.no_of_rooms, xgv.company_name,
xgv.group_name, COALESCE(rate_code, ' ') as rate_code, xgv.comments, na.address1
from OPERA.xanterra_general_view xgv, opera.name_address na
where xgv.trunc_arrival >= sysdate - 1
and xgv.trunc_arrival <= sysdate + 7
and (xgv.guarantee_code = 'DUE' OR xgv.guarantee_code = 'CID')
and xgv.room_resort like 'OH%'
and xgv.market_code != 'COMP'
and xgv.guest_name_id=na.name_id
and xgv.room_category_label != 'PM'
and xgv.resv_status != 'CANCELLED';

Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1445 Posts

Posted - 08/12/2014 :  21:54:26  Show Profile  Reply with Quote
quote:
Originally posted by mawtris

Hi,

I have a table of customer information (ci), but I have to link to another table in order to get the street address (sa).

If I exclude the street address I get 33 results. If I make the link to street address I will only get 30 results.

What I found is that if the ci table field for country is blank and I try linking to sa table then that result will not be shown.

Is there a way to get all 33 results while linking the two tables?




Use a left join
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
451 Posts

Posted - 08/13/2014 :  04:14:33  Show Profile  Reply with Quote
By the looks of things your code will be using a full join meaning matches needed on both tables, so you need to try as gbritton suggests a left join, you will need to change the on part of the code below to match your table join keys:

select xgv.room_resort, COALESCE(deposit_paid, 0) as deposit_paid,
xgv.deposit_request, (case
when xgv.insert_user = '295' then 'Online'
when xgv.insert_user = '296' then 'Online'
when xgv.insert_user = '54' then 'Property'
else 'CRES'
end) "Booked By", xgv.payment_method, xgv.insert_date,
xgv.confirmation_no, xgv.arrival, xgv.guest_first_name,
xgv.guest_last_name, xgv.email, xgv.guest_phone, xgv.state,
xgv.country, xgv.no_of_rooms, xgv.company_name,
xgv.group_name, COALESCE(rate_code, ' ') as rate_code, xgv.comments, na.address1
from OPERA.xanterra_general_view xgv
LEFT JOIN opera.name_address na
On xgv.ciID = na.saID
-- you need to define the correct relationship here for the join
where xgv.trunc_arrival >= sysdate - 1
and xgv.trunc_arrival <= sysdate + 7
and (xgv.guarantee_code = 'DUE' OR xgv.guarantee_code = 'CID')
and xgv.room_resort like 'OH%'
and xgv.market_code != 'COMP'
and xgv.guest_name_id=na.name_id
and xgv.room_category_label != 'PM'
and xgv.resv_status != 'CANCELLED'

We are the creators of our own reality!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000