help with 3 or more table joins in a query 1

[frainbreeze]

hello

i've not had to join up more than 2 tables before, and i've had a look around but i cant seem to find anything on how to do this properly..

i have 3 tables:

suppliers: supplier_ID, supplier_name, contact_name
(table)
------------------
1 supplier1 joe
2 supplier2 bob
3 supplier3 moe


sc_link: sc_link_ID, supplier_ID, category_ID
(table)
------------------
1 1 1
2 2 2
3 3 1

categories: category_ID, category_name
(table)
------------------
1 Balloons
2 Posters

sidenote:
and FYI on my form i have:
<select name="SearchCategory"> where all options are
<options value="category_ID"> Category_name</options>

this is all filled in via a loop in php. Grabbing this value is not the problem (if it were then im in the wrong forum..).


i want to display all suppliers that are in a certain category depending on what is selected from the dropdown. For example, if the user selects "Balloons" from the dropdown and submits, it should return

supplier1
supplier3

pretty basic right? not for me! i have tried:

SELECT suppliers.supplier_name, sc_link_id, categories.category_name
FROM suppliers, sc_link, categories
WHERE (suppliers.supplier_ID = sc_link.supplier_ID AND sc_link.category_ID = categories.category_ID AND `categories.category_ID` = '$matchthis')

which returns an error message of: Unknown column 'sc_link.category_ID' in 'where clause'

and i have tried:

SELECT suppliers.supplier_name, sc_link.sc_link_id, categories.category_name FROM suppliers JOIN sc_link
ON suppliers.supplier_ID = sc_link.supplier_ID JOIN categories
ON sc_link.supplier_ID = categories.category_ID
WHERE `category_ID` like '%$matchthis%'";

which returns an error message of: You have an error in your SQL syntax near 'ON suppliers.supplier_ID = sc_link.supplier_ID JOIN categories ON sc_link.s' at line 2

does anyone know how i can join 3 tables in one statement? or where i'm going wrong? and to think, this is just the beginning...somewhere in the future it looks like i may have to join like 5-7 tables or something like that. so
any help (or even general directions to somewhere else that'd help) are much appreciated

thanks

frainbreeze

 

[r937]

you should never use LIKE with a numeric field

have the dropdown pass the category id(s) and use them
to populate an IN list

the reason your join wasn't working is because you were matching sc_link.supplier_ID to category.category_ID

try this --

select categories.category_name
     , suppliers.supplier_name
  from categories
inner
  join sc_link
    on categories.category_ID
     = sc_link.[b]category_ID[/b] 
inner
  join suppliers 
    on sc_link.supplier_ID 
     = suppliers.supplier_ID 
 where categories.category_ID
    in ( 1, 5, 11 )
order
    by categories.category_name
     , suppliers.supplier_name

rudy

http://r937.com/

SQL Consulting

 

[frainbreeze]

hello

thanks for the help, im not sure what an IN list is (where you get 1, 5, 11??) so i changed

where categories.category_ID = '$matchthis'

which seems to make it work!

cheers mate

frainbreeze

 

[r937]

the IN list is required if your dropdown list allows the MULTIPLE option

please don't put quotes around numeric values intended to be compared with numeric columns

rudy

http://r937.com/

SQL Consulting

 

[frainbreeze]

oic, well it doesnt allow multiple, so no problemo there..

and is there any particular reason why i shouldnt put quotes? i put the quotes in because the value is a php variable inside of a select statement, i.e.

$selectquery = "SELECT etcetc '$matchthis' ";


frainbreeze

 

[r937]

quotes are for strings

numerics aren't strings

in the long run, you will save yourself countless database query debugging headaches by remembering which is which

mysql is really really sloppy

this does not mean we should be too

i don't do php but i'm pretty sure you don't need the single quotes inside the query string

could be wrong, though

(wouldn't be the first time)

but if so, my esteem of php just slipped a notch



rudy

http://r937.com/

SQL Consulting

 

[frainbreeze]

well, since this query is part of a whole search/match array thing in php, i just thought it would be easier to keep it treated as a string, instead of as an int. especially later on when it might be a part of a bigger search where all words are exploded into an array (of strings) and then cycled through and searched for.

also, im not going to be adding or subtracting the ID's, i've put telephone numbers in as strings under this logic as well (whats the point in adding 2 phone numbers together?). so...really..i wont be using ID's in any other way than finding the row (i.e. nothing like if ID > 4, print whatever etc), so for these purposes i dont see whats wrong with the ID being treated as a string..

its probably sloppy of me, and not php, but it just seems easier to have everything as a string unless explicitly needed to calculate (i.e. costs in ££ is not a string)

still unconvinced mate

frainbreeze

 

[r937]

hey, it's not my job to convince you

you want to make strings out of your numbers? be my guest

i was only offering you advice on how to avoid problems

rudy

http://r937.com/

SQL Consulting

 

[frainbreeze]

umm, well i didnt actually mean it was your job to "convince" me to use ints over strings yanno..

you said that it could get messy when debugging if i dont keep them straight, and i just wanted to explain that i didnt just randomly use a string instead of an int and the reason behind it.

you have more experience dealing with db's than i do, so if my reasoning behind the types is unsound, it'd be good to know why or how it'd cause a problem in the long run if i store things as strings? in storing any numbers not used for calculations as strings on a consistent basis, wouldnt that prevent against potential problems btwn datatypes?

and trust me, i do appreciate the advice, hence the star - but you didnt have to get all snarky about it...

chill,

frainbreeze

 

[r937]

snarky? oh my $deity, i musta forget to use a smiley

here we go:


rudy

http://r937.com/

SQL Consulting

 

[skiflyer]

Here's my take on it... just make sure you've set the data types properly in the database so numbers are stored as numbers and not varchars or something of that sort.

Then you'll be fine, you can send the numbers enclosed in single quotes or not. By enclosing them in single quotes you force the database to do a type conversion, and you suffer some performance degradation, but it will function properly.

 

[r937]

you'll be fine" is of course a relative thing, in mysql

have you never seen a mysql coder write

where id like '%15%'



like i said earlier, mysql is really sloppy

it's best not to pick up any sloppy habits

in other databases, you'll get syntax errors (e.g. data type mismatch)

rudy

http://r937.com/

SQL Consulting