Adrian Fischer Adrian Fischer - 1 year ago 44
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.

Answer Source

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'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download