CODEWITHSUNDEEP
mysqlsqldatabase
Finn Eggers
Finn Eggers

Reputation: 945

SQL table aliases

I got a table called "email" with 2 columns, "von" and "zu". Both contain varchar(20). I need to get a table containing every String that matches to a specific name. For example: email:

von    |     zu
---------------
Finn   |    John
Finn   |    Lisa
Eric   |    Finn
Eric   |    Lisa

Now I need to get every matching name to "Finn". The result should look like this:

  X
-------
John
Lisa
Eric

My code looks like this:

Select X from 
(Select von as X from email where von = 'Finn'),
(Select zu as X from email where zu = 'Finn');

And I get the following error:

#1248 - Every derived table must have its own alias

Can anyone help me and tell me what I did wrong? Greetings, Finn

Upvotes: 0

Views: 71

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

This is not an answer, but a clarification what your own query does.

Select X from 
(Select von as X from email where von = 'Finn') von_finn,
(Select zu as X from email where zu = 'Finn') zu_finn;

(I've added the missing table aliases.)

You are using an old join sytax here that was used in the 1980s. Nowadays we specify what join type we are using. In your case where there is no join criteria on the tables, that would be a cross join:

Select X
from (Select von as X from email where von = 'Finn') von_finn
cross join (Select zu as X from email where zu = 'Finn') zu_finn;

Let's look at the first subquery. You select von where von is 'Finn'. This is certainly a typo and you really wanted to select zu. Correct? This would give you the people who received an email from Finn. Same for the second subquery, where you'd get all people who sent an email to Finn. Cross joining means: take all senders and combine them with all receivers (i.e. get all combinations). With your sample data you'd get:

zu_finn.x | von_finn.x
----------+-----------
Eric      | John
Eric      | Lisa

Then you show x. But which one? zu_finn.x or von_finn.x? Either the DBMS will throw an ambiguity error (which would be preferable) or it would silently decide for one column, so you'd either show Eric, Eric or John, Lisa.

Many mistakes for a small query :-) I thought I'd tell you, so you can learn from it.

Upvotes: 0

SS_DBA
SS_DBA

Reputation: 2423

Use Union. This should work for what you want.

Select von as X from email where von = 'Finn'
UNION ALL
Select zu as X from email where zu = 'Finn';

Upvotes: 1

Related Questions