many-to-many query

[none none]

http://dev.mysql.com/doc/mysql/en/JOIN.html

-Original Message-
From: Emily Lena Jones [mailto:[EMAIL PROTECTED] 
Sent: Thursday, October 28, 2004 1:05 PM
To: [EMAIL PROTECTED]
Subject: many-to-many query

Hi, I'm totally new at this so have no idea whether I'm asking for something
easy or quite difficult.

I am working in MySQL 3.23.58/PHP and am trying to construct a rather complex
query.  I have three tables: resources (containing resid, descr, title, url),
topicdir (containing topicid, resid) and topic (containing topicid,
topic, parentid).  Each resource is associated with numerous topics.
Initially I wanted to get just all the resources associated with a
particular topic (in this example #36), so had the following SQL statement:

SELECT resources.title, resources.descr, resources.url
FROM resources, topic_dir
WHERE topic_dir.topic_id = 36 AND topic_dir.res_id = resources.res_id
ORDER BY resources.title

Now it gets a bit more complicated: I need all resources associated
with topic 36 (or whatever) but also all topics with parentid 998
associated with the resources associated with topic 36.  Because I'm working in
an earlier version of MySQL, I can't use a subquery.

Any ideas/solutions/help would be most appreciated!

Many thanks,
Emily

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Determining if query will work

[none none]

Like so many other people..

No one puts any collective thought into what they are doing..

Instead of moving on and trying to finish the rest yourself, you rely
on someone else to finish it for you.

-Original Message-
From: Eve Atley [mailto:[EMAIL PROTECTED] 
Sent: Monday, October 25, 2004 9:49 AM
To: [EMAIL PROTECTED]
Cc: Mysql
Subject: RE: Determining if query will work


I can't tell you how *glad* I am to get this running! Big hugs to you,
Shawn!

It's running beautifully. My only question is, after I run the queries,
I notice it won't let me create the temporary table again (saying
'tmpCandidates' already exists). Do I just need to then log out of my
client (MySQL Control Center) and back in to get rid of that temp table?
As I'll need to change what it searches for (ie baan, peoplesoft, etc.).
Or is there a query I can put in at the end of the queries to destroy
the temporary table once through with it?

Final query setup posted below.

Thanks,
Eve

 
CREATE TEMPORARY TABLE wow.tmpCandidates
SELECT DISTINCT r.Candidate_ID
FROM wow.resume r
WHERE r.Section_ID = '1' 
  AND MATCH (r.Section_Value) AGAINST ('+peoplesoft' IN BOOLEAN
MODE);

INSERT IGNORE INTO wow.resume_erp (Candidate_ID, Section_ID,
Section_Value) 
SELECT SQL_CALC_FOUND_ROWS r.Candidate_ID, r.Section_ID,
r.Section_Value
FROM wow.tmpCandidates tc
INNER JOIN wow.resume r
on r.Candidate_ID = tc.Candidate_ID;

INSERT IGNORE INTO wow.candidate_erp (Candidate_ID, Vendor_ID,
Last_Name, First_Name, Middle_Initial, Condition_Type, Employer,
Country_ID, Visa_Status, Dt_Visa, MMDD_Birth, SSN, CSG_Comments,
Working, Available, Start_Date, Location, HoldOnPeriod, Relocation,
Tech_Ranking, Comm_Ranking, Availability, Cert_Comments, Dt_Submitted,
Def_Rate, Def_Rate_Unit, Other_Country, Currency_id,
Interview_Availability, Interview_Contact, US_Experience,
Location_Country)
SELECT SQL_CALC_FOUND_ROWS c.Candidate_ID, c.Vendor_ID, c.Last_Name,
c.First_Name, c.Middle_Initial, c.Condition_Type, c.Employer,
c.Country_ID, c.Visa_Status, c.Dt_Visa, c.MMDD_Birth, c.SSN,
c.CSG_Comments, c.Working, c.Available, c.Start_Date, c.Location,
c.HoldOnPeriod, c.Relocation, c.Tech_Ranking, c.Comm_Ranking,
c.Availability, c.Cert_Comments, c.Dt_Submitted, c.Def_Rate,
c.Def_Rate_Unit, c.Other_Country, c.Currency_id,
c.Interview_Availability, c.Interview_Contact, c.US_Experience,
c.Location_Country
FROM wow.tmpCandidates tc
INNER JOIN wow.candidate c
ON c.Candidate_ID = tc.Candidate_ID;


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]