Error doing a simple join? HELP PLEASE!

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

Transact-SQL (2005)

Error doing a simple join? HELP PLEASE!

Author

Topic

besadmin
Posting Yak Master

116 Posts

Posted - 2010-08-19 : 11:11:07

hey friends. i am not sure why i am getting 'incorrect syntax near keyword join on the second join'? any help is greatly appreciated as always! thanks friends!


SELECT DISTINCT
	  a.shipment_number AS FCOrderID
	, x.source_order_no
	, a.carrier_name AS Carrier
	, a.ship_date AS Ship_Date
	, a.tracking_number AS TrackingNo
	, b.load_nbr AS LoadNo
	, b.pro_nbr AS ProTrackingNo
FROM server.database.dbo.besTABLE a
	join database2..table 2 b ON a.shipment_number = b.bol_id COLLATE Latin1_General_CI_AS 
WHERE b.load_nbr = (SELECT c.Load_Nbr FROM database2..table 2 c WHERE bol_id = '005444106')
	join server.database3.dbo.tablex x ON a.shipment_number = x.pick_list_no
WHERE x.pick_list_no IN 
	(SELECT c.bol_id 
	  FROM database2..table 2 c 
	  WHERE c.load_nbr = (SELECT c.Load_Nbr FROM database2..table 2 c WHERE bol_id = '005444106'))

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-19 : 11:18:06

Change first WHERE to AND

Madhivanan

Failing to plan is Planning to fail

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-19 : 11:21:34

quote:

Originally posted by besadmin

hey friends. i am not sure why i am getting 'incorrect syntax near keyword join on the second join'? any help is greatly appreciated as always! thanks friends!


SELECT DISTINCT
	  a.shipment_number AS FCOrderID
	, x.source_order_no
	, a.carrier_name AS Carrier
	, a.ship_date AS Ship_Date
	, a.tracking_number AS TrackingNo
	, b.load_nbr AS LoadNo
	, b.pro_nbr AS ProTrackingNo
FROM server.database.dbo.besTABLE a
	join database2..table 2 b ON a.shipment_number = b.bol_id COLLATE Latin1_General_CI_AS 
WHERE b.load_nbr = (SELECT c.Load_Nbr FROM database2..table 2 c WHERE bol_id = '005444106')
	join server.database3.dbo.tablex x ON a.shipment_number = x.pick_list_no
WHERE x.pick_list_no IN 
	(SELECT c.bol_id 
	  FROM database2..table 2 c 
	  WHERE c.load_nbr = (SELECT c.Load_Nbr FROM database2..table 2 c WHERE bol_id = '005444106'))

Can you try this...

SELECT DISTINCT
	  a.shipment_number AS FCOrderID
	, x.source_order_no
	, a.carrier_name AS Carrier
	, a.ship_date AS Ship_Date
	, a.tracking_number AS TrackingNo
	, b.load_nbr AS LoadNo
	, b.pro_nbr AS ProTrackingNo
FROM server.database.dbo.besTABLE a
	join database2..table 2 b ON a.shipment_number = b.bol_id COLLATE Latin1_General_CI_AS 
	join database2..table 2 c ON b.load_nbr = c.Load_Nbr
	join server.database3.dbo.tablex x ON a.shipment_number = x.pick_list_no
WHERE c.bol_id = '005444106'

besadmin
Posting Yak Master

116 Posts

Posted - 2010-08-19 : 11:22:12

man i think you nailed it! thanks so much!

when i do that now i get the following error, do you have any ideas on this?

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

Thanks again so much!

Kristen
Test

22859 Posts

Posted - 2010-08-19 : 11:25:59

You have a JOIN in your WHERE clause


WHERE b.load_nbr = (SELECT c.Load_Nbr FROM database2..table 2 c WHERE bol_id = '005444106')
	join server.database3.dbo.tablex x

Kristen
Test

22859 Posts

Posted - 2010-08-19 : 11:28:59

"Error converting data type varchar to numeric."

You are comparing a VARCHAR column and a NUMERIC column somewhere. The VARCHAR column will be implicitly converted to numeric to make the comparison but on one, or more, row(s) the VARCHAR is not valid for numeric implicit conversion

besadmin
Posting Yak Master

116 Posts

Posted - 2010-08-19 : 11:32:23

hey everyone! thanks SO MUCH for all the quick replys to all.

the one that i was able to use and seems to work the easiest was from vijayisonly

looks like it worked perfectly for me, thanks a ton!

thanks again to all. SQLTeam is the BEST!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources