11

So I have three tables that are involved in my problem, 2 regular tables and a join table for a has many and belongs to many relationship. They look like this:

table1
--id
--data

table2
--id
--data

table1_table2
--table1_id
--table2_id

So, my question is how I would query (using a join) for something that would have one or more values in the table1_table2 for one item in table1. For instance:

Table 1
+----------+
|id | data |
+----------+
|1  | none |
+----------+
|4  | match|
+----------+

Table 2
+----------+
|id | data |
+----------+
|1  | one  |
+----------+
|2  | two  |
+----------+

table1_table2
+----------------------+
|table1_id | table2_id |
+----------------------+
|1         | 1         |
+----------------------+
|4         | 1         |
+----------------------+
|4         | 2         |
+----------------------+

I need a query that would match Table 1 row id 4 because it has a link via the join to both row 1 and 2 from table 2. If this is confusing please ask anything.

Maybe I was a bit unclear, I am using table1_table2 as a join not as a from. I need to make sure it matches 1 and 2 both from table 2.

Here is my query so far...

SELECT DISTINCT table1.id,
table2.data

FROM table1

LEFT JOIN table1_table2 ON table1.id = table1_table2.table1_id
LEFT JOIN table2 ON table2.id = table1_table2.table2_id

I need a where that will make that for an entry in table 1 it matches both 1 and 2 from table 2.

The output I am looking for would be:

+---------------------+
|table1.id|table2.data|
+---------------------+
|4        |one        |
+---------------------+
|4        |two        |
+---------------------+
Improve this question
5
  • Are you learning SQL (i.e. is this homework?) This is a very obvious follow up question to one of your previous questions (stackoverflow.com/questions/10726322/…). I don't have a problem with you learning on SO but you might want to tag your question [homework] if it is. Commented Jun 4, 2012 at 20:50
  • Though the question is related to a previous question, it isn't actually using the same tables. I am renaming the tables I am actually using. Commented Jun 4, 2012 at 21:17
  • 1
    Just a tip: The easiest way to phrase a question like this so it is answerable is to give the output you want. Commented Jun 4, 2012 at 21:19
  • @Red - Thanks, just thought it would be good to clarify your situation. Commented Jun 4, 2012 at 21:37
  • Does Postgre have temp tables or table variables or some other temporary way to materialize a table? Commented Jun 4, 2012 at 22:08

4 Answers 4

Reset to default
7

The following approach works if you can guarantee there are no duplicates in the Table1_Table2 table. Maybe you can start here and finesse it a bit. Note how the JOIN condition works -- putting the IN in the join condition works differently than if you put the IN condition in the WHERE clause.

I've used hash marks for values that you would need to have your code insert into the SQL.

SELECT Table1.id, COUNT(Table1_Table2.Table2_id)
FROM Table1
JOIN Table1_Table2 ON (Table1_Table2.Table1_id = Table1.id
                   AND Table1_Table2.Table2_id IN (#somelist#))
GROUP BY Table1.id
HAVING COUNT(Table1_Table2.Table2_id) = (#length of somelist#)

Oops -- you've changed your question in the way I suggested and I've ignored your edit. But this should get you started, as it returns all the Table1 id's that you are interested in.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

Here's my solution. It's not very generalized. You should post a comment if this solution doesn't work because it is too specific to your situation.

SELECT DISTINCT Table1.id
FROM Table1
INNER JOIN table1_table2 a ON (table1.id = table1_table2.table1_id
                           AND table2.id = 1)
INNER JOIN table1_table2 b ON (table1.id = table1_table2.table1_id
                           AND table2.id = 2)
Improve this answer

5 Comments

I believe this would work, the only problem being that sometimes I can have more than just two values, sometimes I could have up to 50 values. That many joins would become quite hard on the DB and dynamically making them would get rough.
So do you want the list of all the table1 id's that match EVERY table2 entry, regardless of how many things are in table2?
Well, not every thing in table 2, but at least what I said. In the example I just wanted the list of table 1 entries that matched 1 and 2. I could however (if they existed) need a query that gets all the table 1 entries that match 1, 2, 3, and 7 out of table 2. I need to make sure that it matches all of them.
Hmm. It's clear that my solution would work if you can change the SQL every time you want to pick a different list (1,2,3,7 instead of 1,2), but are you looking for static SQL that can take, as input, a list of matches you want like 1,2,3,7, and return all the table1.id's that have such matches?
Correct sir. I like your solution, but I need something more static.
1

If I were doing this in SQL Server I would put the values you want to check into a temp table (or table variable) and then use a variation of what @ChrisCunningham said.

CREATE TABLE #temp (Id INT)
INSERT INTO #temp 
VALUES (1, 3, 7)

SELECT a.id AS table1ID, table2.data
FROM
(
       SELECT Table1.id, Table1_Table2.table2_id 
  FROM Table1 JOIN Table1_Table2 
         ON (Table1_Table2.Table1_id = Table1.id                    
  AND Table1_Table2.Table2_id IN (SELECT Id FROM #temp)) 
  GROUP BY Table1.id 
  HAVING COUNT(Table1_Table2.Table2_id) = (SELECT count (*) FROM #temp) 
      ) a
JOIN Table2 ON Table2.id = a.Table2_id

Of course I'm not sure what mechanisms Postgre has for temp tables, but you would even make it a stored proc where you use some sort of split function to create the values for the temp table rather that the way I did it. But at least this might give you an idea.

Improve this answer

Comments

0

I think you should be able to select this:

select table1_id, count(*) as count from table1_table2 group by table1_id having count > 2;

group by, count and having are your friends!

Improve this answer

1 Comment

Maybe I was a bit unclear, I am using table1_table2 as a join not as a from. I need to make sure it matches 1 and 2 both. I will change the question to reflect this.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.