12 Replies - 1178 Views - Last Post: 18 April 2012 - 12:30 AM

#1 skatingrocker17  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 117
  • Joined: 01-September 11

Select columns from separate tables

Posted 16 April 2012 - 05:57 PM

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

Is This A Good Question/Topic? 0
  • +

Replies To: Select columns from separate tables

#2 codeprada  Icon User is offline

  • Changed Man With Different Priorities
  • member icon

Reputation: 944
  • View blog
  • Posts: 2,353
  • Joined: 15-February 11

Re: Select columns from separate tables

Posted 16 April 2012 - 06:58 PM

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'


Was This Post Helpful? 1
  • +
  • -

#3 skatingrocker17  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 117
  • Joined: 01-September 11

Re: Select columns from separate tables

Posted 16 April 2012 - 07:13 PM

View Postcodeprada, 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?
Was This Post Helpful? 0
  • +
  • -

#4 codeprada  Icon User is offline

  • Changed Man With Different Priorities
  • member icon

Reputation: 944
  • View blog
  • Posts: 2,353
  • Joined: 15-February 11

Re: Select columns from separate tables

Posted 16 April 2012 - 07:49 PM

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.
Was This Post Helpful? 1
  • +
  • -

#5 skatingrocker17  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 117
  • Joined: 01-September 11

Re: Select columns from separate tables

Posted 17 April 2012 - 02:04 PM

View Postcodeprada, 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.
Was This Post Helpful? 0
  • +
  • -

#6 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 789
  • View blog
  • Posts: 1,676
  • Joined: 30-January 09

Re: Select columns from separate tables

Posted 17 April 2012 - 02:14 PM

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?
Was This Post Helpful? 0
  • +
  • -

#7 skatingrocker17  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 117
  • Joined: 01-September 11

Re: Select columns from separate tables

Posted 17 April 2012 - 02:20 PM

View Poste_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.
Was This Post Helpful? 0
  • +
  • -

#8 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 789
  • View blog
  • Posts: 1,676
  • Joined: 30-January 09

Re: Select columns from separate tables

Posted 17 April 2012 - 02:48 PM

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.
Was This Post Helpful? 2
  • +
  • -

#9 skatingrocker17  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 117
  • Joined: 01-September 11

Re: Select columns from separate tables

Posted 17 April 2012 - 03:07 PM

View Poste_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
Was This Post Helpful? 0
  • +
  • -

#10 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 789
  • View blog
  • Posts: 1,676
  • Joined: 30-January 09

Re: Select columns from separate tables

Posted 17 April 2012 - 03:48 PM

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


Was This Post Helpful? 1
  • +
  • -

#11 skatingrocker17  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 117
  • Joined: 01-September 11

Re: Select columns from separate tables

Posted 17 April 2012 - 09:06 PM

View Poste_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;

Was This Post Helpful? 0
  • +
  • -

#12 sBorg  Icon User is offline

  • New D.I.C Head

Reputation: 5
  • View blog
  • Posts: 28
  • Joined: 24-March 12

Re: Select columns from separate tables

Posted 17 April 2012 - 10:38 PM

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.
Was This Post Helpful? 1
  • +
  • -

#13 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 789
  • View blog
  • Posts: 1,676
  • Joined: 30-January 09

Re: Select columns from separate tables

Posted 18 April 2012 - 12:30 AM

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.
Was This Post Helpful? 3
  • +
  • -

Page 1 of 1