Help with a query

Hi,

can anyone help me with a query?

Let's say I have two tables:
- books (id INT, name VARCHAR, publisher_id INT)
- publishers (id INT, name VARCHAR)

And I'd like to have a query to select and sort records in 'books' by
the publishers.name that corresponds with books.publisher_id

I'm a newbie, so excuse the newbie question.



Thanks

I forgot to add:
The important thing is that the fields have same names in both tables
and they can't be changed.

hjsb85@gmail.com wrote:
> I forgot to add:
> The important thing is that the fields have same names in both tables
> and they can't be changed.

To deal with duplicate field names, you simply need to prefix the name
of the table to the name of the field: books.id, publishers.id.

Cheers,
Nicholas Sherlock

--
http://www.sherlocksoftware.org

I did it with
SELECT * FROM books LEFT JOIN publishers ON publisher_id =
publishers.id

only since the tables have the same column names, now the array the
record is in doesn't have associative values for the duplicate names. I
instead get like:

array(8) {
[0]=>string(1) "3"
["id"]=> string(1) "1"
[1]=> string(11) "The Big Dork (Unabridged)"
["name"]=>string(11) "Penguin Books"
[2]=>string(1) "1"
["publisher_id"]=>string(1) "1"
[3]=>string(1) "1"
[4]=>string(11) "Penguin Books"
}

How do I fix the query so I assign different column names without
changing the table itself?


Thanks

On 7 Dec 2006 03:08:56 -0800, hjsb85@gmail.com wrote:

> Let's say I have two tables:
> - books (id INT, name VARCHAR, publisher_id INT)
> - publishers (id INT, name VARCHAR)
>
> And I'd like to have a query to select and sort
> records in 'books' by the publishers.name that
> corresponds with books.publisher_id
>
> I did it with
> SELECT * FROM books LEFT JOIN publishers
> ON publisher_id = publishers.id
>
> only since the tables have the same column names, now the array the
> record is in doesn't have associative values for the duplicate names. I
> instead get like:
>
> array(8) {
> [0]=>string(1) "3"
> ["id"]=> string(1) "1"
> [1]=> string(11) "The Big Dork (Unabridged)"
> ["name"]=>string(11) "Penguin Books"
> [2]=>string(1) "1"
> ["publisher_id"]=>string(1) "1"
> [3]=>string(1) "1"
> [4]=>string(11) "Penguin Books"
> }
>
> How do I fix the query so I assign different column names without
> changing the table itself?

SELECT
B.id AS book_id
,B.name AS book_name
,publisher_id
,P.name as publisher_name
FROM books AS B
LEFT JOIN publishers AS P
ON publisher_id = P.id
ORDER BY P.name;

(untested)
Note this will list all books, even when they don't have a
publisher, and it doesn't list publishers which didn't publish
any book.

>Thanks

YW
--
( Kees
)
c[_] Graduate school is where you learn to call a spade a leveraged
tactile-feedback geomass delivery system. (Martha Koester) (#190)

A bit complicated but I finally got it. Thanks :)

Kees Nuyt wrote:
> On 7 Dec 2006 03:08:56 -0800, hjsb85@gmail.com wrote:
>
> > Let's say I have two tables:
> > - books (id INT, name VARCHAR, publisher_id INT)
> > - publishers (id INT, name VARCHAR)
> >
> > And I'd like to have a query to select and sort
> > records in 'books' by the publishers.name that
> > corresponds with books.publisher_id
> >
> > I did it with
> > SELECT * FROM books LEFT JOIN publishers
> > ON publisher_id = publishers.id
> >
> > only since the tables have the same column names, now the array the
> > record is in doesn't have associative values for the duplicate names. I
> > instead get like:
> >
> > array(8) {
> > [0]=>string(1) "3"
> > ["id"]=> string(1) "1"
> > [1]=> string(11) "The Big Dork (Unabridged)"
> > ["name"]=>string(11) "Penguin Books"
> > [2]=>string(1) "1"
> > ["publisher_id"]=>string(1) "1"
> > [3]=>string(1) "1"
> > [4]=>string(11) "Penguin Books"
> > }
> >
> > How do I fix the query so I assign different column names without
> > changing the table itself?
>
> SELECT
> B.id AS book_id
> ,B.name AS book_name
> ,publisher_id
> ,P.name as publisher_name
> FROM books AS B
> LEFT JOIN publishers AS P
> ON publisher_id = P.id
> ORDER BY P.name;
>
> (untested)
> Note this will list all books, even when they don't have a
> publisher, and it doesn't list publishers which didn't publish
> any book.
>
> >Thanks
>
> YW
> --
> ( Kees
> )
> c[_] Graduate school is where you learn to call a spade a leveraged
> tactile-feedback geomass delivery system. (Martha Koester) (#190)