CODEWITHSUNDEEP
mysqlsql
Tim
Tim

Reputation: 424

Select if exists in another table, with AND condition

I haven't found the answer to my question specifically even though there are very similar subjects on the same matter.

Here is my database (MySQL) :

Table urls
ID;URL

Table links
ID;TARGET;SOURCE

I wish to select links where their SOURCE and TARGET exists in the urls table.

I'm guessing subqueries or left join will be the answer, but I can't seem to get it working.

Upvotes: 0

Views: 82

Answers (1)

P.Salmon
P.Salmon

Reputation: 17615

IF you mean both target and source in the same links row have to exists in urls 2 inner joins for example

DROP TABLE IF EXISTS LINKS,URLS;
CREATE Table urls
(ID INT,URL VARCHAR(3));

CREATE Table links
(ID INT,TARGET VARCHAR(3),SOURCE VARCHAR(3));

INSERT INTO URLS VALUES
(1,'AAA'),(2,'BBB'),(3,'CCC'),(4,'DDD');

INSERT INTO LINKS VALUES
(1,'AAA','BBB'),(2,'CCC','ZZZ'),(3,'ZZZ','DDD');

SELECT *
FROM LINKS
JOIN URLS U1 ON U1.URL = LINKS.SOURCE
JOIN URLS U2 ON U2.URL = LINKS.TARGET;

+------+--------+--------+------+------+------+------+
| ID   | TARGET | SOURCE | ID   | URL  | ID   | URL  |
+------+--------+--------+------+------+------+------+
|    1 | AAA    | BBB    |    2 | BBB  |    1 | AAA  |
+------+--------+--------+------+------+------+------+

Upvotes: 1

Related Questions