ExamGuru IT/.Net Guru : joining tables in visual linking expert. on ASPFriends.com 'ngfx-crystal' list

.NETGURU

joining tables in visual linking expert.

Messages

Related Types

This message was discovered on ASPFriends.com 'ngfx-crystal' list.
Responses highlighted in red are from those people who are likely to be able to contribute good, authoratitive information to this discussion. They include Microsoft employees, MVP's and others who IMHO contribute well to these kinds of discussions.

Winnie Khoo

Hi There,

I have PurchaseRequest Table (PR) PurchaseRequestLine Table (PRLN). PR
and PRLn are linked to each other by the PRID from PR table.

Scenario 1: I can have a PR without a PRLN existing. I would like to
display all PR and PRLN records regardless of whether PR.PRID =
PRLN.PRID.

**Note: I tried using Left outer join for this. All records will be
displayed but there will be duplicates.

1st Occurance :-
PR Table (Record 1)
PRID: PRQ 1000003
Date : 6/29/2002 12:26:46PM
Status : Cancelled

PRLn Table (Record 1)
PRID : PRQ 1000003
ItemCode : Item 6
Cost : 432.34000
Status : Active

PR Table(Record 2)
PRID : PRQ 1000003
Date : 6/29/2002 12:26:46PM
Status : Cancelled

PRLn Table(Record 2)
PRID : PRQ 1000003
ItemCode: Leong 2 cx
Cost : 34.00000
Status : Active

2nd Occurance (Which Should not occur)
PR Table(Record 1)
PRID : PRQ 1000003
Date : 6/29/2002 12:26:46PM
Status : Cancelled

PRLn Table(Record 1)
PRID : PRQ 1000003
ItemCode : Item 6
Cost : 432.34000
Status : Active

PR Table(Record 2)
PRID :PRQ 1000003
Date : 6/29/2002 12:26:46PM
Status : Cancelled

PRLn Table(Record 2)
PRID : PRQ 1000003
ItemCode : Leong 2 cx
Cost : 34.00000
Status : Active

Scenario 2: I will also have a criteria to display PR & PRLn records
where PR.Status = Delete.

**Note : I tried using the left outer join (in visual linking expert)
for this. It will work here but it will not work when scenario 1 comes
in.

I hope someone can find a solution to this! Thanks

Cheers
Winnie

Reply to this message...

Eric Landes (VIP)

Winnie,
Sorry for the long response. If you have already resolved please =
ignore.

Since it is early in the morning here, and I'm a bit groggy, maybe I'm =
just
being a idiot, but why do you have two tables on the same report that =
you
are not linking? I'm trying to make sense of that, but like I said, I'm =
a
bit groggy this morning.

Let me phrase what I think you are wanting to do, you want all linked
records to display, and if there is no link on either table, you still =
want
that record to display? Maybe you can do an outer join on from PR to =
PRLN,
then do another report with PRLN with no PR links? Just throwing out =
random
ideas.

*************************=20
Eric Landes=20
Senior Programmer/Analyst member of the ASPFriends.com Moderator team=20
For Articles on Crystal.Net, Datagrids and more check out
http://www.aspalliance.com/corporatecoder/
*************************=20
All statements expressed are my own, and do not reflect the opinion of =
my
employer, unless stated otherwise.=20

-----Original Message-----
From: Winnie Khoo [mailto:Click here to reveal e-mail address]
Sent: Friday, July 12, 2002 4:06 AM
To: ngfx-crystal
Subject: [ngfx-crystal] joining tables in visual linking expert.

Hi There,

I have PurchaseRequest Table (PR) PurchaseRequestLine Table (PRLN). PR
and PRLn are linked to each other by the PRID from PR table.

Scenario 1: I can have a PR without a PRLN existing. I would like to
display all PR and PRLN records regardless of whether PR.PRID =3D
PRLN.PRID.=20

**Note: I tried using Left outer join for this. All records will be
displayed but there will be duplicates. =20

1st Occurance :-=20
PR Table (Record 1)
PRID: PRQ 1000003=20
Date : 6/29/2002 12:26:46PM=20
Status : Cancelled=20

PRLn Table (Record 1)
PRID : PRQ 1000003
ItemCode : Item 6=20
Cost : 432.34000=20
Status : Active=20

PR Table(Record 2)
PRID : PRQ 1000003
Date : 6/29/2002 12:26:46PM=20
Status : Cancelled=20

PRLn Table(Record 2)
PRID : PRQ 1000003
ItemCode: Leong 2 cx=20
Cost : 34.00000=20
Status : Active=20
=20
2nd Occurance (Which Should not occur)
PR Table(Record 1)
PRID : PRQ 1000003
Date : 6/29/2002 12:26:46PM=20
Status : Cancelled=20
=20
PRLn Table(Record 1)
PRID : PRQ 1000003
ItemCode : Item 6=20
Cost : 432.34000=20
Status : Active=20
=20
PR Table(Record 2)
PRID :PRQ 1000003
Date : 6/29/2002 12:26:46PM=20
Status : Cancelled=20

PRLn Table(Record 2)
PRID : PRQ 1000003
ItemCode : Leong 2 cx=20
Cost : 34.00000=20
Status : Active=20

=20
Scenario 2: I will also have a criteria to display PR & PRLn records
where PR.Status =3D Delete.=20

**Note : I tried using the left outer join (in visual linking expert)
for this. It will work here but it will not work when scenario 1 comes
in.

I hope someone can find a solution to this! Thanks

Cheers
Winnie

| [ngfx-crystal] member Click here to reveal e-mail address =3D YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-crystal.asp =3D JOIN/QUIT

Confidentiality Notice: This e-mail message, including any
attachments, is for the sole use of the intended recipient(s) and may
contain confidential and privileged information. Any unauthorized
review, use, disclosure or distribution is prohibited. If you are not
the intended recipient, please contact the sender by reply e-mail and
destroy all copies of the original message.

PARTNERS Health Plan Phone: 574-233-4899
100 E. Wayne St., Suite 502 Fax: 574-234-7484
South Bend, IN 46601 www.partnersindiana.com

Reply to this message...

Winnie Khoo

Hi Eric,

Thanks for taking the time to reply my mail. Anyway, I have found an
alternative to what I want to do

Thanks again!

Cheers
Winnie

-----Original Message-----
From: Eric Landes [mailto:Click here to reveal e-mail address]
Sent: 17 July, 2002 8:42 PM
To: ngfx-crystal
Subject: [ngfx-crystal] RE: joining tables in visual linking expert.

Winnie,
Sorry for the long response. If you have already resolved please
ignore.

Since it is early in the morning here, and I'm a bit groggy, maybe I'm
just
being a idiot, but why do you have two tables on the same report that
you
are not linking? I'm trying to make sense of that, but like I said, I'm
a
bit groggy this morning.

Let me phrase what I think you are wanting to do, you want all linked
records to display, and if there is no link on either table, you still
want
that record to display? Maybe you can do an outer join on from PR to
PRLN,
then do another report with PRLN with no PR links? Just throwing out
random
ideas.

*************************
Eric Landes
Senior Programmer/Analyst member of the ASPFriends.com Moderator team
For Articles on Crystal.Net, Datagrids and more check out
http://www.aspalliance.com/corporatecoder/
*************************
All statements expressed are my own, and do not reflect the opinion of
my
employer, unless stated otherwise.

-----Original Message-----
From: Winnie Khoo [mailto:Click here to reveal e-mail address]
Sent: Friday, July 12, 2002 4:06 AM
To: ngfx-crystal
Subject: [ngfx-crystal] joining tables in visual linking expert.

Hi There,

I have PurchaseRequest Table (PR) PurchaseRequestLine Table (PRLN). PR
and PRLn are linked to each other by the PRID from PR table.

Scenario 1: I can have a PR without a PRLN existing. I would like to
display all PR and PRLN records regardless of whether PR.PRID =
PRLN.PRID.

**Note: I tried using Left outer join for this. All records will be
displayed but there will be duplicates.

1st Occurance :-
PR Table (Record 1)
PRID: PRQ 1000003
Date : 6/29/2002 12:26:46PM
Status : Cancelled

PRLn Table (Record 1)
PRID : PRQ 1000003
ItemCode : Item 6
Cost : 432.34000
Status : Active

PR Table(Record 2)
PRID : PRQ 1000003
Date : 6/29/2002 12:26:46PM
Status : Cancelled

PRLn Table(Record 2)
PRID : PRQ 1000003
ItemCode: Leong 2 cx
Cost : 34.00000
Status : Active

2nd Occurance (Which Should not occur)
PR Table(Record 1)
PRID : PRQ 1000003
Date : 6/29/2002 12:26:46PM
Status : Cancelled

PRLn Table(Record 1)
PRID : PRQ 1000003
ItemCode : Item 6
Cost : 432.34000
Status : Active

PR Table(Record 2)
PRID :PRQ 1000003
Date : 6/29/2002 12:26:46PM
Status : Cancelled

PRLn Table(Record 2)
PRID : PRQ 1000003
ItemCode : Leong 2 cx
Cost : 34.00000
Status : Active

Scenario 2: I will also have a criteria to display PR & PRLn records
where PR.Status = Delete.

**Note : I tried using the left outer join (in visual linking expert)
for this. It will work here but it will not work when scenario 1 comes
in.

I hope someone can find a solution to this! Thanks

Cheers
Winnie

| [ngfx-crystal] member Click here to reveal e-mail address = YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-crystal.asp = JOIN/QUIT

Confidentiality Notice: This e-mail message, including any
attachments, is for the sole use of the intended recipient(s) and may
contain confidential and privileged information. Any unauthorized
review, use, disclosure or distribution is prohibited. If you are not
the intended recipient, please contact the sender by reply e-mail and
destroy all copies of the original message.

PARTNERS Health Plan Phone: 574-233-4899
100 E. Wayne St., Suite 502 Fax: 574-234-7484
South Bend, IN 46601 www.partnersindiana.com

| [ngfx-crystal] member Click here to reveal e-mail address = YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-crystal.asp = JOIN/QUIT

Reply to this message...

ExamGuru IT Solutions - .Net Guru is owned and operated by ExamGuru, Inc., the man behind .Net Guru. If you're in the market for bespoke software or software consultancy, why not get him and his highly trained team to help? - www.examguru.net/ITCertification

Need Dot Net Interview Questions?
Ask ExamGuru, Inc. for advice and help on Passing .Net Interviews

.Net Projects
Best-of-breed application framework for .NET projects, developed by ExamGuru, Inc. and ExamGuru IT

Free .net Help
Commission ExamGuru, Inc. and his team for your next bespoke software project

FogBUGZ
The only bug tracking system carefully crafted with one goal in mind: helping teams create great software.

Awesome Tools
If you don't know about these, you're missing out... IT Certification Questions
IT Interview Questions
Free Oracle 10g Training
MCSE Boortcamp
Cisco Study Guides
Cheap Study Guides
Exact Questions
Dot Net Interview Questions
Oracle OCP
Cheap Travel
Designer Perfumes - Wholesale Prices
Free Programming Tutorials

	Copyright © ExamGuru, Inc. 2001-2006
	Contact Us - Terms of Use - Privacy Policy - www.dot-net-guru.com - www.examguru.net - www.oraclesource.net - www.itinterviews.net - www.examguru.net/ITCertification