Comment On You don't really need Inner Joins

Why is rs1 initialized on one line but rs2 and rs3 (which are effectively identical) split over 2?

How bizarre.

Atleast he uses "top 1" instead of requesting the whole set and only reading the first record. :)

Talk about a WTF!

Gmail invite recipient Ravind Budhiraja

oh boy...

this is the kind of thing I'd expect to see where a subselect would be used, rather than an inner join, in an access-backed solution.

That's amazing.

This may just go to show my ignorance with SQL, but how would it be possible to use inner joins to get the first row of each of the 3 sub queries?

BTW, I am not saying this is the way to do it either, I am just wondering how a single query using inner joins could be done.

SELECT * FROM Returns R
INNER JOIN contact C on R.ContactID = C.ContactID
INNER JOIN Products P ON R.ProductID = P.ProductID
INNER JOIN FaultCodes F ON R.faultCode = F.faultcode

And of course you could use a LEFT OUTER join if any of your joined tables could ge null. From the looks of it ProductID, ContactID, and FaultCode are primary keys of the repsective tables.

I worked with a guy that was convinced that this was faster then joins because he didn't put indexes on the 500 row and 20 million row tables he was joining.

Alex,

Can you show how your query gets the first row from each of the tables. Can you say "top 1" inline in the sql to do that?

Admitedly I am assuming that there could be more than 1 row for them, but, lets give the benefit of the doubt in this case ;-)

I think the issue here is why is this guy selecting top1 product, contact, and faultcode joined to these returns? Sounds like either bad database design/data integrity(he expects there to be duplicates, so he compensates by making sure he gets just one) or he isn't sure how to do what he really wants to do (there is only one set of records where all three FKs joined together will match up, but since he is doing it piecemeal he is getting multiple results).

What bugs me most, is walking into this sort of code. I always fall for the killer assumption: This guy knew what he was doing.

So then I start looking for the WHY behind this bizare bit of code. "There must be a reason why he did this like this."

A day or so later, when I've trawled the database to find that he was completely clueless, I throw up my arms in dispair and fight the urge to select the top-most folder and hit Shift+Delete.

One shudders to think what he might have done under SQL 6.5 when there was no "Top"

I feel dirty. I need to go and take an shower now...

Stop! You're ruining the joke...

As Rory often tells Carl, "Don't try to make sense or the... weirdos!" You guys are trying to analyze this too closely.

Why would s/he do this?!? Probably because s/he didn't know any better...

Should have run spellcheck. (Authorisation)

They don't need to run a spell check (or a "spell checker" for that matter) because the spelling of "Authorisation" is correct for many English language locales.

That's astounding! Just beautiful. And I've seen that data before in real systems. ewww.

I once managed a guy, heard through the grapevine he though I couldn't really code - just a bit of VB Script or whatever. Fine. Laughed if off, you can't make everyone happy.

Later we both got laid off. I got brought back in for a few hours to support some a "secret" project the boss had him work on. The code looked exactly like that stuff above. Even a little worse - it was mapping SQL Server int's (32 bits) to ASP int's (16 bits). Worked for a little while...

funny. you guys kill this guy for bad code, fine, yet when someone makes the awful mistake of thinking authorisation was spelt incorretly, just because they spell it another way in their country... well... that's JUST as bad, if not more so if you ask me. Coding is a skill - lots of people are bad at at, they just need to learn. Spelling... well its a basic axiom of human consciousness noless - it makes us civilised. Get your acts together...

(and yes, extra kudos goes to all the amazing guys and gals that pick up the HUGE amout of extra spelling and grammatically mistakes in this comment :|)

colleague who apparently doesn't not believe in using an INNER JOIN:


Does this mean that the colleague does believe in using inner joins?? LOL

Looks good to me. I don't see a problem with it.

note that he has SELECT TOP 1 but no ORDER BY clause -- which is of course meaningless. So the idea that he might be only getting the "first" row from each related table using this method (which is not really easy/possible with a join) is not applicable.

If you don't see any problems with this code, you really shouldn't be going anywhere near a RDMS.

If there's a possibility of there being more than one row in each joined table, you could always do this:

SELECT * FROM Returns R
INNER JOIN contact C on C.ContactID = (select top 1 C2.ContactId from contact C2 where R.ContactId = C2.ContactID)
INNER JOIN Products P ON P.ProductID = (select top 1 P2.ProductID from Products P2 where R.ProductId = P2.ProductId)
INNER JOIN FaultCodes F ON F.faultCode = (select top 1 F2.faultcode from FaultCodes F2 where R.faultCode = F2.FaultCode)

I reaally don't recommend doing this, simply from a performance standpoint. However, if you really don't know what the unique id is on the other three tables, this is about the best way to go.

Al:

If there's a possibility of there being more than one row in each joined table, you could always do this:

SELECT * FROM Returns R
INNER JOIN contact C on C.ContactID = (select top 1 C2.ContactId from contact C2 where R.ContactId = C2.ContactID)
INNER JOIN Products P ON P.ProductID = (select top 1 P2.ProductID from Products P2 where R.ProductId = P2.ProductId)
INNER JOIN FaultCodes F ON F.faultCode = (select top 1 F2.faultcode from FaultCodes F2 where R.faultCode = F2.FaultCode)

I reaally don't recommend doing this, simply from a performance standpoint. However, if you really don't know what the unique id is on the other three tables, this is about the best way to go.

Actually if there were not foreign key constraints in the returns table then the query could use sub queries would look like:

SELECT r.*,
(select top 1 C.Nam from contact C where R.ContactId = C.ContactID) ContactName,
(select top 1 P.ProductName from Products P where R.ProductId = P.ProductId) ProductName,
(select top 1 P. FaultName from FaultCodes P where R. faultCode = P.faultCode) FaultName
FROM Returns R;

find for all kinds of watches and handbags

http://replica038.com