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 2005 Forums
 .NET Inside SQL Server (2005)
 RIGH JOIN 3 TABLES

Author  Topic 

ilimax
Posting Yak Master

164 Posts
Posted - 2007-02-22 : 08:42:45
I do not know where to post this question ... it is about VB 6 and Crystal Report. Can anybody help me.
I have this query with 3 tables and does not pull correct data ...does anybody have idea why?

SELECT TurkeyOrders.[OrderID],
TurkeyOrders.[PickedUp],
NS_Customers.[First Name],
NS_Customers.[Last Name],
NS_Customers.[Phone],
TurkeyOrders.[Updated],
TurkeyOrders.[Weight],
TurkeyOrderType.[Order],
TurkeyOrders.[Quantity],
TurkeyOrders.[PickupDate],
TurkeyOrders.[OrderLocation]
FROM NS_Customers
RIGHT JOIN (TurkeyOrderType RIGHT JOIN TurkeyOrders
ON TurkeyOrderType.ID = TurkeyOrders.OrderType)
ON NS_Customers.[Customer Number] = TurkeyOrders.[CustomerID]
WHERE TurkeyOrders.[OrderLocation] = '" & StoreKriterija & "'"

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2007-02-22 : 08:47:19
You have to explain to us what are you trying to do with the query, provide some sample data and the expected result

KH
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-02-22 : 08:49:52
[code]SELECT TurkeyOrders.[OrderID],
TurkeyOrders.[PickedUp],
NS_Customers.[First Name],
NS_Customers.[Last Name],
NS_Customers.[Phone],
TurkeyOrders.[Updated],
TurkeyOrders.[Weight],
TurkeyOrderType.[Order],
TurkeyOrders.[Quantity],
TurkeyOrders.[PickupDate],
TurkeyOrders.[OrderLocation]
FROM TurkeyOrders
LEFT JOIN NS_Customers ON NS_Customers.[Customer Number] = TurkeyOrders.[CustomerID]
LEFT JOIN TurkeyOrderType ON TurkeyOrderType.ID = TurkeyOrders.OrderType
WHERE TurkeyOrders.[OrderLocation] = '" & StoreKriterija & "'"[/code]

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

ilimax
Posting Yak Master

164 Posts
Posted - 2007-02-22 : 09:14:04
quote:
Originally posted by khtan

You have to explain to us what are you trying to do with the query, provide some sample data and the expected result

KH




I am trying to pull orders;
Table 1: TurkeyOrders
OrderID: 1
Updated: 2/2/2007
Weight: 12-18 LB
Quantity:1
PickupDate: 2/25/2007
OrderLocation: B
PickedUp: No

NS_Customers:
CustomerID: 1
First Name: John
Last Name: Stone

TurkeyOderType
TypeID: 1
Order: BREAST
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-02-22 : 09:22:23
You should really try to replace right join and left join with a

INNER JOIN

because if there is no match with auxiliary tables, the order is having a referential integrity error!


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

ilimax
Posting Yak Master

164 Posts
Posted - 2007-02-22 : 09:25:01
quote:
Originally posted by Peso

SELECT		TurkeyOrders.[OrderID],
		TurkeyOrders.[PickedUp],
		NS_Customers.[First Name],
		NS_Customers.[Last Name],
		NS_Customers.[Phone],
		TurkeyOrders.[Updated],
		TurkeyOrders.[Weight],
		TurkeyOrderType.[Order],
		TurkeyOrders.[Quantity],
		TurkeyOrders.[PickupDate],
		TurkeyOrders.[OrderLocation]
FROM		TurkeyOrders
LEFT JOIN	NS_Customers ON NS_Customers.[Customer Number] = TurkeyOrders.[CustomerID]
LEFT JOIN	TurkeyOrderType ON TurkeyOrderType.ID = TurkeyOrders.OrderType
WHERE		TurkeyOrders.[OrderLocation] = '" & StoreKriterija & "'"


Peter Larsson
Helsingborg, Sweden


I already tried with this option with LEFT JOIN, but I still have wrong data in Crystal Report...Report show 400 records instead of 2 records ...
Go to Top of Page

ilimax
Posting Yak Master

164 Posts
Posted - 2007-02-22 : 09:27:09
quote:
Originally posted by Peso

You should really try to replace right join and left join with a

INNER JOIN

because if there is no match with auxiliary tables, the order is having a referential integrity error!


Peter Larsson
Helsingborg, Sweden

I even tried with INNER but it is same ...
I have to use Righ or Left Join ...Sometimes there is no data in joined fields ...
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-02-22 : 09:37:58
Have you tried DISTINCT?
SELECT DISTINCT	TurkeyOrders.[OrderID],
		TurkeyOrders.[PickedUp],
		NS_Customers.[First Name],
		NS_Customers.[Last Name],
		NS_Customers.[Phone],
		TurkeyOrders.[Updated],
		TurkeyOrders.[Weight],
		TurkeyOrderType.[Order],
		TurkeyOrders.[Quantity],
		TurkeyOrders.[PickupDate],
		TurkeyOrders.[OrderLocation]
FROM		TurkeyOrders
INNER JOIN	NS_Customers ON NS_Customers.[Customer Number] = TurkeyOrders.CustomerID
INNER JOIN	TurkeyOrderType ON TurkeyOrderType.ID = TurkeyOrders.OrderType
WHERE		TurkeyOrders.[OrderLocation] = '" & StoreKriterija & "'"


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

mahesh_bote
Constraint Violating Yak Guru

298 Posts
Posted - 2007-02-26 : 05:03:08
quote:
Originally posted by ilimax

quote:
Originally posted by Peso

SELECT		TurkeyOrders.[OrderID],
		TurkeyOrders.[PickedUp],
		NS_Customers.[First Name],
		NS_Customers.[Last Name],
		NS_Customers.[Phone],
		TurkeyOrders.[Updated],
		TurkeyOrders.[Weight],
		TurkeyOrderType.[Order],
		TurkeyOrders.[Quantity],
		TurkeyOrders.[PickupDate],
		TurkeyOrders.[OrderLocation]
FROM		TurkeyOrders
LEFT JOIN	NS_Customers ON NS_Customers.[Customer Number] = TurkeyOrders.[CustomerID]
LEFT JOIN	TurkeyOrderType ON TurkeyOrderType.ID = TurkeyOrders.OrderType
WHERE		TurkeyOrders.[OrderLocation] = '" & StoreKriterija & "'"


Peter Larsson
Helsingborg, Sweden


I already tried with this option with LEFT JOIN, but I still have wrong data in Crystal Report...Report show 400 records instead of 2 records ...



make group in Crystal Reports, so that u will get the unique data. so change the query, use group in it.

let us know.

Mahesh
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