Returning rows that match two keys, exculding those that match only one.

[bugboy]

I'm running a query on a table with two foreign key columns- wID and dID which define a many to many relationship. I know the value of two different wID's ( wID=1, wID=2) and I need to find only the dID's that match BOTH wID's excluding those that match only one.

I have made it this far:

SELECT dID, COUNT(*) AS count FROM table WHERE wID = 2 or wID = 1 GROUP BY dID ORDER BY Num DESC

This returns all dID's that match either of the wID's then puts a count of (2) on the dID's that match BOTH wID's and a (1) on the dID's that match only one of the wID's. I only want the dID's with a count of (2).

dID / count
5 /2
1 /2
2 /2
3 /1
4 /1

from the result above i only actually want this:

dID
5
1
2

Can i get rid of the extra rows (with a count of (1)) by modifying the query or will i need to figure out a way to do it in php after the query?

Thanks for you help!

[Atli]

Hi.

Change this part:

WHERE wID = 2 OR wID = 1
 

To this:

WHERE wID = 2 AND wID = 1
 

And you should be good to go.

[bugboy]

Hmmm i didn't get any results back.. i think it's because both wID's are from the same column but not the same row..

SELECT dID FROM s WHERE wID = 2 and wID = 1
 

I think it's looking for both wID's to be in the same row before it will return results?

[r035198x]

Hmmm i didn't get any results back.. i think it's because both wID's are from the same column but not the same row..

SELECT dID FROM s WHERE wID = 2 and wID = 1
 

I think it's looking for both wID's to be in the same row before it will return results?

Is this the same problem as in this thread?

[Atli]

O right. I thought they were different ID's.

Try something like this:

SELECT t1.dID FROM myTbl as t1
INNER JOIN myTbl as t2
  ON t1.dID = t2.dID
  AND t1.wID = 1
  AND t2.wID = 2;
 

[bugboy]

That's awsome! and it even uses my keys properly! I've even extended it to 3 wID's!

THANKS, i get it!

SELECT s1.dID FROM s as s1
 
      INNER JOIN s as s2
 
        ON s1.dID = s2.dID
 
      INNER JOIN s as s3 
 
        ON s2.dID = s3.dID
 
        AND s1.wID = 1
 
        AND s2.wID = 2
 
        AND s3.wID = 3;

My next challenge is to join it to the 'd' table (where dID is the PK) and get the 'def' column value from the row matching each dID returned. Can i use the above as a subquery?.. or are subqueries not usable if they return multiple values?
Maybe i can INNER JOIN the 'd' table in the same query without losing efficiency!?!....

[bugboy]

This works if the subquery only returns one dID but of course fails when it returns more than one:

Select def from d where did = (SELECT s1.dID FROM s as s1
      INNER JOIN s as s2
        ON s1.dID = s2.dID
        AND s1.wID = 1
        AND s2.wID = 2);

Any ways to handle a subquery with more than one result?


My first attempt at doing it with a join:
On second thought.. maybe i don't get joins... this returns all the def's from the d table when in practice it should only return the ones that match the two wID's...

SELECT s1.dID, d.def FROM d, s as s1
 
      INNER JOIN s as s2
        ON s1.dID = s2.dID
      INNER JOIN d as d1 
        ON s1.dID = d1.dID
        AND s1.wID = 1
        AND s2.wID = 2 
      GROUP BY def;

Any ideas? How do i get it to return just the defs that match the found dID's?

[Atli]

Your close. The FROM clause in your query should only have one table.
Putting a list of tables separated by a comma is the same as using JOIN without a ON clause, which causes all columns to be added on all columns, which will return a big messy pile of rows.

It should look more like this.

SELECT s1.d, d.def FROM s AS s1
INNER JOIN s AS s2
  ON s1.dID = s2.dID
  AND s1.wID = 1
  AND s2.wID = 2
LEFT JOIN d as d1
  ON d.dID = t1.dID
 

Every boolean expression you add to a INNER JOIN must be true, or the entire row will be dropped. If a single condition is not met, the row will not be added to the result set.

LEFT JOIN, on the other hand, will not cause the row to be dropped. If the boolean expressions you add to a LEFT JOIN are not true, the row will remain in the result set with a NULL for the fields the LEFT JOIN adds.

So, you can add the third table by putting:

INNER JOIN s AS s3
  ON s3.dID = s1.dID
  AND s3.wID = 3
 

above the LEFT JOIN.

See how this works?

[bugboy]

That's great, i'll give it a shot and post back. Thanks!

Here's what i've come up with since my last post.. It gives me what i want but it doesn't use my 'd' table's primary key making it too slow.. since there will be a few million rows.. eventually. I'll try what you suggest...

I used an 'IN' instead of '=' before the subquery to handle multiple subquery results.

SELECT d.did, d.def from d where d.dID IN 
   (SELECT s1.dID FROM s as s1
      INNER JOIN s as s2
        ON s1.dID = s2.dID
        AND s1.wID = 1
        AND s2.wID = 2) 
 GROUP BY d.def

[bugboy]

Ok so i think it's close... I made a few adjustments to get past errors (below) and it's now stuck on the LEFT JOIN.. it doesn't know what d.dID is in the ON statement..

SELECT s1.did, d1.def FROM s AS s1
      INNER JOIN s AS s2
        ON s1.dID = s2.dID
        AND s1.wID = 1
        AND s2.wID = 2
      INNER JOIN s AS s3
        ON s3.dID = s1.dID
        AND s3.wID = 3
      LEFT JOIN d as d1
        ON d.dID = d1.dID

I changed the s1.d to s1.dID and I also changed the d.dID to d1.dID in the first select, then I changed the t1.dID in the LEFT JOIN to d1.dID to get past errors.. maybe i made new ones?

I just can't figure out how to get it to recognize the d.dID. If i change it to d1.dID = d1.dID it returns all the d1.def's with the same s1.did '2' for each.

[Atli]

Sorry about the weird table names there. Wrote the query for another table set and overlooked a few names there :P

It doesn't recognize 'd.dID' because you are not using a table called 'd' before the LEFT JOIN. You want to join with the s1-s3 tables, so picking any one of them will do. I chose s1 because it makes sense to build on the first table rather than the once you JOIN later.

Try doing:

  ON s1.dID = d1.dID
 

You should also know, for the query in your previous post, sub-queries are inherently slow. If you can, always avoid using a sub-query and use JOINS and such instead.

[bugboy]

Thanks for all your help Atli! It works great and i think i'm starting to understand joins!

the EXPLAIN says that each table is using 'ref' which i looked up and it says that it means it can't use proper indexes.. should i mess around with combining columns into indexes or is 'ref'' a good target when having so many joins?

Thanks again this is awesome! I'm going to load some data in and see how it runs...

[bugboy]

Ok so i played around with indexes and managed to get three 'eq_ref's' and one 'ref'! with 'rows' 3, 1, 1, 1. From what i've read that's really good! I tied s.dID and s.wID together in a unique index and it really helped. When i add a fourth INNER JOIN i get rows 2, 1, 1, 1 ,1 cool!

I've loaded the DB with 10,000 records and it's still fast! now 1,000,000...

Thanks again.

[bugboy]

Ok, now i've tried to modify the query to go straight from a 'word' in table 'w' to a title in table 'c' linked by the table 's'.

To recap: Before i was starting with two s.wID's to find a c.title that they both match... but i needed to know the wID for the word first. So now i'm stretching the query to start with the word it's self without needing to know it's wID.

SELECT c.title FROM s 
      INNER JOIN w as w1
        ON s.wID = w1.wID
        AND w1.word = 'find'
      INNER JOIN w as w2
        ON s.wID = w2.wID
        AND w2.word = 'menu'
      LEFT JOIN c
        ON s.cID = c.cID

I'm not getting any results from this.. but no errors either. I have defiantly linked the two word's wID's to a row in table c using table s.. but still nothing..

any idea's? i'm probably not getting the 'join' thing quite yet...

[bugboy]

Ok.. so this is what i've come up with.. i was trying to avoid subquerys... but i guess i still don't get joins..

Can anyone do this with a join?

Would it be more efficient?

Three tables: w (word, wid), s (wid, cid) and c (cid, title)

SELECT c.cid, c.title FROM s AS s1
      INNER JOIN s AS s2
        ON s1.cID = s2.cID
        AND s1.wID = (SELECT wid FROM w WHERE word = 'find')
        AND s2.wID = (SELECT wid FROM w WHERE word = 'menu')
      LEFT JOIN c
        ON s1.cID = c.cID

I uses: ref, eq_ref, eq_ref, const, const... not bad..
rows: 3, 1, 1, 1, 1

[Atli]

You could reduce the sub-queries to one by doing something like this:

SELECT c.title FROM c
INNER JOIN s
    ON s.cID = c.cID
INNER JOIN w
    ON w.wID = s.wID
    AND w.word IN(
        SELECT wID 
        FROM w 
        WHERE word IN('find', 'menu')
    )
 

P.S. I was unable to test this, my test server crashed today :(

[bugboy]

Thanks!

This would be great... much easier to add and change the number of words used in the query... but it returns all of the titles that match either word.. Is there a way to get it to return only the titles that match both? I've played with the order, and join types with no luck... It seems the order you make joins doesn't matter.. is that right?

SELECT c.title FROM c
      INNER JOIN s
          ON s.cID = c.cID
      INNER JOIN w
          ON w.wID = s.wID
          AND w.wID IN(
              SELECT wID
              FROM w
              WHERE word IN ('find', 'menu')
          )

Sorry to hear about your server, that sucks.