Welcome to WebmasterWorld Guest from 50.16.78.128

Forum Moderators: open

Message Too Old, No Replies

MySQL: Find column B if matching column A entries from query

   
4:15 pm on Oct 2, 2009 (gmt 0)

5+ Year Member



Ok this might sound a little bit confusing. I will do my best to explain.

I have a table with two columns of information:
---------------------------
Column A Column B
---------------------------
example.net websiteA.com
example.org websiteA.com
example.net websiteB.com
example.org websiteD.org
example.net websiteE.net
example.org websiteE.net
example.net websiteF.edu
---------------------------

If I input 'example.net' and 'example.org' I want the query to return results from column B where both are present in column A. For example, if I input example.net and example.org against the above data, it returns:

websiteA.com
websiteE.net

Ive tried to develop a query statement for this but nothing I try seems to work. Any ideas?

5:45 pm on Oct 2, 2009 (gmt 0)

WebmasterWorld Senior Member demaestro is a WebmasterWorld Top Contributor of All Time 10+ Year Member



select
columnB, count(columnB) as count_matching_rec
from
table_name
where
columnA = $serch_term1 and columnA = $serch_term2
group by
columnB
having
count(coulmnB) = 2

**This might be what you want... the issue would be if you added a 3rd search term you would have to change the "HAVING" clause to be 3... like this:

having
count(coulmnB) = 3

I am not sure if this is 100% right but post back and we will see if we can get it.

Basically what is happening in this query is we are counting the amount of identical coulmnBs that are being returned then telling the query we only want them if it has a count of 2... hopefully indicating that it matched both search terms.

6:02 pm on Oct 2, 2009 (gmt 0)

5+ Year Member



This did not return any results, only a blank page.
6:03 pm on Oct 2, 2009 (gmt 0)

5+ Year Member



Nevermind, I believe I fixed it. I used OR instead of AND
8:47 pm on Oct 2, 2009 (gmt 0)

WebmasterWorld Senior Member demaestro is a WebmasterWorld Top Contributor of All Time 10+ Year Member



Right, sorry now that I think about it OR would be right.

Do you have it doing what you want or does it need some refining?

8:58 pm on Oct 2, 2009 (gmt 0)

WebmasterWorld Senior Member demaestro is a WebmasterWorld Top Contributor of All Time 10+ Year Member



This query will break if it matched search_term1 twice

If you have data like this you will get a bad result

example.net websiteA.com
example.ca websiteA.com
example.net websiteA.com

If you input 'example.net' and 'example.org' then "websiteA.com" would still be returned because it matched 'example.net' 2 times but not example.org... this would be a false positive if I understand this correctly. Could the data appear this way?

9:05 pm on Oct 2, 2009 (gmt 0)

5+ Year Member



Demaestro you are right. The query is not breaking, but it is returning 2 for your example. How would I remedy this?
3:33 pm on Oct 5, 2009 (gmt 0)

5+ Year Member



Anyone else have any thoughts on this?
4:56 pm on Oct 5, 2009 (gmt 0)

WebmasterWorld Senior Member demaestro is a WebmasterWorld Top Contributor of All Time 10+ Year Member



Marty, 1 way to avoid this would be to control duplicate entries, if that isn't possible then you can try to do this in 2 queries then do some work in code to find ones that match both.

select
'term1' as term_match, columnB
from
table_name
where
columnA = $serch_term1
UNION
select
'term2' as term_match, columnB
from
table_name
where
columnA = $serch_term2

Then looping through the results check for columnB values from term1 that match columnB values from term2, discard anything else that doesn't match and keep what does.

A little more laborious but it will be more logically sound.

6:30 pm on Oct 5, 2009 (gmt 0)

5+ Year Member



Alright, I took your advice and re-structured the MySQL query. I now have a question about how to loop through the data but I will post that in the PHP forum. Thanks again for all of your help!
3:56 pm on Oct 6, 2009 (gmt 0)

WebmasterWorld Senior Member demaestro is a WebmasterWorld Top Contributor of All Time 10+ Year Member



Out of curiosity try this out.

select
t1.columnB
from
table_name t1, table_name t2
where
t1.columnA = $serch_term1
and
t2.columnA = $serch_term2
and
t1.columnB = t2.columnB

2:38 pm on Oct 7, 2009 (gmt 0)

5+ Year Member



hey that worked! thanks!
2:44 pm on Oct 7, 2009 (gmt 0)

WebmasterWorld Senior Member demaestro is a WebmasterWorld Top Contributor of All Time 10+ Year Member



Sweet... came to me while I wasn't even thinking about it.

I knew there was a way but I didn't have time to delve deep into it. My brain must have kept working on it in the background or something cause it just spit out the answer randomly.

6:52 pm on Oct 7, 2009 (gmt 0)

5+ Year Member



My next task is figuring out how to adapt so that it dynamically changes depending on how many search terms can be entered (there can be anywhere from 1 to 20 terms).

Any thoughts?

10:53 am on Oct 8, 2009 (gmt 0)

5+ Year Member



SELECT count(*) as counter,B FROM temp where A in("example.net","example.org") group by B

This will show you how many matches their are for example.net and example.org

you will have to loop through the results and filter the results that have a counter of less than 2

You could put 20 search terms inside the IN statement e.g in("example.net","example.org","example.org","example.org") and so on.....

then just filter out the results returned that are less that the amount you are searching for

 

Featured Threads

Hot Threads This Week

Hot Threads This Month