I have a table that looks like this:

Database: Testdb
Table: Testtable

ID    | Value
--------------
1     | 105
2     | 105
3     | 105
4     | 105
1     | 255
2     | 255
2     | 311
3     | 311
4     | 311
1     | 500
3     | 500

I want to select all the IDs that have the value 105, 255 and 500. Which in this case is only ID 1. Using relational algebra I'd simply select the IDs with the desired values and intersect them. But as you know, MySQL doesn't agree with the whole intersect concept.

I therefore tried to use the much advocated inner join method. I'm relatively new at MySQL and could only find examples of the aforementioned method with two tables involved. As of now I have yet to get a single MySQL command to pass without an error when trying to get it to work.

In essence, this is what I want to do:

SELECT ID
FROM Testdb.Testtable
WHERE ID = "105";

INTERSECT

SELECT ID
FROM Testdb.Testtable
WHERE ID = "255";

INTERSECT

SELECT ID
FROM Testdb.Testtable
WHERE ID = "500";

As always when it comes to learning new stuff, I'm probably missing some ridiculously easy way to solve this. Or is it really this hard? Maybe I have to use 'union' and save temporary tables and stuff?

I'd really appreciate it if someone could give me a nudge in the right direction.

Thanks :)

Recommended Answers

All 2 Replies

You can include the same table multiple times in a query as long as you alias it. In this case, you'd include it a total of three times and join it to itself. The below should work, although I haven't tested it.

Select t1.ID
From Testtable t1
Inner Join Testtable t2
on t1.ID = t2.ID
Inner Join Testtable t3
On t1.ID = t3.ID
Where t1.Value = 105
And t2.Value = 255
And t3.Value = 500

Thanks! That worked like a charm!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.