Is this SQL query correct ?

Question

Is this SQL query correct ?

spectra

SSCarpal Tunnel

Points: 4120
More actions
October 15, 2018 at 6:00 am

#366994

Is this SQL query correct ?
Could you please reply to this ?
https://www.sqlservercentral.com/Forums/2003131/Is-this-query-correct

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 1

What other help with that query do you actually need? Folks there have answered correctly. The LIVE table is used twice... once for employees as an alias of "e" and once for managers (who are also employees) with an alias of "m". The code in the WHERE clause is written so horribly and out of order that it's difficult to see what the actual join is. Folks are also correct about the manger table not existing because the SOTs that wrote the book and the question didn't actually test the code they wrote.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

adish SSChasing Mays Points: 634 More actions · Answer 2

I appreciate the fact that the OP was very honest about his question. He did not try to disguise it. I believe he needs a bit of hand holding. The discussion on the other forum has gone on to a different level. I will try to give a step by step explanation.
The requirement is to list all the people who live in the same city and on the same street as their manager.
We will need data for people (employee) and their address which you can get from the Live table.
We will need data for managers and their address. We will get the list of managers from the managers table and their address from the Live table.
Since we need the Live table twice, we will need to provide aliases.
We get employee name and address from Live AS e (written as Live e in your book)
We get manager address from Live AS m (written as Live m in your book)
We get manager list from managers table
Therefore we need Live e, Live m and managers table in our FROM query.
Next, we need to join e to managers to get the employees' manager (done as e.person_name = managers.person_name). This gives us the employee name, address and his manager.
Now, we need the managers address. We get this by joining managers to m (done as m.person_name = managers.manager_name). This gives us the managers name and address.
Now we need to match the address. (done by e.street = m.street and e.city = m.city)
This is exactly what your book is doing.

What the experts are trying to tell you is that
1. Your book writes the FROM clause as - Live e, Live m, managers - which is a very old fashioned form. Now a days tables are joined using explicit JOINs
2. Your book writes manager in the FROM clause - which is not the same as managers - and therefore the query will give an error when run
3. Your book is not nicely formatted and it makes code difficult to read. eg 'e.person_name = managers.person.name' should be written in one line.

Hope this helps.