forcing only one record using LEFT JOIN

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 2000 Forums

Transact-SQL (2000)

forcing only one record using LEFT JOIN

Author

Topic

flchico
Starting Member

46 Posts

Posted - 2007-06-14 : 16:40:26

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)

17689 Posts

Posted - 2007-06-14 : 20:08:07

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.

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-15 : 00:17:41

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

flchico
Starting Member

46 Posts

Posted - 2007-06-15 : 11:31:09

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?

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-06-17 : 21:31:33

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).

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2007-06-25 : 15:40:42

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.

BoaterDan
Starting Member

1 Post

Posted - 2008-07-16 : 16:15:05

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).

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-07-16 : 16:34:51

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.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-07-16 : 16:53:26

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

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2008-07-17 : 02:02:28

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources