CODEWITHSUNDEEP
phpmysqlsqlphpmyadmin
dave
dave

Reputation: 1

What type of mysql query do you run to compare two fields in separate tables?

I have a mysql table(table1) which has the following row:

topic_id: 1
topics: programming
description: A programming language is an artificial language designed to...

I have another table(table2) with this row:

desc_id: 1
description: In mathematics and computer science, an algorithm is an effective...
topics: mathematics, computer science, programming

What I'm looking to do is to run a query to compare the two topics fields and let me know which topics exist in table2 that don't exist in table1.

For instance, comparing the two above I'd like to run a query to let me know that topics mathematics and computer science don't exist in table1.

Upvotes: 0

Views: 439

Answers (4)

dqhendricks
dqhendricks

Reputation: 19251

normalize by creating a third table, one that links table 2 to table 1 with a many to many relationship.

Table_1
id, etc

Table_2
id, etc

Table_3
id, table1_id, table2_id

you could then use simple joins to create a query that will pull the relavent data

SELECT * FROM Table_1 LEFT JOIN Table_3 ON Table_1.id = Table_3.table1_id WHERE Table_3.table2_id = $table2_id

This will pull all topics for the course.

Upvotes: 0

Marc B
Marc B

Reputation: 360872

If you normalized your table2 so that the topics list is in a separate sub-table, this would be a trivial query. As it stands now, it's difficult as by default mysql won't see those seperate topics in table2.topics as discrete topics. It's just a long string that happens to have commas in there.

Thankfully, MySQL has the find_in_set() function, which can help out immensely, but this function isn't available elsewhere. Not having access to your dataset, I'm just guessing here, but this should do the trick:

SELECT table1.topics, count(table1.topic_id) AS cnt
FROM table1
LEFT JOIN table2.topics ON FIND_IN_SET(table1.topics, table2.topics) = 0
GROUP BY table1.topics
HAVING cnt = 0

Basically, join the tables wherever the table1 topic is NOT in a table2 topic and count how many times the table1 topic shows up like this. If it shows up zero times, then it's present in at least one record in table2.

Upvotes: 0

gion_13
gion_13

Reputation: 41533

I would use a subquery, but it can also be done with innerjoins : 

SELECT *
FROM `table2`
WHERE `topics` NOT IN (
    SELECT DISTINCT(topics)
    FROM `table1`
)

Upvotes: 1

Khurram Ijaz
Khurram Ijaz

Reputation: 1864

you can try NOT IN

i.e.

SELECT topics FROM table2 where topics NOT IN( select topics from table1)

Upvotes: 0

Related Questions