CODEWITHSUNDEEP
mysqlsqldatabase
Mario Navarro Claras
Mario Navarro Claras

Reputation: 65

SQL query from 3 different tables.

First I want you to show my tables:

Table Users

CREATE TABLE Users(
   EMAIL VARCHAR(40) NOT NULL);

Table Actions

CREATE TABLE Actions
    IDACCION INT NOT NULL,
    ACCION VARCHAR(100) NOT NULL);

Table UserAction

CREATE TABLE UserAction(
    EMAIL VARCHAR(40) NOT NULL,
    IDACCION INT NOT NULL,
    DATETIME DATE NOT NULL,
    FOREIGN KEY (IDACCION) REFERENCES Actions(IDACCION),
    FOREIGN KEY (EMAIL) REFERENCES Users(EMAIL),
    PRIMARY KEY (IDACCION, EMAIL, DATETIME));

Then, I need to get the actions (ACCION from Actions) and DATETIME (DATETIME from UserAction) asociated with him, where the condition is that email (from User) be the same as Action email. Is it possible?

If is not possible, I can move DATETIME to Action table

Upvotes: 0

Views: 58

Answers (4)

Kostas Mitsarakis
Kostas Mitsarakis

Reputation: 4747

Note that datetime is a reserved word for MySQL.

You can try the following:

SELECT aa.ACCION, bb.`DATETIME`
FROM Actions AS aa
INNER JOIN UserAction AS bb
ON aa.IDACCION = bb.IDACCION
INNER JOIN Users AS cc
ON bb.EMAIL = cc.EMAIL;

Upvotes: 1

Brian Payne
Brian Payne

Reputation: 424

In answer to the second question, that of comparing dates, T-SQL contains a function called DATEDIFF that will compare dates rather well. I suggest you look at the Microsoft Developer Network article here.

Upvotes: 0

Marcos Rachid
Marcos Rachid

Reputation: 30

You really need to structure your tables, your Users table does not have a Primary key and don't seem to have any purpose on an application, for this query doesn't even need Users table.

SELECT a.ACCION, ua.DATETIME FROM Actions a
INNER JOIN UserAction ua on a.IDACCION = ua.IDACCION

Upvotes: 1

Maddy
Maddy

Reputation: 123

select 
 a.accion,
 ua.datetime
from 
 users u,
 actions a,
 useraction ua
where
 u.email=ua.email
 and ua.idaccion=a.idaccion;

Upvotes: 1

Related Questions