Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.15 MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test
Database changed
mysql> CREATE TABLE emp
-> (
-> id int primary key,
-> name varchar(20),
-> did int
-> );
Query OK, 0 rows affected (0.45 sec)
mysql>
mysql>
mysql>
mysql> CREATE TABLE dep
-> (
-> id int primary key,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.35 sec)
mysql>
mysql> INSERT INTO dep
-> VALUES
-> (1,'lakers'),
-> (2,'spurs'),
-> (3,'sixers'),
-> (4,'pacers'),
-> (5,'warriors')
->
-> ;
Query OK, 5 rows affected (0.06 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql>
mysql> INSERT INTO emp
-> VALUES
-> (1,'rohit',1),
-> (2,'amit',2),
-> (3,'haris',3),
-> (4,'eti',4)
-> ;
Query OK, 4 rows affected (0.08 sec)
Records: 4 Duplicates: 0 Warnings: 0
I ran your query without aliases
mysql> select * from dep,emp;
+----+----------+----+-------+------+
| id | name | id | name | did |
+----+----------+----+-------+------+
| 1 | lakers | 1 | rohit | 1 |
| 1 | lakers | 2 | amit | 2 |
| 1 | lakers | 3 | haris | 3 |
| 1 | lakers | 4 | eti | 4 |
| 2 | spurs | 1 | rohit | 1 |
| 2 | spurs | 2 | amit | 2 |
| 2 | spurs | 3 | haris | 3 |
| 2 | spurs | 4 | eti | 4 |
| 3 | sixers | 1 | rohit | 1 |
| 3 | sixers | 2 | amit | 2 |
| 3 | sixers | 3 | haris | 3 |
| 3 | sixers | 4 | eti | 4 |
| 4 | pacers | 1 | rohit | 1 |
| 4 | pacers | 2 | amit | 2 |
| 4 | pacers | 3 | haris | 3 |
| 4 | pacers | 4 | eti | 4 |
| 5 | warriors | 1 | rohit | 1 |
| 5 | warriors | 2 | amit | 2 |
| 5 | warriors | 3 | haris | 3 |
| 5 | warriors | 4 | eti | 4 |
+----+----------+----+-------+------+
20 rows in set (0.03 sec)
I ran your query as you gave it, with aliases
mysql> select * from dep a,emp b;
+----+----------+----+-------+------+
| id | name | id | name | did |
+----+----------+----+-------+------+
| 1 | lakers | 1 | rohit | 1 |
| 1 | lakers | 2 | amit | 2 |
| 1 | lakers | 3 | haris | 3 |
| 1 | lakers | 4 | eti | 4 |
| 2 | spurs | 1 | rohit | 1 |
| 2 | spurs | 2 | amit | 2 |
| 2 | spurs | 3 | haris | 3 |
| 2 | spurs | 4 | eti | 4 |
| 3 | sixers | 1 | rohit | 1 |
| 3 | sixers | 2 | amit | 2 |
| 3 | sixers | 3 | haris | 3 |
| 3 | sixers | 4 | eti | 4 |
| 4 | pacers | 1 | rohit | 1 |
| 4 | pacers | 2 | amit | 2 |
| 4 | pacers | 3 | haris | 3 |
| 4 | pacers | 4 | eti | 4 |
| 5 | warriors | 1 | rohit | 1 |
| 5 | warriors | 2 | amit | 2 |
| 5 | warriors | 3 | haris | 3 |
| 5 | warriors | 4 | eti | 4 |
+----+----------+----+-------+------+
20 rows in set (0.00 sec)
mysql>
I ran your query with aliases in the SELECT
clause as well as the FROM
clause
mysql> select a.*,b.* from dep a,emp b;
+----+----------+----+-------+------+
| id | name | id | name | did |
+----+----------+----+-------+------+
| 1 | lakers | 1 | rohit | 1 |
| 1 | lakers | 2 | amit | 2 |
| 1 | lakers | 3 | haris | 3 |
| 1 | lakers | 4 | eti | 4 |
| 2 | spurs | 1 | rohit | 1 |
| 2 | spurs | 2 | amit | 2 |
| 2 | spurs | 3 | haris | 3 |
| 2 | spurs | 4 | eti | 4 |
| 3 | sixers | 1 | rohit | 1 |
| 3 | sixers | 2 | amit | 2 |
| 3 | sixers | 3 | haris | 3 |
| 3 | sixers | 4 | eti | 4 |
| 4 | pacers | 1 | rohit | 1 |
| 4 | pacers | 2 | amit | 2 |
| 4 | pacers | 3 | haris | 3 |
| 4 | pacers | 4 | eti | 4 |
| 5 | warriors | 1 | rohit | 1 |
| 5 | warriors | 2 | amit | 2 |
| 5 | warriors | 3 | haris | 3 |
| 5 | warriors | 4 | eti | 4 |
+----+----------+----+-------+------+
20 rows in set (0.00 sec)
mysql>
I just ran these in MySQL 5.6.15 on my Windows 8 Laptop. It works fine with and without aliases. It might be SQL Fiddle that has the problem in this instance with a MySQL Cartesian Product.
your first query is standard SQL (the parentheses are redundant, only needed by MS-Access) and should work just fine in SQLite, with or without the parentheses:
SELECT
Message.messageContent, Message.messageDateTime
FROM
Message
INNER JOIN
( Contact INNER JOIN contact_m2m_message
ON Contact.contactID = contact_m2m_message.contactID
)
ON Message.msgID = contact_m2m_message.messageID ;
Edit: Actually, SQLite needs the parentheses, too, at least the version provided in SQL-Fiddle.
The 2nd query is not valid because you have not provided an alias for the derived table:
SELECT
Message.messageContent, Message.messageDateTime
FROM
Message
INNER JOIN
( Select Contact.contactNumber, Contact.contactName
FROM Contact INNER JOIN contact_m2m_message
ON Contact.contactID = contact_m2m_message.contactID
) --<-------------------------------------- no alias
ON Message.msgID = contact_m2m_message.messageID;
To make it work wirh a derived table, you have to provide an alias for the derived table and also add the contact_m2m_message.messageID
in the SELECT
list of the derived table (and remove columns that are not used):
SELECT
Message.messageContent, Message.messageDateTime
FROM
Message
INNER JOIN
( Select -- Contact.contactNumber, Contact.contactName,
contact_m2m_message.messageID
FROM Contact INNER JOIN contact_m2m_message
ON Contact.contactID = contact_m2m_message.contactID
) AS cm
ON Message.msgID = cm.messageID;
But I don't think you should use that. The first query should work. You can re-arrange the order how of the tables are joined. Using table aliases also helps in readibility:
SELECT
m.messageContent, m.messageDateTime
FROM
Message AS m
INNER JOIN
contact_m2m_message AS cm
ON m.msgID = cm.messageID
INNER JOIN
Contact AS c
ON c.contactID = cm.contactID ;
Best Answer
In order to implement the join on two columns simultaneously, you can use an EXISTS predicate:
If you are using version 3.15.0 or later, you can also take advantage of row value comparisons: