Adrian Fischer Adrian Fischer - 10 months ago 19
MySQL Question

mysql JOIN has me beat

From the outset let me say that I really struggle with joins.

I have a table called 'registervendors'. In the table is a column called 'state'. 'state' is a single digit number. I also have a column called 'vendregid'.

I have another table called 'states'. In that table is a column called 'state'. 'state' in this table is a three letter abbreviation and another called 'statesid' which is a single digit number.

I need all this to happen WHERE 'vendregid' is matched.

So I think it would go something like this:

$rows = $db->select(" SELECT * FROM registervendors r JOIN states s ON s.state = r.state WHERE regid='$vendregid' ");

if regid is a match I want to pull from table 'states' the abbreviation in column 'state' where table 'statesid' matches 'registorvendors' column 'state'
Its doing my head in.


If I read (and you explained) correctly, you want to join the registervendors table with the states table using the state id column on the former and the statesid id column on the latter.

SELECT rv.*, s.*
FROM registervendors rv
INNER JOIN states s
    ON rv.state = s.statesid
WHERE vendregid = 'some_id'