I'm trying to select data from an existing database (not designed by
me), but the data that I need to extract is stored within several tables.

I'm still at a relatively novice level in terms of my MySQL experience,
and am not entirely sure how to proceed. It will actually be PHP (using
mysqli_* functions) that will be querying the database (caused by a user
interacting with a form designed to let them update their own data), and
although I think that I could probably extract the needed data through a
number of successive simpler queries, I have a nagging worry that I
ought to be concerned about atomicity (and that making a series of
queries could therefore potentially be risky, although the form is the
only way in normal use that the data is intended to be updated). (?)

In short, what I need to do is get some user "profile information" for a
given user where each user is a member of exactly one 'staff group' and
each staff group has different profile information fields available to
it (a subset of the fields available).

To get the data I need, I have to carry out the following steps:

1. I know the person's username initially, but not their id or
staff_group, which I need to determine in order to subsequently find
which data applies to the user (which is connected by these fields).
These are obtained from the table 'person':

+-------------------------------------+--------------+------+-----+
(I've omitted other fields from this table description, which are not of
use here)

SELECT id, staff_group_id FROM person where username=
[username value from a PHP variable I already have];



2. Now knowing the staff_group_id for the user, I need to find which
profile fields (profile_field_id) are available to members of that staff
group, from the 'profile_template' table:

+------------------+---------+------+-----+---------+----------------+

This would be something like:

SELECT position, profile_field_id from profile_template WHERE
staff_group_id =
[the staff group id of my user, that I'd just already looked up] ;


At this point I get stuck: I have a feeling a JOIN of some kind will be
necessary, but I'm afraid that I haven't quite had a lightbulb moment
regarding joins and don't fully understand them properly.

Is it possible to combine the two queries above into one combined query?
(I would be expecting to obtain several profile_field_id values for the
particular staff group.)



3. Unfortunately, there's more. The profile_field_id above is an index
into another table (profile_field) which contains what is essentially
metadata about each profile field (ie, name, type, viewing order).

So, for *each* profile_field_id found from above, I would need all data
for (only) the profile fields that match the found ids (still with me?).

profile_template.profile_field_id matches profile_field.id

(profile_field table described below)

+--------------+--------------+------+-----+---------+----------------+


4. And even more (this is the final constraint!). Having obtained the
data so far, I finally need to obtain the "actual" data for the profile
fields which actually apply to the specific user. These are stored in
yet another table (profile_record):

+--------------------+---------+------+-----+---------+----------------+

Here, person_id is the id of the person (as found in part 1), and
profile_field_id is the id (ie, type) of the field as described in the
profile_field table.


(And then finally I can hopefully start assigning the data to
appropriate PHP variables and start creating the output for my form..)


The (large and vague) question, of course, is: How can I do it?

For safety, do I need to somehow make this one atomic action?
Or can I safely do this as a series of queries, with the data obtained
at each stage being transferred into and out of PHP variables as
appropropriate (which would be much easier, I think!)

How on earth can I make such a query work? (My head's been spinning just
trying to decode the database setup so far, to work out what data I need)


Many thanks if anybody can offer any useful tips or advice (I realise
that I do nevertheless need to go away and read up some more on
semi-advanced SQL until it starts to make sense to me!).


David.

El 26/10/2010 17:23, David escribió/wrote:
Your reasoning basically follows the logic of the tables joins you need
to perform and in the same order

1. You start with a base table (FROM)
2. You add tables with the appropriate JOIN statement:
2.1 You write the expression for the match in the ON clause
2.2 You prepend a modifier to decide what to do with rows that don't
have a match:
- LEFT: show all rows from the table in left
- RIGHT: show all rows from the table in right
- INNER: only show matching rows
3. Rows that match more than one row get duplicated to hold all matches.

E.g.:

left_id
=======
10
20
40

right_id
========
10
20
30


LEFT JOIN:

left_id right_id
======= ========
10 10
20 20
40 NULL

RIGHT JOIN:

left_id right_id
======= ========
10 10
20 20
NULL 30

INNER JOIN:

left_id right_id
======= ========
10 10
20 20

So your query is:

SELECT pe.id AS person_id, pt.position, pt.profile_field_id
FROM person pe
LEFT JOIN profile_template pt ON pe.staff_group_id=pt.staff_group_id

..... although, while testing, it often helps to see everything:

SELECT *
FROM person pe
LEFT JOIN profile_template pt ON pe.staff_group_id=pt.staff_group_id


You already have it:

SELECT pe.id AS person_id,
pt.position AS profile_template_position,
pf.name, pf.content_type, pf.position AS profile_field_position
FROM person pe
LEFT JOIN profile_template pt ON pe.staff_group_id=pt.staff_group_id
LEFT JOIN profile_field pf ON pt.profile_field_i=pf.id


Any difficulty so far?


Well, separate queries are in no way guaranteed to be atomic. However,
in practice and in most cases it tends to not matter. Table locking and
transaction isolation level are advanced concepts that shouldn't worry
you yet (and if they do, MySQL is probably the wrong DBMS). Learn first
SQL and basic DB design.


--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--

In article <ia6rp8$lm3$1 (AT) news (DOT) eternal-september.org>, David <david (AT) 55952163-3189045 (DOT) bogus.domain.invalid> wrote:
OK, no big deal.
[...]
Certainly. Something along these lines ought to work:

SELECT position, profile_field_id
FROM person INNER JOIN profile_template
ON person.staff_group_id = profile_template.staff_group_id
WHERE username = [username value from your PHP variable]

... INNER JOIN profile_field ON profile_field_id = id
Examples above; the remainder is left to the student as an exercise.
You really should. Whether you'll have a problem or not depends on how
frequently these tables are updated; my guess is, not often enough to cause
you any trouble. But the more important thing is that you need to learn to
write JOINs.

No, actually, that will be *much* more work than writing one properly
structured multi-table query.
RTFM. :-)

[Apologies in advance for the large amount of re-quoting, but I felt it
would be helpful to keep the database descriptions in the message..]

On 26/10/10 16:48, Álvaro G. Vicario wrote:
Thanks, glad to hear that I was at least _starting_ to think along the
right lines.. ;-)


*Muchas* gracias, Álvaro: I think that's the most succinct description
(along with your example, which I've just snipped ;-/ ) of joins that
I've ever seen! I don't think my lightbulb is fully lit yet, but that's
helped a great deal and it's at least starting to flicker.. :-)


So far, so good [1]. Just for clarification: in the join, is the 'left'
table always the one after the "FROM" and the 'right' table the one
after the "JOIN" command?

And thanks for the free bonus tip that you can make aliases for table
names in queries, I didn't know that! It certainly saves on typing when
testing queries! (I think I'd still always spell them out in full in my
final program code to avoid confusing myself if I need to go back and
look at it again later.)

[1] It makes so much more sense when you break it down one step at a
time: I think part of my problem was trying to go straight from never
having used JOINs to trying to do things with lots of tables at once and
confusing myself!


OK, I _think_ I'm still with you so far..

So, to join additional tables to the query, you basically just keep on
adding your requested SELECT fields and add a new JOIN on the end?

Is the way I should think of it that the second join is joining to an
"imaginary table" consisting of the result of the first query/join (and
so on, and so on, for subsequent joins)?


Your explanations so far have been *very* helpful, and I _really_
thought that I'd just about got the hang of it (after working through
and trying to understand the previous queries), but my attempt at the
final query doesn't quite seem to have worked..

(Note that I've changed some of the aliases/AS names slightly, as I was
starting to confuse myself again with too many p's, and have removed a
field from the select that I realised I didn't actually need.)

My attempted query was:


SELECT pers.id AS p_id,

pt.position AS prfl_tpl_posn,

pf.id AS pf_id,
pf.name, pf.content_type AS ctype,

content, pr.is_visible_on_site AS fvis

FROM person pers
LEFT JOIN profile_template pt ON pers.staff_group_id=pt.staff_group_id

LEFT JOIN profile_field pf ON pt.profile_field_id=pf.id

LEFT JOIN profile_record pr ON pers.id=pr.person_id

WHERE username = 'somebody';


And this resulted in:

+------+---------------+-------+-----------+----------+-----------+------+


Not quite what I was after, as you can see, so I've obviously made a mistake in
my join somewhere. I assumed that matching on my user ID (pers.id, as already
found) against the records associated with my id in profile_record would bring
up the results, but it looks as though because I already have (at the previous
stage of the query) *several* found records containing my user ID, it's sort of
iterated through profile_record multiple times?


(Note that my test user (me!) had only filled in the "Profile 4" [2] field
previously, which is why 'content' is empty for the other profile fields.

[2] Not the real field name, I hasten to add, I've just changed the data so that
it all fits on screen here.)


Could I perhaps trouble you for another hint as to where I might be going wrong?


Thanks again,


David.

On 27/10/10 01:34, Doug Miller wrote:
Thanks also for your help, Doug.

Interesting that you have used INNER JOIN and Álvaro used LEFT JOIN.

I'm trying to get my head round whether one versus the other would make any
difference in my particular query..


Indeed. And I did do a fair amount reading and searching before asking here.

Unfortunately, the problem with FMs can often be determining which part it is
that you read to R! I find the MySQL manual to be fairly good (although for some
reason, not quite as clear to me as the PHP manual, for example), but I wasn't
really sure in the first place whether JOINs were where I should be looking (or
whether the problem should be approached in a different way), and I also didn't
find the JOIN page of the MySQL manual to be particularly easy to follow, I'm
afraid.


David.

On 29/10/10 11:28, David wrote:

OK, after some head-scratching, I've come up with..


SELECT pers.id AS p_id,

pt.position AS prfl_tpl_posn,

pf.id AS pf_id,
pf.name, pf.content_type AS ctype,

content, pr.is_visible_on_site AS fvis

FROM person pers
LEFT JOIN profile_template pt ON pers.staff_group_id=pt.staff_group_id

LEFT JOIN profile_field pf ON pt.profile_field_id=pf.id

LEFT JOIN profile_record pr ON pers.id=pr.person_id AND pf.id=pr.profile_field_id

WHERE username = 'somebody';


Adding an AND clause to the join (to match up on the profile_field_id's as well)
seems to have done the trick, I'm just not certain whether that's the right
thing, or whether I just happen to have got the intended result (this time) by
chance..? (Not a good thing!)

+------+---------------+-------+-----------+----------+-----------+------+

If this is the right solution, should I really ought to have brackets around the
whole ON clause? (I'm not sure how SQL does its parsing order)


Hmm, I'm afraid I clearly really need to find a tutorial or book that explains
JOINs to me in a way that I can clearly visualise and understand...


David.

On Oct 29, 1:36*pm, David
<da... (AT) 55952163-3189045 (DOT) bogus.domain.invalid> wrote:
Looks to me like you've just about got it.

You should always qualify column identifiers, so that it's clear to
people like us (and yourself six months down the line) where a given
column is coming from. So, "WHERE username = 'somebody'" becomes
"WHERE pers.username = 'somebody'"

El 29/10/2010 15:36, David escribió/wrote:

The JOIN clause basically accepts any expression that's valid in a WHERE
clause so there's nothing weird in adding an AND if the join requires a
match in more than one column. I'm not sure about your exact query (I'm
too sleepy to go through your data again) but the idea is exactly that.
Each join you add refines the result of the previous set of joins.


I see no purpose in that. Operator precedence can hardly be an issue
when the only alternative is a syntax error:

SELECT *
FROM left_table a
INNER JOIN right_table b (ON a.foo=b.foo) AND a.bar=b.bar

---------------------------
Error
---------------------------
SQL Error (1064): You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to
use near '(ON a.foo=b.foo) AND a.bar=b.bar' at line 3
---------------------------
OK
---------------------------



SELECT *
FROM left_table a
(INNER JOIN right_table b ON a.foo=b.foo) AND a.bar=b.bar

---------------------------
Error
---------------------------
SQL Error (1064): You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to
use near '(INNER JOIN right_table b ON a.foo=b.foo) AND a.bar=b.bar' at
line 3
---------------------------
OK
---------------------------



This may help:

http://www.codinghorror.com/blog/200...sql-joins.html


--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--

It may also hinder!

[Sorry for the delay in replying to this..]

On 02/11/10 12:59, Álvaro G. Vicario wrote:

Thanks, that's what I wasn't sure about.. Thinking of a JOIN in similar
terms to a WHERE does help.

Thanks again, this is slowly starting to make ever so slightly more sense to
me now!


For the possibility of brackets (quoting my example above again), I was
thinking along the lines of:

SELECT pers.id AS p_id,
pt.position AS prfl_tpl_posn,
pf.id AS pf_id,
pf.name, pf.content_type AS ctype,
content, pr.is_visible_on_site AS fvis
FROM person pers
LEFT JOIN profile_template pt ON pers.staff_group_id=pt.staff_group_id
LEFT JOIN profile_field pf ON pt.profile_field_id=pf.id
LEFT JOIN profile_record pr ON (pers.id=pr.person_id AND
pf.id=pr.profile_field_id)
WHERE username = 'somebody';

...with the brackets as shown above around all of that 'ON' clause to make it
clear that the AND does also belong to that ON? But I guess that SQL must
assume that everything after '..JOIN' automatically belongs to that JOIN
until the next keyword that syntactically cannot (eg, the 'WHERE' in the
example above)?

(Sorry if my interest in brackets seems odd: I'm the kind of person who
finds it helpful when writing code in other languages (eg, PHP) to sometimes
add 'strictly unnecessary' brackets where it helps me to understand and read
my code. Even if a language's rules of parsing means that it will Do The
Right Thing without the brackets, I find it sometimes helpful to make things
explicit, especially where it helps to make it visually clear in my own
mind.. I was wondering whether SQL worked in the same way..)



Thanks, that looks useful - although there's then a lot of arguing in the
comments that it may not be entirely correct! ;-(


David.