Hi,

I have a table in my database with the structure:


+---+-------------+----------
| id | surname | reviewer|
+---+-------------+----------
| 1 | smith | 2 |
------------------------------
| 2 | jones | 4 |

What I want is to output the surname of both the reviewer and the reviewee so I would have:

+---+-------------+----------
| id | reviewee | reviewer|
+---+-------------+----------
| 1 | smith | jones |
------------------------------
| 2 | jones | Riley |




With the reviewer 'referencing' id and them both being in the same table I can't figure out how to do it.

You didn't give the name of the table so I called it MyTable.
It''s a good idea to have both firstname and surname in the table.
It would be better if you called the reviewer field reviewer_id.

Mike

That's excellent.

Cheers

Can we use JOIN syntax please?
Also, a LEFT JOIN will allow you to show those without reviewers.

You're welcome to use whatever syntax you feel like George. I shall do similar

you're welcome to use whatever column names you feel like, mike

the rest of the world shall do similar

Once uraknai said thanks I thought we'd finished but ...

The new join syntax works great and has it's advantages but I just happen to find the older syntax simpler to write and understand - for me and for others. Thanks for the info on left joins but you might be surprised to learn that null joins are easily possible with the old syntax. Luckily they aren't required by uraknai's original query.

The column names were supplied by uraknai and so I left them out so he could have a little something to do.

I only commented on the field names in the table cause it appears to me to be better practice to call the reviewer field, which is an id, reviewer_id. I assume that the latest fad book that you guys are reading/writing has some special naming convention ti_reviewer_fk_MyTab perhaps. What naming convention would you suggest?

Mike

naming conventions are just that...conventions
it doesn't really matter whether you use columnname_id or columnameid or ColumnNamID.

I uised to use _ as a separator for things like ID, these days I tend to find ColumnNameID is the more common convention, so I tend to use that.

providing its consistent and applicable to all code developed in that shop. there's nowt worse in my books in finding multiple conventions in one site, and undoubtedly its easier if everyone used the same convention

AFIAK the old style syntax is deprecated and join is the preferred syntax.
personally I find it easier with newbies to get them to think in terms of blocks within SQL .. the JOIN being the 'proper' place to define relationships, the WHERE the filter, and so on. but Im not prescriptive...

AS the JOIN syntax is the current preferred syntax Id rather start people using JOIN rather than WHERE..... I think its fine for people who are familiar with WHERE or for legacy code, it may not be a smart choice for current or proposed applications. Whether the WHERE join syntax will ever be phased out is a moot point, but in my books its no reason to start people off using it.

The JOIN syntax makes more logical sense to me, it also is more capable, and perhaps more importantly more transparent than burying the joins in a WHERE clause.. that transparency has more to do with the legibility rather than the familiarity of "that's the way I've always done it".

Im currently paying some of the pain of moving into a .NET environment, and hating it, because I can't use the old styles that I'm familiar with. Id like to continue to using the familiarity of VB but instead Ive got to relearn .NET.. its a pain but it has to be done