0

How can I start the following query?

I have three tables hasfriend, person and message.

I want to run the following SQL query, output all persons once who have sent a message and are friends with a certain person.

The tables are constructed as follows:

  • hasfriend consists of: email, emailfriend
  • message consists of: id, fromemail, toemail
  • person consists of: email, firstname, lastname

So my query now looks like this:

SELECT DISTINCT person.firstname, person.lastname
FROM hasfriend, person, message
WHERE hasfriend.emailfriend = 'max.muster@mail.de' 
  AND (message.fromemail = person.email)

But it's not a good solution because the hasfriend.email is fixed. How can I solve this better?

Luuk
  • 12,245
  • 5
  • 22
  • 33
Max_T
  • 1
  • 1
  • 4
    [Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**30 years!!** ago) and its use is discouraged – marc_s Apr 03 '22 at 09:43
  • Please specify where the table `friend` is coming from ("I have three tables `hasfriend`, `person` and `message`") – Luuk Apr 03 '22 at 09:53
  • Can you please show me how to do this with join in this example cause i have only learned it with comma... – Max_T Apr 03 '22 at 09:53
  • @Luuk typing mistake cause this database was in another language... – Max_T Apr 03 '22 at 09:54
  • Which database? Because `SQL` is not a database, it's a language, and you did not specify which DBMS you are using (like: MySQL, MSSQL, PostgreSQL, ....) – Luuk Apr 03 '22 at 09:56
  • Sorry PostgreSQL, and @Luuk ok, but i thin in this case i'd like to first solve the problem and than to use join. I mean step by step. – Max_T Apr 03 '22 at 09:57
  • You specified that you only want to see 1 emailaddress (`WHERE hasfriend.emailfriend = 'max.muster@mail.de' `) what happens when you delete that check? – Luuk Apr 03 '22 at 10:00
  • Than i'll see everyone how has "send" a message and if this stands here there are only persons how has him as friend – Max_T Apr 03 '22 at 10:01
  • [Joins Between Tables](https://www.postgresql.org/docs/current/tutorial-join.html) in the Postgres tutorial –  Apr 03 '22 at 10:07
  • It sounds like you have learned comma joins right now. If so, dismiss that book, tutorial or teacher immediately. You don't want to learn SQL as it was used in the 1980s. As to the task: what does "is friends with a certain person" mean? If that certain person is max.muster, are we looking for people that max.muster calls friends? Or are we looking for people who call max.muster as friend? Or must it be mutual? And must the person have just send any e-mail to someone or must it be an e-mail to max.muster? – Thorsten Kettner Apr 03 '22 at 11:52
  • Please add info (for all used tables) of how tables are created. A Table like `hasfriend` with only the fields: email, emailfriend, is a very limited description. Becuse after this we know two fields, `email` and `emailfiend` but there is no information on who is is a friend of. – Luuk Apr 03 '22 at 12:43
  • For getting table description see: https://www.postgresqltutorial.com/postgresql-administration/postgresql-describe-table/ OR https://stackoverflow.com/questions/109325/postgresql-describe-table – Luuk Apr 03 '22 at 12:45

0 Answers0