[skatingrocker17]

I'm curious how to select columns from multiple tables. I have two tables, one called ordered and one called books.

I have to list the names and order states of all of the books ordered from customer number 1004.

Names is in a separate table from date and customer number.

I know I can do
select * from books, orders where custid = 1004;

But it doesn't seem to bring the results I'm looking for, I looked at the text file I read the data in from and customer number 1004 only ordered 6 total books and this lists 2628.

I'm looking to do something like select name from books, date, custID from orders where custid = 1004;

But I know the above is syntactically incorrect. Is there a way I can just certain table columns from each table?

Thanks

[codeprada]

Hey, you need to utilize JOINs. From the sounds of it those two tables should be joined together on either the book's name or the books table's primary key. Quick example

SELECT `col1`, `col2` FROM `table1` INNER JOIN `table2` ON `table1`.`column` = `table2`.`column` WHERE `col1` = 'Random Value'

[skatingrocker17]

codeprada, on 16 April 2012 - 06:58 PM, said:

Hey, you need to utilize JOINs. From the sounds of it those two tables should be joined together on either the book's name or the books table's primary key. Quick example

SELECT `col1`, `col2` FROM `table1` INNER JOIN `table2` ON `table1`.`column` = `table2`.`column` WHERE `col1` = 'Random Value'

I'm finding that somewhat hard to follow.

What are the periods? Like a dot operator?

[codeprada]

They're used to reference the column name of a specific table. If you have two tables with an identical column then you'll have to place the table name plus the dot to let MySQL know which table exactly the column is in.

[skatingrocker17]

codeprada, on 16 April 2012 - 07:49 PM, said:

They're used to reference the column name of a specific table. If you have two tables with an identical column then you'll have to place the table name plus the dot to let MySQL know which table exactly the column is in.

I don't think I need to INNER JOIN because the columns from the separate tables are all different. I only need 1 column from books and two columns from orders.

[e_i_pi]

How do the tables books and orders relate to one another? Are there any columns in either table that can be compared in any way? For instance, orders might have a column named book_id.

Better still, what are all the columns of the two tables?

[skatingrocker17]

e_i_pi, on 17 April 2012 - 02:14 PM, said:

How do the tables books and orders relate to one another? Are there any columns in either table that can be compared in any way? For instance, orders might have a column named book_id.

Better still, what are all the columns of the two tables?

books has bookid, order has orderid. The table orders has a column called bid which is the bookid from books.

[e_i_pi]

Well there's your clue - orders relates to books via the bid column. This means we can JOIN the two tables together to realise the relationship, like so:

SELECT
  -- put your columns you want selected in here
FROM `orders`
INNER JOIN `books` ON `books`.`bookid` = `orders`.`bid`
WHERE `orders`.`custid` = 1004

This shold generate a row of data for each book in each order for the customer with id 1004.

[skatingrocker17]

e_i_pi, on 17 April 2012 - 02:48 PM, said:

Well there's your clue - orders relates to books via the bid column. This means we can JOIN the two tables together to realise the relationship, like so:

SELECT
  -- put your columns you want selected in here
FROM `orders`
INNER JOIN `books` ON `books`.`bookid` = `orders`.`bid`
WHERE `orders`.`custid` = 1004

This shold generate a row of data for each book in each order for the customer with id 1004.

Thanks that worked. But how do I get the name column from the books table and get that to display too?
Thanks

[e_i_pi]

In your SELECT statement, when you are naming the columns, precede them with the table name, like so:

SELECT
 `orders`.`custid`,
 `books`.`book_title`

...as a for instance. If you have multiple columns with the same name, you would use the AS keyword to give them unique names during the output:

SELECT
 `orders`.`id` AS order_id,
 `books`.`id` AS book_id

[skatingrocker17]

e_i_pi, on 17 April 2012 - 03:48 PM, said:

In your SELECT statement, when you are naming the columns, precede them with the table name, like so:

SELECT
 `orders`.`custid`,
 `books`.`book_title`

...as a for instance. If you have multiple columns with the same name, you would use the AS keyword to give them unique names during the output:

SELECT
 `orders`.`id` AS order_id,
 `books`.`id` AS book_id

This is the statement that worked for me. I tried to follow your advice on getting the name column included but I couldn't figure it out. I tried "inner join name.books" and variations of that with no luck. Name is only a member of the book table.

select 
custid, day from orders 
inner join books on books.bookid = orders.bid 
where orders.custid = 1004;

[sBorg]

How about this:

SELECT ord.custid, ord.orderid, bks.bookname
FROM `orders` AS ord, `books` AS bks
WHERE ord.bid= bks.bookid AND
ord.custid = 1004;

Lets break it down:

1.

SELECT ord.custid, ord.orderid, bks.bookname
FROM `orders` AS ord, `books` AS bks

SELECT the required values FROM the tables.

2.

WHERE ord.bid= bks.bookid AND
ord.custid = 1004;

A simple WHERE clause with the conditions between the tables depending on the related columns.

[e_i_pi]

I personally opt for INNER JOINs, as the clause upon which you are JOINing the tables is located visually right where the JOINed table is. This might not seem like a big deal, but it is when you start writing queries against large 3NF+ DBs, and your queries reach 200-400 lines. Joining a swathe of tables and having around 30 WHERE clauses is an eyesore, and nigh on impossible to follow.

In regards to getting it to work with INNER JOINs, you have to utilise the JOIN syntax properly:

FROM table_a
INNER JOIN table_b ON table_b.foreign_key = table_a.key

In your case, the query would be:

SELECT
  `orders`.`custid`,
  `orders`.`day`
FROM `orders`
INNER JOIN `books` ON `books`.`bookid` = `orders`.`bid`
WHERE `orders`.`custid` = 1004

...assuming that custid and day reside in the orders table.