By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,679 Members | 2,469 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,679 IT Pros & Developers. It's quick & easy.

getting data across 3 tables

P: 32
I am trying to get data from 2 tables, however there is a 3rd reference table, and I am having trouble with the query.


Expand|Select|Wrap|Line Numbers
  1. SELECT 'parts.number', 'printers.name'
  2.      FROM parts 
  3.      UNION printers ON printer_id = 'user_input' 
  4.      JOIN parts_printer_consumables ppc ON printer_id = 'user_input' 
  5.      WHERE printers.printer_id = 'user_input' AND parts.part_id = 'ppc.part_id'
  6.  
I dont think that is the the right query though

The input is a given printer (integer)
grabs the part id and printer_id from ppc
gets part info from parts table
gets printer info from printers table

so its a join across 3 tables? :-? thanks for the help
Sep 5 '07 #1
Share this Question
Share on Google+
6 Replies


pbmods
Expert 5K+
P: 5,821
Heya, jgentes.

Instead of 'UNION', try using 'JOIN'.

UNION is for appending the results of one or more queries onto the end of the first one, vertically.
Sep 5 '07 #2

P: 32
okay i tried that, and still giving me an error.
ERROR 1054 unknown column 'parts_printer_consumable.printer_id' in the where clause.

Expand|Select|Wrap|Line Numbers
  1. SELECT parts.number, printers.name FROM printers
  2.           JOIN printers ON printers.id = parts_printer_consumable.printer_id
  3.          WHERE parts_printer_consumables.printer_id = '19'
  4.                AND parts.part_id = parts_printer_consumable.consumable_id
  5.  
where 19 = user-input
Sep 6 '07 #3

mwasif
Expert 100+
P: 801
You have not mentioned your 2nd table name parts_printer_consumables in your JOIN statement.
Sep 6 '07 #4

P: 32
okay, how would i join it? because the only 2 fields are the printer_id and the consumable_id ? this table was intended to be reference only

okay i made an adjustment and got another error

Expand|Select|Wrap|Line Numbers
  1. SELECT parts.number, printers.name FROM parts
  2.      JOIN parts_printer_consumable ppc ON ppc.printer_id = '19' 
  3.      JOIN printers ON printers.id = ppc.printer_id
  4.      WHERE ppc.printer_id = '19' 
  5.              AND parts.part_id = ppc.consumable_id
  6.  
I now have the error

unknown column 'ppc.printer_id' in ON clause

ON A SIDE NOTE... what is the notation for the mysql code? i tried a number of things but couldn't figure it out, and i see that someone edited my first post to abide by the correct notation
Sep 6 '07 #5

pbmods
Expert 5K+
P: 5,821
Heya, Jgentes.

Now then.

Think about it like this: When you JOIN tables, you're creating one big table out of those multiple tables and searching that, instead of individual tables. That's not exactly how it works under the hood, but it's close enough.

Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.         `parts`.`number`,
  3.         `printers`.`name`
  4.     FROM
  5.     (
  6.             `parts_printer_consumable`
  7.                 AS `ppc`
  8.         LEFT JOIN
  9.             `parts`
  10.                 ON
  11.                     `parts`.`part_id` = `ppc`.`consumable_id`
  12.         LEFT JOIN
  13.             `printers`
  14.                 ON
  15.                     `printers`.`id` = `ppc`.`printer_id`
  16.     )
  17.     WHERE
  18.         `ppc`.`printer_id` = '19'
  19.  
I changed your JOINs to LEFT JOINs and switched the order so that you will only get results if there are results in the `ppc` table.

Aside from being easier to look at, this setup makes more sense. What you are doing is taking all of the results from `ppc` and appending rows from `parts` where the IDs match up, then appending rows from `printers` where those IDs match up and then filter the whole thing by `printer_id`.

Note that because `ppc` is the origin table for the JOIN, MySQL is smart enough to evaluate the WHERE clause before joining the other tables.

PS, you're looking for [code=mysql].
Sep 6 '07 #6

P: 32
thank you pb, that worked.

and ahh.. i was using [code-mysql] and [mysql] and [sql]
ill have to remember that.

and whats the difference between just a join and a left join?
is it just a matter of "proper" coding techniques?
Sep 6 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.