MySQL For Dummies Book Giving me Headache

[Bazrazmataz]

Hi

Im trying to learn php and mysql from the dummies book but im running into a few problems. I dunno if anyone has got it but if u have its pg 83 im stuck on mainly.

This code wont work :
SELECT * FROM Pet OUTER JOIN Color Using (Pet.petName=Color.petName)

I have 2 tables Pet (containing petName & petType) Color (petName & petColor)

My second problem is on pg 79 involving with sorting data, the code that wont work is:

SELECT * FROM Member ORDER BY DESC lastName

This works fine without the DESC in but not with it in!

[raf]

Is that textbook sql??

SELECT * FROM Pet OUTER JOIN Color Using (Pet.petName=Color.petName)
should be
SELECT * FROM Pet OUTER JOIN Color ON Pet.petName=Color.petName
==> don't use the *, always type out the columns you actualy use, even if you use all columns. If you later on add columns to that table, you'd have to large recordsets --> you need to keep your recordsets as small as possible
So a better statement would be
SELECT Pet.var1, Pet.var2, Color.var1, Color.var2 FROM Pet OUTER JOIN Color ON Pet.petName=Color.petName
(where .var1 is a the name of a column from that table that you need in your code)

SELECT * FROM Member ORDER BY DESC lastName
should be
SELECT * FROM Member ORDER BY lastName DESC
==> same comment about the *

[Bazrazmataz]

Its the book entitled PHP & MySQL For Dummies, ive emailed the publishers but have recived no reply. Ill give what u said a go on my home pc.

Cheers

[Bazrazmataz]

One thing is this a typo in the book ? or have conventions changed since this book was published?

[raf]

I don't see how this can be a typo.

As far as i know; ordering is in all SQL-dialetcs and versions

ORDER BY variablename ASC
or
ORDER BY variablename DESC

the join syntaxforms that i know are

select table1.blabla, table2.moreblabla from table1 INNER JOIN table2 ON table1.fieldname=table2.fieldname
--> the more modern way

or the more classic way, also known as generic joins
select table1.blabla, table2.moreblabla from table1, table2 WHERE table1.fieldname=table2.fieldname

The USING() form is documented inside the mysql-documentzation on joining
http://www.mysql.com/doc/en/JOIN.html
but i never used it or sen it inside code i read. (i try to maximaly use syntax that is truly universal --> so i need to remember less. I always use the modern form since it works in all situations on all db-formats)
But your query should then have been

SELECT * FROM Pet OUTER JOIN Color Using (petName)

so it's incorrect in the book anyway

[Bazrazmataz]

Well i poped home and tested your suggestions the desc line worked but i still have had no joy with the other line.

This is really bugging me as ive asked lots of people for help on this and nothing seems to work and the people at dummies.com wont get back to me.
This link show a screen dump of my command prompt and the errors im getting plus a little info about the two tables.

http://www.razmataz-designs.co.uk/dump.jpg

Please Someone Help me Solve This I cant advance in my book leaving behind errors.!!

[raf]

You need to have

SELECT * FROM Pet LEFT OUTER JOIN Color ON Pet.petName=Color.petName
or
SELECT * FROM Pet RIGHT OUTER JOIN Color ON Pet.petName=Color.petName

(the word OUTER is optional)
and you best use all lowercase letter for the tables. So that's

SELECT * FROM pet LEFT JOIN color ON pet.petName=color.petName


I'd need more info to tell you if you need a left or right join. With a left join, all records from pet will be included. If no matching row in color is found, then the celss from color will be Null. With a right join it's the oposit

[Bazrazmataz]

Thankyou Raf your code worked and at last i can move on with my book!!!

Cheers

[raf]

You're welcome. Happy coding :thumbsup: