By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,483 Members | 1,052 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,483 IT Pros & Developers. It's quick & easy.

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

bugboy
100+
P: 160
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:

Expand|Select|Wrap|Line Numbers
  1. 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!
Oct 3 '07 #1
Share this Question
Share on Google+
16 Replies


Atli
Expert 5K+
P: 5,058
Hi.

Change this part:
Expand|Select|Wrap|Line Numbers
  1. WHERE wID = 2 OR wID = 1
  2.  
To this:
Expand|Select|Wrap|Line Numbers
  1. WHERE wID = 2 AND wID = 1
  2.  
And you should be good to go.
Oct 3 '07 #2

bugboy
100+
P: 160
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..

Expand|Select|Wrap|Line Numbers
  1. SELECT dID FROM s WHERE wID = 2 and wID = 1
  2.  
I think it's looking for both wID's to be in the same row before it will return results?
Oct 3 '07 #3

10K+
P: 13,264
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..

Expand|Select|Wrap|Line Numbers
  1. SELECT dID FROM s WHERE wID = 2 and wID = 1
  2.  
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?
Oct 3 '07 #4

Atli
Expert 5K+
P: 5,058
O right. I thought they were different ID's.

Try something like this:
Expand|Select|Wrap|Line Numbers
  1. SELECT t1.dID FROM myTbl as t1
  2. INNER JOIN myTbl as t2
  3.   ON t1.dID = t2.dID
  4.   AND t1.wID = 1
  5.   AND t2.wID = 2;
  6.  
Oct 3 '07 #5

bugboy
100+
P: 160
That's awsome! and it even uses my keys properly! I've even extended it to 3 wID's!

THANKS, i get it!


Expand|Select|Wrap|Line Numbers
  1. SELECT s1.dID FROM s as s1
  2.  
  3.       INNER JOIN s as s2
  4.  
  5.         ON s1.dID = s2.dID
  6.  
  7.       INNER JOIN s as s3 
  8.  
  9.         ON s2.dID = s3.dID
  10.  
  11.         AND s1.wID = 1
  12.  
  13.         AND s2.wID = 2
  14.  
  15.         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!?!....
Oct 3 '07 #6

bugboy
100+
P: 160
This works if the subquery only returns one dID but of course fails when it returns more than one:

Expand|Select|Wrap|Line Numbers
  1. Select def from d where did = (SELECT s1.dID FROM s as s1
  2.       INNER JOIN s as s2
  3.         ON s1.dID = s2.dID
  4.         AND s1.wID = 1
  5.         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...


Expand|Select|Wrap|Line Numbers
  1. SELECT s1.dID, d.def FROM d, s as s1
  2.  
  3.       INNER JOIN s as s2
  4.         ON s1.dID = s2.dID
  5.       INNER JOIN d as d1 
  6.         ON s1.dID = d1.dID
  7.         AND s1.wID = 1
  8.         AND s2.wID = 2 
  9.       GROUP BY def;
Any ideas? How do i get it to return just the defs that match the found dID's?
Oct 3 '07 #7

Atli
Expert 5K+
P: 5,058
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT s1.d, d.def FROM s AS s1
  2. INNER JOIN s AS s2
  3.   ON s1.dID = s2.dID
  4.   AND s1.wID = 1
  5.   AND s2.wID = 2
  6. LEFT JOIN d as d1
  7.   ON d.dID = t1.dID
  8.  
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:
Expand|Select|Wrap|Line Numbers
  1. INNER JOIN s AS s3
  2.   ON s3.dID = s1.dID
  3.   AND s3.wID = 3
  4.  
above the LEFT JOIN.

See how this works?
Oct 3 '07 #8

bugboy
100+
P: 160
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT d.did, d.def from d where d.dID IN 
  2.    (SELECT s1.dID FROM s as s1
  3.       INNER JOIN s as s2
  4.         ON s1.dID = s2.dID
  5.         AND s1.wID = 1
  6.         AND s2.wID = 2) 
  7.  GROUP BY d.def
Oct 3 '07 #9

bugboy
100+
P: 160
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..


Expand|Select|Wrap|Line Numbers
  1. SELECT s1.did, d1.def FROM s AS s1
  2.       INNER JOIN s AS s2
  3.         ON s1.dID = s2.dID
  4.         AND s1.wID = 1
  5.         AND s2.wID = 2
  6.       INNER JOIN s AS s3
  7.         ON s3.dID = s1.dID
  8.         AND s3.wID = 3
  9.       LEFT JOIN d as d1
  10.         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.
Oct 3 '07 #10

Atli
Expert 5K+
P: 5,058
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:
Expand|Select|Wrap|Line Numbers
  1.   ON s1.dID = d1.dID
  2.  
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.
Oct 4 '07 #11

bugboy
100+
P: 160
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...
Oct 4 '07 #12

bugboy
100+
P: 160
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.
Oct 4 '07 #13

bugboy
100+
P: 160
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.


Expand|Select|Wrap|Line Numbers
  1. SELECT c.title FROM s 
  2.       INNER JOIN w as w1
  3.         ON s.wID = w1.wID
  4.         AND w1.word = 'find'
  5.       INNER JOIN w as w2
  6.         ON s.wID = w2.wID
  7.         AND w2.word = 'menu'
  8.       LEFT JOIN c
  9.         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...
Oct 6 '07 #14

bugboy
100+
P: 160
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)
Expand|Select|Wrap|Line Numbers
  1. SELECT c.cid, c.title FROM s AS s1
  2.       INNER JOIN s AS s2
  3.         ON s1.cID = s2.cID
  4.         AND s1.wID = (SELECT wid FROM w WHERE word = 'find')
  5.         AND s2.wID = (SELECT wid FROM w WHERE word = 'menu')
  6.       LEFT JOIN c
  7.         ON s1.cID = c.cID
I uses: ref, eq_ref, eq_ref, const, const... not bad..
rows: 3, 1, 1, 1, 1
Oct 6 '07 #15

Atli
Expert 5K+
P: 5,058
You could reduce the sub-queries to one by doing something like this:
Expand|Select|Wrap|Line Numbers
  1. SELECT c.title FROM c
  2. INNER JOIN s
  3.     ON s.cID = c.cID
  4. INNER JOIN w
  5.     ON w.wID = s.wID
  6.     AND w.word IN(
  7.         SELECT wID 
  8.         FROM w 
  9.         WHERE word IN('find', 'menu')
  10.     )
  11.  
P.S. I was unable to test this, my test server crashed today :(
Oct 7 '07 #16

bugboy
100+
P: 160
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?

Expand|Select|Wrap|Line Numbers
  1. SELECT c.title FROM c
  2.       INNER JOIN s
  3.           ON s.cID = c.cID
  4.       INNER JOIN w
  5.           ON w.wID = s.wID
  6.           AND w.wID IN(
  7.               SELECT wID
  8.               FROM w
  9.               WHERE word IN ('find', 'menu')
  10.           )
Sorry to hear about your server, that sucks.
Oct 7 '07 #17

Post your reply

Sign in to post your reply or Sign up for a free account.