Give results even if two tables used dont match

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Give results even if two tables used dont match

Author

Topic

mawtris
Starting Member

2 Posts

Posted - 2014-08-12 : 11:13:05

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
Aged Yak Warrior

555 Posts

Posted - 2014-08-12 : 11:14:34

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!

mawtris
Starting Member

2 Posts

Posted - 2014-08-12 : 16:15:09

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

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-12 : 21:54:26

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

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-08-13 : 04:14:33

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!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources