select statement help

[yoki]

Hi,

I have the following sql statement:

SELECT *
FROM Abstract_CoAuthors
INNER JOIN Abstract_Information ON Abstract_Information.Abstract_ID = Abstract_CoAuthors.Abstract_ID
INNER JOIN Co_Authors ON Co_Authors.Co_Author_ID = Abstract_CoAuthors.Co_Author_ID

which outputs the following:

|Abstract_ID| |Co_Author_ID| |Abstract_ID| |Title| |Topic_ID| |Other_Topic| |Presentation_Type_ID| |Date_Received| |Presenting_Author_ID| |Doc_ID| |Co_Author_ID| |Last_Name| |First_Name| |Institution| |City| |Country|
|1| |1| |1| |THIS IS A TEST| |1| |1| |2005-08-10| |1| |1| |1| |Flint| |Keith| |Cadence Healthcare| |Montreal| |Canada|
|1| |2| |1| |THIS IS A TEST| |1| |1| |2005-08-10| |1| |1| |2| |Howlett| |Liam| |Cadence Healthcare| |London| |United Kingdom|

I would like to only have the Last_Name, First_Name, Institution, City, Country and not the whole thing...

How would I do this? Sorry I'm fairly new with JOIN statements...

[yoki]

This is what I tried but with no result:

SELECT Abstract_CoAuthors.Abstract_ID , Abstract_CoAuthors.Co_Author_ID , Co_Authors.Last_Name
FROM Abstract_CoAuthors , Co_Authors
INNER JOIN Abstract_Information ON Abstract_Information.Abstract_ID = Abstract_CoAuthors.Abstract_ID
INNER JOIN Co_Authors ON Co_Authors.Co_Author_ID = Abstract_CoAuthors.Co_Author_ID
LIMIT 0 , 30

MySQL said:

#1066 - Not unique table/alias: 'Co_Authors'

[Weedpacket]

Do you really need all those backticks? I doubt that "Abstract_CoAuthors" is a reserved word in MySQL....

I see that you're using Co_Authors twice; as the error message suggests, you should use aliases so that MySQL can tell which use is which.

SELECT Abstract_CoAuthors.Abstract_ID , Abstract_CoAuthors.Co_Author_ID , co_author1.Last_Name
FROM Abstract_CoAuthors , Co_Authors as co_author1
INNER JOIN Abstract_Information ON Abstract_Information.Abstract_ID = Abstract_CoAuthors.Abstract_ID
INNER JOIN Co_Authors as co_author2 ON co_author2.Co_Author_ID = Abstract_CoAuthors.Co_Author_ID
LIMIT 0 , 30

Unless there's a more sensible choice of names than "co_author1" and "co_author2".

That would fix the alias problem - whether it gives you the result you want is another matter.

As you've already found, to get just the columns you want, refer to them by name.