mercredi 3 février 2016

MySQL SELECT IF ID in other table

i need to do a query on a database.

Table "servers"

+----------+----------+
| ID       | Name     |
+----------+----------+
| 1        | Server1  |
| 2        | Server2  |
| 3        | Server3  |
| 4        | Server4  |
+----------+----------+

Table "warranty":

+----------+----------+----------+
| linkType | linkID   | Year     |
+----------+----------+----------+
| 1        | 1        | 2015     |
| 2        | 3        | 2016     |
| 2        | 4        | 2016     |
+----------+----------+----------+

servers.iD = warranty.linkID

Now i would like to get a list like:

+----------+----------+----------+
| Warranty | ServerID | Name     |
+----------+----------+----------+
| no       | 1        | Server1  |
| no       | 2        | Server2  |
| yes      | 3        | Server3  |
| yes      | 4        | Server4  |
+----------+----------+----------|

Means i need a list of all Servers in table "server", with an Infofield "Warranty" (YES/NO).

IF the ID of an Server appears in table "warranty", it should print "yes", else: "no", but ONLY if linkType = 2

I know how to use an SELECT IF(server = "Y", "yes", "no") AS "server?"... Query, but i have no idea on how to SELECT IF an ID is present or not in another table.

Aucun commentaire:

Enregistrer un commentaire