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
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Help To avoid repetitions

Author  Topic 

YogeshDesai
Posting Yak Master

136 Posts
Posted - 2007-04-16 : 06:12:03
Hi,

I have one database abc and i need to find out some data for that i m using 6 tables of that database.
eg
a,b,c,d,e,f and the tables have one unique column that is 'U'
So I have return one query.But the actual my problem it's producing data repetitions. I have tried all the possibilites to link the tables.I got two more field common in three tables,I sucess to reduce the rows of repetitve data,But still it's Data Redunancy.


select pnr_pax.pnr_id,
pnr_pax.pnr_pax_name,

pnr_hfx_gfxs.service_code,
pnr_hfx_gfxs.action_code,
pnr_hfx_gfxs.variable_data,
pnr_hfx_gfxs.ticket_no,
pnr_hfx_gfxs.activity_date,
pnr_hfx_gfxs.activity_time,

passenger_name_records.rec_locator,
passenger_name_records.pnr_creation_date,
passenger_name_records.pnr_creation_time,
passenger_name_records.pos_crs_pnr_addr,
passenger_name_records.pos_crs_pnt_addr,

pnr_itns.board_point,
pnr_itns.off_point,
pnr_itns.pnr_itn_id,
pnr_itns.airline_designator,
pnr_itns.flight_number,
pnr_itns.departure_date,
pnr_itns.departure_time,
pnr_itns.arrival_time,
pnr_itns.booking_status,
pnr_itns.city_code,
pnr_itns.old_booking_status,

pnr_pre_res_seat.prs_row_no,
pnr_pre_res_seat.prs_flight_no,
pnr_pre_res_seat.prs_letter,
pnr_pre_res_seat.prs_cur_status_code,
pnr_pre_res_seat.smoking_seat_ind,
pnr_pre_res_seat.nonsmoking_seat_ind,
pnr_pre_res_seat.window_seat_ind,
pnr_pre_res_seat.aisle_seat_ind,
pnr_pre_res_seat.left_side_seat_ind,
pnr_pre_res_seat.exit_seat_ind,
pnr_pre_res_seat.right_side_seat_ind,
pnr_pre_res_seat.bulkhead_seat_ind,
pnr_pre_res_seat.upper_comp_seat_ind,
pnr_pre_res_seat.over_wing_seat_ind

from pnr_pax,
pnr_hfx_gfxs,
passenger_name_records,
pnr_itns,
pnr_pre_res_seat

where
pnr_pax.pax_id = pnr_hfx_gfxs.pnr_pax_id
and passenger_name_records.id = pnr_pax.pnr_id
and passenger_name_records.id = pnr_hfx_gfxs.pnr_id
and passenger_name_records.id = pnr_itns.pnr_id
and pnr_itns.pnr_itn_id = pnr_hfx_gfxs.pnr_itn_pnr_itn_id
and passenger_name_records.id = pnr_pre_res_seat.pnr_id

when running this query it's giving me redudant data please find the screenshot for more details.Help Required.

ScreenShot of result-set


Your help highly appriceated.











Yogesh V. Desai. | SQLDBA|

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-04-16 : 06:16:08
you could try DISTINCT keyword if the tables allow 1-many relationship.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

YogeshDesai
Posting Yak Master

136 Posts
Posted - 2007-04-16 : 08:37:27
Hi Peter, thanks for your valuable suggestion but query did not allow to use the distinct clause.
quote:
Originally posted by Peso

you could try DISTINCT keyword if the tables allow 1-many relationship.


Peter Larsson
Helsingborg, Sweden


Yogesh V. Desai. | SQLDBA|
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-04-16 : 08:39:49
Huh?

You are not allowed to use DISTINCT? SQL Server told you this?
select distinct pnr_pax.pnr_id,
pnr_pax.pnr_pax_name,

pnr_hfx_gfxs.service_code,
pnr_hfx_gfxs.action_code,
pnr_hfx_gfxs.variable_data,
pnr_hfx_gfxs.ticket_no,
pnr_hfx_gfxs.activity_date,
pnr_hfx_gfxs.activity_time,

passenger_name_records.rec_locator,
passenger_name_records.pnr_creation_date,
passenger_name_records.pnr_creation_time,
passenger_name_records.pos_crs_pnr_addr,
passenger_name_records.pos_crs_pnt_addr,

pnr_itns.board_point,
pnr_itns.off_point,
pnr_itns.pnr_itn_id,
pnr_itns.airline_designator,
pnr_itns.flight_number,
pnr_itns.departure_date,
pnr_itns.departure_time,
pnr_itns.arrival_time,
pnr_itns.booking_status,
pnr_itns.city_code,
pnr_itns.old_booking_status,

pnr_pre_res_seat.prs_row_no,
pnr_pre_res_seat.prs_flight_no,
pnr_pre_res_seat.prs_letter,
pnr_pre_res_seat.prs_cur_status_code,
pnr_pre_res_seat.smoking_seat_ind,
pnr_pre_res_seat.nonsmoking_seat_ind,
pnr_pre_res_seat.window_seat_ind,
pnr_pre_res_seat.aisle_seat_ind,
pnr_pre_res_seat.left_side_seat_ind,
pnr_pre_res_seat.exit_seat_ind,
pnr_pre_res_seat.right_side_seat_ind,
pnr_pre_res_seat.bulkhead_seat_ind,
pnr_pre_res_seat.upper_comp_seat_ind,
pnr_pre_res_seat.over_wing_seat_ind

from pnr_pax,
pnr_hfx_gfxs,
passenger_name_records,
pnr_itns,
pnr_pre_res_seat

where 
pnr_pax.pax_id = pnr_hfx_gfxs.pnr_pax_id
and passenger_name_records.id = pnr_pax.pnr_id
and passenger_name_records.id = pnr_hfx_gfxs.pnr_id
and passenger_name_records.id = pnr_itns.pnr_id
and pnr_itns.pnr_itn_id = pnr_hfx_gfxs.pnr_itn_pnr_itn_id
and passenger_name_records.id = pnr_pre_res_seat.pnr_id


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

YogeshDesai
Posting Yak Master

136 Posts
Posted - 2007-04-17 : 06:10:54
Sorry I was doing some mistake in code.Now I have tried with distinct clause,but still there are repetitions

Yogesh V. Desai. | SQLDBA|
Go to Top of Page

bpgupta
Yak Posting Veteran

75 Posts
Posted - 2007-04-17 : 06:25:07
remove the time and date column from query and then try it with distinct clause .
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-04-17 : 06:27:09
Please describe what you think is a repetition.
Or better, please post some output here and explain what you would like to see different.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

YogeshDesai
Posting Yak Master

136 Posts
Posted - 2007-04-19 : 05:28:14
quote:
Originally posted by Peso

Please describe what you think is a repetition.
Or better, please post some output here and explain what you would like to see different.


Peter Larsson
Helsingborg, Sweden


Hi,

Actually in the above resultset the repetition is
a person is travelling from location A-B,then B-C and C-A on the same pnr No.when I am trying to fetch the records with his others details by joining other table for related information It's the output for that particular person 3*6=18 times (3 for three location and 6 for information from 6 tables) if any table has more three entries for the particular person the repetion increasing more.
and in location point it shows A-B,B-C C-A,again repetiting the same.

Yogesh V. Desai. | SQLDBA|
Go to Top of Page

YogeshDesai
Posting Yak Master

136 Posts
Posted - 2007-04-19 : 05:31:12
quote:
Originally posted by bpgupta

remove the time and date column from query and then try it with distinct clause .


Time and date column is required field dear friend,I can not remove it from query.

Thank you very much for your suggestions.

Yogesh V. Desai. | SQLDBA|
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-04-19 : 05:31:42
And you would want what instead?
Better, please post some output here and explain what you would like to see different.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

YogeshDesai
Posting Yak Master

136 Posts
Posted - 2007-04-19 : 06:03:22
quote:
Originally posted by Peso

And you would want what instead?
Better, please post some output here and explain what you would like to see different.


Peter Larsson
Helsingborg, Sweden

Hi I have attached screen-shot the result I am expecting,
I have higlighted some entries.These passenger have multipal entries but he has different "Rec_Loc" he is travelling to same location but rec_locator is different. When I am adding more table to fetch some more information.It's giving me wrong data



Yogesh V. Desai. | SQLDBA|
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-04-19 : 06:06:22
We can't see the picture.
Please make sure you have saved the picture on a place where we to have read access for.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

YogeshDesai
Posting Yak Master

136 Posts
Posted - 2007-04-19 : 06:26:35
quote:
Originally posted by Peso

And you would want what instead?
Better, please post some output here and explain what you would like to see different.


Peter Larsson
Helsingborg, Sweden



When I add more table the result set is coming like this here i am attaching one more screen shot for you reference.
Please notice the highlighted fields.


Yogesh V. Desai. | SQLDBA|
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-04-19 : 06:29:18
ONE MORE TIME! WE CAN'T SEE PICTURED SAVED IN THE ROOT OF YOUR C: DRIVE!


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

YogeshDesai
Posting Yak Master

136 Posts
Posted - 2007-04-19 : 09:04:35
quote:
Originally posted by Peso

ONE MORE TIME! WE CAN'T SEE PICTURED SAVED IN THE ROOT OF YOUR C: DRIVE!


Peter Larsson
Helsingborg, Sweden

1 st pic
Result expected



2 nd Pic
Repeated records



Yogesh V. Desai. | SQLDBA|
Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts
Posted - 2007-04-19 : 09:11:33
Let me help you:

1. Upload your screenshots to the image hosting sites like http://imageshack.us/
2. Copy the url to the image and put the url inside [img] tags in your post.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site