getting data across 3 tables

[jgentes]

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

SELECT 'parts.number', 'printers.name'
     FROM parts 
     UNION printers ON printer_id = 'user_input' 
     JOIN parts_printer_consumables ppc ON printer_id = 'user_input' 
     WHERE printers.printer_id = 'user_input' AND parts.part_id = 'ppc.part_id'
 

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

[pbmods]

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.

[jgentes]

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

SELECT parts.number, printers.name FROM printers
          JOIN printers ON printers.id = parts_printer_consumable.printer_id
         WHERE parts_printer_consumables.printer_id = '19'
               AND parts.part_id = parts_printer_consumable.consumable_id
 

where 19 = user-input

[mwasif]

You have not mentioned your 2nd table name parts_printer_consumables in your JOIN statement.

[jgentes]

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

SELECT parts.number, printers.name FROM parts
     JOIN parts_printer_consumable ppc ON ppc.printer_id = '19' 
     JOIN printers ON printers.id = ppc.printer_id
     WHERE ppc.printer_id = '19' 
             AND parts.part_id = ppc.consumable_id
 

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

[pbmods]

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.

SELECT
        `parts`.`number`,
        `printers`.`name`
    FROM
    (
            `parts_printer_consumable`
                AS `ppc`
        LEFT JOIN
            `parts`
                ON
                    `parts`.`part_id` = `ppc`.`consumable_id`
        LEFT JOIN
            `printers`
                ON
                    `printers`.`id` = `ppc`.`printer_id`
    )
    WHERE
        `ppc`.`printer_id` = '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].

[jgentes]

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?