homepage Welcome to WebmasterWorld Guest from 107.22.78.233
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

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




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

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?

 

Demaestro




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

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.

martymac




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

This did not return any results, only a blank page.

martymac




msg:4000080
 6:03 pm on Oct 2, 2009 (gmt 0)

Nevermind, I believe I fixed it. I used OR instead of AND

Demaestro




msg:4000168
 8:47 pm on Oct 2, 2009 (gmt 0)

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?

Demaestro




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

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?

martymac




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

Demaestro you are right. The query is not breaking, but it is returning 2 for your example. How would I remedy this?

martymac




msg:4001485
 3:33 pm on Oct 5, 2009 (gmt 0)

Anyone else have any thoughts on this?

Demaestro




msg:4001581
 4:56 pm on Oct 5, 2009 (gmt 0)

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.

martymac




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

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!

Demaestro




msg:4002178
 3:56 pm on Oct 6, 2009 (gmt 0)

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

martymac




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

hey that worked! thanks!

Demaestro




msg:4002972
 2:44 pm on Oct 7, 2009 (gmt 0)

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.

martymac




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

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?

lukesimswilson




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

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved