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
   

- Advertisement -