INNER JOIN confusion

Hi,

I'm trying to do an INNER JOIN to extract a matching fields from another table if sucha  match exists. I'm getting asyntax error with this SQL statement:

SELECT *
FROM horses
INNER JOIN pedigree WHERE horses.HorseID = pedigree.HorseID
SORT BY horses.Horse_name DESC

...telling me my FROM clause has a syntax error.

I'm working in Dreamweaver recordsets, but I believe this is a straight-forward SQL issue.

What am I doing wrong?

Thanks
LVL 1
billium99Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
ok, use the LEFT JOIN then:

SELECT h.horseid, h.horsename ....
FROM horses h
LEFT JOIN pedigree p
   ON h.HorseID = p.HorseID
 ORDER BY h.Horse_name DESC
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
SELECT *
FROM horses
INNER JOIN pedigree ON horses.HorseID = pedigree.HorseID
 ORDER BY horses.Horse_name DESC
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
note: you should avoid the SELECT *, and instead use table alias names, and only list those columns that you really need returned:

SELECT h.horseid, h.horsename ....
FROM horses h
INNER JOIN pedigree p
   ON h.HorseID = p.HorseID
 ORDER BY h.Horse_name DESC

0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
billium99Author Commented:
Well that shatters all previous records for fast response!

Unfortunately I was still getting a syntax error. I'm think I resolved that but now I'm getting a type mismatch error:

SELECT horses.horseID, horses.Horse_name, horses.Price, horses.Featured_yesno
FROM horses INNER JOIN pedigree ON horses.HorseID = pedigree.HorseID
ORDER BY horses.Horse_name DESC


Does this mean my two HorseID fields are not the same type of field?

Thanks

Bill
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Does this mean my two HorseID fields are not the same type of field?
yes, highly probable.
0
 
billium99Author Commented:
OK - so error is gone, but it's not returning any records. I know there are three records in the db - if I do a straight select all recordset:

SELECT *
FROM horses
ORDER BY horses.Horse_name DESC

They come right up, but with the INNER JOIN, nothing is getting returned - right now there should be no matches for the JOIN, because the pedigree table is empty, but I want to select all the Horse records regardless of whether there is a matching record in the pedigree table. How do i do this?

Thanks
0
 
Jason C. LevineNo oneCommented:
>> Well that shatters all previous records for fast response!

Yes, angelIII is impressive :)
0
 
billium99Author Commented:
D'oh! LEFT, instead of INNER

<sigh> I'll never learn all of this stuff - but that's one I should've known from one of my other sites.

Thanks Agellll!

Bill
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.