[56K WARN] Query question.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
waskelton4
Forum Contributor
Posts: 132
Joined: Mon Sep 09, 2002 6:42 pm

[56K WARN] Query question.

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

hmm not too sure but try switching the second join to use cc0 instead of c
waskelton4
Forum Contributor
Posts: 132
Joined: Mon Sep 09, 2002 6:42 pm

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I never use outer join, so I'm not sure.. I can't test it right now either.. :?
waskelton4
Forum Contributor
Posts: 132
Joined: Mon Sep 09, 2002 6:42 pm

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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 ?
waskelton4
Forum Contributor
Posts: 132
Joined: Mon Sep 09, 2002 6:42 pm

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

yes... in SQL format please.. so I can add it to my database and play with it..
waskelton4
Forum Contributor
Posts: 132
Joined: Mon Sep 09, 2002 6:42 pm

Post 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
m3rajk
DevNet Resident
Posts: 1191
Joined: Mon Jun 02, 2003 3:37 pm

Post 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 -
waskelton4
Forum Contributor
Posts: 132
Joined: Mon Sep 09, 2002 6:42 pm

Post 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
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post 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
waskelton4
Forum Contributor
Posts: 132
Joined: Mon Sep 09, 2002 6:42 pm

Post 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
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post 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.
waskelton4
Forum Contributor
Posts: 132
Joined: Mon Sep 09, 2002 6:42 pm

Post 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
Post Reply