Page 1 of 1

[56K WARN] Query question.

Posted: Fri Mar 04, 2005 4:16 pm
by waskelton4
Hello all,

I'm trying to build a form to do ad-hoc querys for a project i'm working on and I have a question about this query i'm trying to build.

I have a feeling i should know the answer to this.. so forgive me If it's rather obvious..

I have a table full of Contacts(people) and a table to link them to various categories that they may belong to..
tblContacts, tbl_contact_category, tblCategories

I'd like to write a query that pulls all of the contacts that are in a certian category yet make sure they do not belong to another of the categories..

I have tried a number of things.. this the latest one..

Code: Select all

SELECT DISTINCT c.* FROM tblcontacts c  
LEFT OUTER JOIN tbl_contact_category cc0 on c.intContact_ID = cc0.intContactID 
LEFT OUTER JOIN tbl_contact_category cc1 on c.intContact_ID = cc1.intContactID 
WHERE (cc0.intCategoryID = 15)AND NOT (cc1.intCategoryID = 12)
This seems to return all of cateogry 15 weather they are in category 12 or not.

Can someone offer me some assistance please. This has give me a headache :)

Thanks

Will

Posted: Fri Mar 04, 2005 4:27 pm
by feyd
hmm not too sure but try switching the second join to use cc0 instead of c

Posted: Mon Mar 07, 2005 11:13 am
by waskelton4
feyd wrote:hmm not too sure but try switching the second join to use cc0 instead of c
I changed the query to...

Code: Select all

SELECT DISTINCT c.* FROM tblcontacts c 
LEFT OUTER JOIN tbl_contact_category cc0 on c.intContact_ID = cc0.intContactID
LEFT OUTER JOIN tbl_contact_category cc1 on cc0.intContactID = cc1.intContactID
WHERE (cc0.intCategoryID = 15)AND NOT (cc1.intCategoryID = 12)
Same result set..

Any other ideas.. Are the join types correct?
is the where clause formatted right?

Thanks again for the help

Will

Posted: Mon Mar 07, 2005 11:18 am
by feyd
I never use outer join, so I'm not sure.. I can't test it right now either.. :?

Posted: Mon Mar 07, 2005 11:22 am
by waskelton4
feyd wrote:I never use outer join, so I'm not sure.. I can't test it right now either.. :?
really?

why do you not use outer join?
don't need it.. or is it a bad idea?

ws

Posted: Mon Mar 07, 2005 11:34 am
by feyd
can you post an export of the table structure and data? I may be able to play with it then. I use LEFT JOINs a lot.. haven't ever stuck OUTER in there.. so I can't remember what it does, if anything. Probably nothing.

is there a reason why you have intContact_ID and intContactID ?

Posted: Mon Mar 07, 2005 11:45 am
by waskelton4
the contactID and contact_ID was initialy a way for me to see that the one with the underscore was a Primary key.. the other.. foriegn key.. but after using it for a little while it didn't help much.

do you want the table structure and some of the data from the two tables?

thanks again..

will

Posted: Mon Mar 07, 2005 11:51 am
by feyd
yes... in SQL format please.. so I can add it to my database and play with it..

Posted: Mon Mar 07, 2005 12:18 pm
by waskelton4
thanks for the help..

The primary talbe has over 16000 rows in it and contains people and some of their info..

I'm gonna cut it down first.

can you PM me your email so i can send it that way?

thanks
ws

Posted: Mon Mar 07, 2005 2:39 pm
by m3rajk
attach the result of:
mysqldump -u <yourusername> --database <databasename> -p > tablestructurebackup.sql

to back everything up add a c before the u and after the -

Posted: Mon Mar 07, 2005 3:16 pm
by waskelton4
I'm gonna go ahead and post here.

I took out everything but the first/last names and the ID.. that should be enough.. I also only took the first 200 rows of the contacts table and then cut the other table down to include only relavent records..

thanks for all the help..

http://mnogroup.com/will/testingstuff.sql

ws

Posted: Mon Mar 07, 2005 3:24 pm
by magicrobotmonkey
I think you want it like this:

Code: Select all

SELECT DISTINCT c.* FROM tblcontacts c
LEFT JOIN tbl_contact_category cc0 on c.intContact_ID = cc0.intContactID AND cc0.intCategoryID = 15
LEFT  JOIN tbl_contact_category cc1 on c.intContactID = cc1.intContactID AND cc1.intCategoryID = 12
WHERE cc0.intContactID IS NOT NULL AND cc1.intContactID  IS NULL

Posted: Mon Mar 07, 2005 4:53 pm
by waskelton4
I think that may have worked magicrobotmonkey but the format doesn't seem to work with a more simple query..

such as..

Code: Select all

SELECT DISTINCT c.* FROM tblcontacts c LEFT OUTER JOIN tbl_contact_category cc0 on c.intContact_ID = cc0.intContactID AND cc0.intCategoryID = 1 WHERE (cc0.intCategoryID IS NULL )
That returns only 4794 rows in my testing database and I know there are over 11000 rows in the contact_category table with categoryID = 1

Maybe I should share some more info on my project...

I have tried to create a web form that will allow the users to create ad-hoc queries.. primarily selecting contacts based on their categories.

The application allows them to build the query as they go in this sort of fasion..

They can choose..
1. all contacts in category 1,12, 15, 80, 50 OR 23
2. but NOT if they are in category 10 or 13

they can also choose
1. all in categories 3, 5, AND 9
2. OR category 24.

those are examples of what they may want to do. It actually can get more complicated than that as well.
here is a screen shot of the form.. no laughing please ;)
Image

The small select boxes that are blank in the blue, grey and tan sections allow the users to select "AND" or "OR" additions to their query.

as you can see the radio buttons above and below the categories allow the user to choose if the contact is in any of the categories.. or in ALL of the categories selected and they may choose to include those contacts or not to with the "is" "is not" switch. I may have just made it more confusing. hopefully the form is intuitive enough and makes sense.

Does this shed much/any light on what i'm trying to do?

many thanks..

will

Posted: Mon Mar 07, 2005 7:00 pm
by magicrobotmonkey
right - so for each selected category, you need to do a left join on cat id = person id and cat type = whatever and if it is looking for in then check for is not null and if its looking for not in, look for is null.

I think you might have tried my recommendation wrong.

Code: Select all

SELECT DISTINCT c.* FROM tblcontacts c LEFT OUTER JOIN tbl_contact_category cc0 on c.intContact_ID = cc0.intContactID AND cc0.intCategoryID = 1 WHERE (cc0.intCategoryID IS NULL )
That returns only 4794 rows in my testing database and I know there are over 11000 rows in the contact_category table with categoryID = 1
that query is looking for all contact that are not in catID 1, as if catID IS NULL then it was not found in the left join and thus the user is not in it. Yet you said that there were 11000 users in cat 1.

Posted: Tue Mar 08, 2005 10:11 am
by waskelton4
Doh..

I guess i mixed them up.. the double negative threw me.

So in this case if i use the IN operator with a long list of categories.. will the single join work?

The way I was building the queries I was getting quite a few joins in there and sometimes it would crash the query..

Thanks for the help! LOTS

ws