SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 forcing only one record using LEFT JOIN
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

flchico
Starting Member

46 Posts

Posted - 06/14/2007 :  16:40:26  Show Profile  Reply with Quote
Hi,

I have a query that includes a 'LEFT JOIN' and sometimes this join returns more than one record. Is there a way to force the join to return at most one record? Something like a TOP 1 but in the LEFT JOIN?

khtan
In (Som, Ni, Yak)

Singapore
17600 Posts

Posted - 06/14/2007 :  20:08:07  Show Profile  Reply with Quote
when in JOIN, if the JOIN condition does not fulfilled, there will not be any record return. Even with LEFT JOIN.

Maybe you can explain more in detail your situation here.


KH

Go to Top of Page

pbguy
Constraint Violating Yak Guru

India
319 Posts

Posted - 06/15/2007 :  00:17:41  Show Profile  Reply with Quote
In the left join, if the right side table has any duplicate records like one to many relation will return more than one record.

To reduce the no. of records add more condition if possible.

--------------------------------------------------
S.Ahamed
Go to Top of Page

flchico
Starting Member

46 Posts

Posted - 06/15/2007 :  11:31:09  Show Profile  Reply with Quote
I tried this.

This is my original Join:

LEFT JOIN ACC_Item_Codes zz 	
	ON   zz.cGroup_id = e.cGroup_id AND 	
		RIGHT('0000000' + CAST(a.iUPC_Mfg AS VARCHAR(7)), 7) +
		RIGHT('0000000' + CAST(a.iUPC_Item AS VARCHAR(7)), 7) +
		RIGHT('0000000' + CAST(a.iUPC_Case AS VARCHAR(7)), 7) = zz.cItemCodeKey_id_MIC 


This is the new Join trying to limit the join result to at most 1:

LEFT JOIN (SELECT TOP 1 cItemCode,cItemCodeKey_id_MIC,cGroup_id 
			FROM ACC_Item_Codes) AS zz
	ON cGroup_id = e.cGroup_id AND
		RIGHT('0000000' + CAST(a.iUPC_Mfg AS VARCHAR(7)), 7) + 
		RIGHT('0000000' + CAST(a.iUPC_Item AS VARCHAR(7)), 7) + 
		RIGHT('0000000' + CAST(a.iUPC_Case AS VARCHAR(7)), 7) = zz.cItemCodeKey_id_MIC


But they're not equivalent, they don't yield the same results. I'm I close to making the second query close to the first one or it can't be done?
Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 06/17/2007 :  21:31:33  Show Profile  Reply with Quote
Of course they won't give the same results. One gives 0 or 1 rows and the other gives them all.
This sounds like a very dodgy requirement to me. Can you explain what you are trying to do & why?

Anyway - your query doesn't work because you are getting only one row from ACC_Item_Codes. You need to push your join columns into your sub query, do a group by in there so you get one possible value for the join, then join across. If you are talking about 'give me all columns for any row that matches' then it gets more complicated (and less likely to be a real requirement).
Go to Top of Page

PSamsig
Constraint Violating Yak Guru

Denmark
384 Posts

Posted - 06/25/2007 :  15:40:42  Show Profile  Send PSamsig a Yahoo! Message  Reply with Quote
You need to tell us what you need from ACC_Item_Codes, if it is only one coloumn, then it is easy, if it is more, then it become a bit more tricky.


-- If you give someone a program, you will frustrate them for a day; if you teach them how to program, you will frustrate them for a lifetime.
Go to Top of Page

BoaterDan
Starting Member

1 Posts

Posted - 07/16/2008 :  16:15:05  Show Profile  Reply with Quote
Real helpful responses guys.

The problem is you turned the SELECT TOP 1 part into a subquery, which means it runs logically isolated from the rest of the query. The join and its criteria are applied to whatever the subquery happens to return. In this case it returns somewhat random data since the subquery has no WHERE clause.

Move the ON parameters into the subquery as a WHERE clause, and then leave the join a simple primary-foreign key join (which is somewhat reduntant since you've already filtered matches only in the subquery).
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 07/16/2008 :  16:34:51  Show Profile  Reply with Quote
Maybe something like this?
LEFT JOIN 
(
	SELECT 
		MAX(cItemCode),
		cItemCodeKey_id_MIC,
		cGroup_id 
	FROM 
		ACC_Item_Codes
	GROUP BY
		cItemCodeKey_id_MIC,
		cGroup_id 
) AS zz
	ON cGroup_id = e.cGroup_id AND
		RIGHT('0000000' + CAST(a.iUPC_Mfg AS VARCHAR(7)), 7) + 
		RIGHT('0000000' + CAST(a.iUPC_Item AS VARCHAR(7)), 7) + 
		RIGHT('0000000' + CAST(a.iUPC_Case AS VARCHAR(7)), 7) = zz.cItemCodeKey_id_MIC
YOu need to determine which cItemCode you want, assuming that the combination of cItemCodeKey_id_MIC and cGroup_id can return more than one cItemCode. I just used MAX, but maybe there is some order that is important, perhaps a DATETIME or someting.
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 07/16/2008 :  16:53:26  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
flchico -- we can all help you much better if you give a simple, clear example that demonstrates exactly what you are after. Right now, it is too vague. The basic idea Lamprey showed is what you need -- you'll need to use GROUP BY or DISTINCT or something similar to consolidate the multiple values that you are joining to into a single value.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

PSamsig
Constraint Violating Yak Guru

Denmark
384 Posts

Posted - 07/17/2008 :  02:02:28  Show Profile  Send PSamsig a Yahoo! Message  Reply with Quote
This a year old thread, my guess is that by now flchico has either learned SQL or got fired.

-- If you give someone a program, you will frustrate them for a day; if you teach them how to program, you will frustrate them for a lifetime.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000