(mysql) dreamweaver subquery error

I have this query working perfectly in phpmyadmin, it even displays correct results in dreamweaver cs4 testing window (I get to see the data). The problem is that as soon as I OK the recordset (duly tested) I get an erro in the bindings window.


The recordset binding error is:

 MySQL 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 '' at line 2

My query is:
SELECT distribution.evenement_FK, distribution.S, personnes.fullname, titres.titre, 
(Select titres.titre FROM titres WHERE distribution.titre_sec = titres.titre_id) AS 'Titre secondaire'
FROM distribution, personnes, titres
WHERE distribution.pers_id = personnes.pers_id AND distribution.titre_id = titres.titre_id

Open in new window

mhrob3Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

IanThCommented:
You will get a MySQL query syntax error number 1064 when you incorrectly use a reserved word in your query such as "when" or "order".
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
in mysql, use ` and not ' for identifiers.

I would write your query like this:

SELECT d.evenement_FK, d.S, p.fullname, t.titre, t2.titre AS `Titre secondaire`
FROM distribution d
JOIN personnes p
  ON d.pers_id = p.pers_id 
JOIN titres t
  ON d.titre_id = t.titre_id
LEFT JOIN titres t2
  ON d.titre_sec = t2.titre_id

Open in new window

0
mhrob3Author Commented:
thanks for the fast reply, but i'm sorry to say that although I get a positive result in phpmyadmin (same as I got with my own query in phpmyadmin), I still get an error in dreamweaver when I paste your code in dreamweaver's query window, but this time the error message is different, and I can't even get the data preview window to open with your query. That baffles me, to say the least, as all other queries I have  tried so far seemed to work just fine... They probably were less involved though.

The message I get this time around is this one:

 MySQL 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 d.pers_id = p.pers_id  JOIN titres t   ON d.titre_id = t.titre_id LEFT JO' at line 1

Thanks for your insight on this... I really appreciate.


0
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
what version of mysql?
0
mhrob3Author Commented:
Aye. Finally got it to work using ADDT Query Builder (the visual help a lot, now that I've struggled for a day). This is the query I finally ended up with (see below), but I had to cut out the recordset, clean up the code in DW, and finally pasted back the record set in a clean php document...

DW leaves in all kind of code behind from prior query efforts and that messes up things in a grand fashion... Still annoyed by that, but learning is not easy... I had tried Query Builder yesterday but made a mess of it all by myself. Today though, after much hair pulling, I alreafy understand much more of what is going on, and my confidence is improving

Now it works perfectly.Thanks for your support angelll.

Oh and QB created a fictitious titres_0 table to accomodate the fact that 2 separate colums whre calling on the same table column for a varchar value (for a main title and a secondary title). Mmm...
SELECT distribution.evenement_FK AS evenement, 
titres.titre AS titreprincipal, 
titres_0.titre AS titresecondaire, 
personnes.fullname AS artiste
FROM (((distribution 
LEFT JOIN titres ON titres.titre_id=distribution.titre_id) 
LEFT JOIN titres AS titres_0 ON titres_0.titre_id=distribution.titre_sec) 
LEFT JOIN personnes ON personnes.pers_id=distribution.pers_id) 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Oh and QB created a fictitious titres_0 table to accomodate the fact that 2 separate c
if you look at my suggestion, you will notice that I did the same... using t and t2 as table aliases.
this will make it 100% clear for both the human reader and the sql engine from which table the column value has to be taken from.
0
mhrob3Author Commented:
angelIII:
what version of mysq
-------------------------------
Sorry, I never saw your question before the above posting

Version du client MySQL: 5.0.41
phpmyadmin:  2.11.7.1
DW CS4

So what do you think of the outcome? 3 LEFT JOINS  in the FROM part of the query. I am a little shocked myself, eheh. I will try to do some more of these as I go on learning, but do you see why this type of joins seem to agree mor with DW? Thanks again for your input...
0
mhrob3Author Commented:
angelIII:
>Oh and QB created a fictitious titres_0 table to accomodate the fact that 2 separate c

if you look at my suggestion, you will notice that I did the same... using t and t2 as table aliases.
this will make it 100% clear for both the human reader and the sql engine from which table the column value has to be taken from.
------------------------------------------------

Thanks for your comment: I had seen such aliases in forums before but could'nt quite understand why they where used. Very useful indeed. This is such a big learning curve for me, but I really am feeling the awesome power of databases the more I dive into it. Still a little bit overwhelmed though, eheh.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you will need to be aware of the difference of LEFT JOIN vs INNER JOIN.
normally, you don't need table_a  LEFT JOIN table_b , as it will return rows from table_a even if there are no matching records in table_b.
0
mhrob3Author Commented:
I thinf I just got caught up in the way the book I'm reading introduces joins, doing a good job with natural and inner joins, but presenting subqueries before adressing other operators (?) like left and right. I find my experience with subqueries has been a little painful for now, although they worked ok in phpmyadmin but stumped me completely in DW.

I think I'll skip subqueries for now and experiment more with left and right joins (I'm already more familiar with those and find them easier to grasp for now).

Thanks for your advice.
0
mhrob3Author Commented:
I hope I closed the subject properly... I'm still feeling a bit ackward as this is my first question.

Thanks to all.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.