Access > mySQL SQL....

Report · Aug 01, 2006

Hope someone can help with this - I've basically created the following tables / queries as part of my first mySQL project :

Candidates
---------------
Candidate_ID (autonumber)
Name (text)
...

1, Iain
2, Fi
3, Rob

Vacancies
--------------
Vacancy_ID (autonumber)
Vacancy (text)
...

1, Cartographer
2, Gardener
3, Occupational therapist
4, Web designer
5, Recruitment manager

Profiles
-----------
Profile_ID (autonumber)
Profile (text)
...

1, Map making
2, Web design
3, Gardening
4, Hand therapy
5, Recruitment
6, Aviation
7, Sport
8, Travel

VacancyProfiles
----------------------
Vacancy_ID (number)
Profile_ID (number)
...

1,1
1,2
4,2
2,3
3,4
5,5
5,6

CandidateProfiles
--------------------
Candidate_ID (number)
Vacancy_ID (number)
...

1,1
1,2
1,7
1,8
2,3
2,4
2,8
3,5
3,6
3,7

and from there created two queries

CandidatesQuery
--------------------

SELECT Candidates.Candidate_ID, Candidates.Name, Profiles.Profile_ID, Profiles.Profile
FROM Profiles INNER JOIN (Candidates INNER JOIN CandidateProfiles ON Candidates.Candidate_ID = CandidateProfiles.Candidate_ID) ON Profiles.Profile_ID = CandidateProfiles.ProfileID;

1, Iain, 1, Map making
1, Iain, 2, Web design
1, Iain, 7, Sport
1, Iain, 8, Travel
2, Fi, 3, Gardening
2, Fi, 4, Hand therapy
2, Fi, 8, Travel
3, Rob, 5, Recruitment
3, Rob, 6, Aviation
3, Rob, 7, Sport

and

Vacancies_Query
-------------------

SELECT Vacancies.Vacancy_ID, Vacancies.Vacancy, Profiles.Profile_ID, Profiles.Profile
FROM Profiles INNER JOIN (Vacancies INNER JOIN VacancyProfiles ON Vacancies.Vacancy_ID = VacancyProfiles.VacancyID) ON Profiles.Profile_ID = VacancyProfiles.ProfileID;
...

1, Cartographer, 1, Map making
1, Cartographer, 2, Web design
2, Gardener, 3, Gardening
3, Occupational therapist, 4, Hand therapy
4, Web designer, 2, Web design
5, Recruitment manager, 5, Recruitment
5, Recruitment manager, 6, Aviation

Trouble is, I come from an Access background, and I think the syntax of mySQL SQL is different to Access SQL - in the case here for the INNER JOINs - could anyone help out with the syntax for the queries above in mySQL friendly SQL, as I'm a bit unsure of what it should be.

Cheers,
Iain

Report · Aug 01, 2006

Iain71 wrote:
> SELECT Candidates.Candidate_ID, Candidates.Name, Profiles.Profile_ID,
> Profiles.Profile
> FROM Profiles INNER JOIN (Candidates INNER JOIN CandidateProfiles ON
> Candidates.Candidate_ID = CandidateProfiles.Candidate_ID) ON
> Profiles.Profile_ID = CandidateProfiles.ProfileID;

Haven't studied your tables in detail, but this translates to this in
MySQLese:

SELECT Candidates.Candidate_ID, Candidates.Name, Profiles.Profile_ID,
Profiles.Profile
FROM Profiles, Candidates, CandidateProfiles
WHERE Candidates.Candidate_ID = CandidateProfiles.Candidate_ID
AND Profiles.Profile_ID = CandidateProfiles.ProfileID

> SELECT Vacancies.Vacancy_ID, Vacancies.Vacancy, Profiles.Profile_ID,
> Profiles.Profile
> FROM Profiles INNER JOIN (Vacancies INNER JOIN VacancyProfiles ON
> Vacancies.Vacancy_ID = VacancyProfiles.VacancyID) ON Profiles.Profile_ID =
> VacancyProfiles.ProfileID;

SELECT Vacancies.Vacancy_ID, Vacancies.Vacancy, Profiles.Profile_ID,
Profiles.Profile
FROM Profiles, Vacancies, VacancyProfiles
WHERE Vacancies.Vacancy_ID = VacancyProfiles.VacancyID
AND Profiles.Profile_ID = VacancyProfiles.ProfileID

--
David Powers
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
Author, "Foundation PHP 5 for Flash" (friends of ED)
http://foundationphp.com/

Report · Aug 02, 2006

Thanks David - I think I prefer the mySQL version.

I did try it with the INNER JOIN syntax, and have a page here that seems to work - if you click on any vacancy it brings up a detail page with matching 'profiles' :

http://www.searchtechuk.com/database/vacancies2.php

The last trick (for displaying data at least - still not sure about adding/editing records to related tables simultaneously, as I'm used to doing in Access - see other thread!) would be to click on any Vacancy and bring up a list of matching Candidates - would it simply be an amalgam of the above - so something like :

SELECT *
FROM Vacancies, Candidates, CandidateProfiles, VacancyProfiles, Profiles
WHERE Vacancies.VacancyID=VacancyProfiles.VacancyID
AND Profiles.ProfileID=VacancyProfiles.ProfileID
AND Candidates.CandidateID=CandidateProfiles.CandidatesID
AND Profiles.ProfileID=CandidateProfiles.ProfileID

Cheers,
Iain

Report · Aug 02, 2006

Iain71 wrote:
> SELECT *
> FROM Vacancies, Candidates, CandidateProfiles, VacancyProfiles, Profiles
> WHERE Vacancies.VacancyID=VacancyProfiles.VacancyID
> AND Profiles.ProfileID=VacancyProfiles.ProfileID
> AND Candidates.CandidateID=CandidateProfiles.CandidatesID
> AND Profiles.ProfileID=CandidateProfiles.ProfileID

Yes. The only proviso is that there must be a match in every column. If
you have columns that don't contain matches, you then need to use the
LEFT JOIN syntax.

You can find the gory details of MySQL join syntax here:

http://mysql.com/doc/refman/5.0/en/join.html

--
David Powers
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
Author, "Foundation PHP 5 for Flash" (friends of ED)
http://foundationphp.com/

Report · Aug 02, 2006

Thanks David.

'gory details' is about right!

I though this mySQL lark was supposed to be pretty straightforward! :D

Still, I think I'm getting on OK for my first crack at it, with my three main tables, plus the two interlinking tables and it all working as it should so far - hopefully from now on it's not going to get too much trickier in to design - just a case of getting the right SQL syntax.

Iain

Report · Aug 02, 2006

Iain71 wrote:
> I though this mySQL lark was supposed to be pretty straightforward! :D

Just because the MySQL Community Edition is free doesn't mean that it's
a "simple" database. It has a lot of sophisticated features, and is used
in a lot of industrial applications.

Still, I find it a lot easier to work with than Access. I never really
got to grips with Access, whereas I was up and running in no time with
MySQL. I've been using it for several years now, and wouldn't turn back.

Give it a little more time, and I'm sure you'll find it pretty easy.

--
David Powers
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
Author, "Foundation PHP 5 for Flash" (friends of ED)
http://foundationphp.com/

Report · Aug 02, 2006

Absolutely - I've been very impressed at how quickly I've got the basics up and running using mySQL - I had a mare getting an Access app up and running on a local network at my *real* job - not helped by the fact that my IT manager didn't really know about setting up IIS(ISS?), and nor did I, and had to figure it all out.

The setting a connection to mySQL just seemed to have fewer hoops to jump through. I never got as far as adding/editing or deleting records on that project, so it's remained read only (I'm about the only person who really updates the databases and at the moment continue to do so using Access).

I had a quick look having gotten this far with mySQL, but unfortunately ran into errors straight away, so I've left it until I've got more time to look at it properly.

But this is a great project for me to really get stuck into a proper *live* database driven app. Having done this and a few other paid jobs recently, it might be time to update my own little website next, and maybe even investigate some marketing!

Iain