?
Solved

INNER JOIN confusion

Posted on 2007-10-21
8
Medium Priority
?
381 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:billium99
  • 4
  • 3
8 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20119144
SELECT *
FROM horses
INNER JOIN pedigree ON horses.HorseID = pedigree.HorseID
 ORDER BY horses.Horse_name DESC
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20119146
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
 
LVL 1

Author Comment

by:billium99
ID: 20119189
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20119194
>Does this mean my two HorseID fields are not the same type of field?
yes, highly probable.
0
 
LVL 1

Author Comment

by:billium99
ID: 20119234
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 20119242
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
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 20119341
>> Well that shatters all previous records for fast response!

Yes, angelIII is impressive :)
0
 
LVL 1

Author Comment

by:billium99
ID: 20119391
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question