Forum Moderators: coopster
i have 4 tables.
the tables and fields i am using for this. (the fields are not called the same in the tables, should they be?)
Users: user_id
Regions: region_sales_id & region_name
States: state_region & state_name
Leads: lead_state
Query #1.
I take the user_id from the users table and query the regions table. I then take the region that is associated with the matching user_id. works great.
Query #2.
I then take the result of query #1(the region name) and query the states table to find all the states that are party of that region. works great. If i print out the results, i get all the states for that region.
Query #3. (this is where I have the problem)
I then want to query the leads table and get all the leads in the states from query #2. I am only getting the results from the first state, not all.
Exception #1.
there is a field in the leads table called lead_type. There is one type that I do not want to see the results for.
Exception #2.
I have one state that is devided between 2 people. If the area code matches any of 6 areacodes, it goes somewhere else, the rest goes to the region listed for the state.
Would I just use if and else statements for the results to not include the exceptions?
I cant include the region when the lead is inserted into the database, that would be to easy, hehe. But the regions and states will be moving around and the user loses that lead when he loses that state.
select leads.leads
from users
inner join regions on users.user_id = regions.region_sales_id
inner join states on regions.region_name = states.state_name
inner join leads on leads.lead_state = states.state_name
where users.userid = XXXXX
Also, if a column will hold the same data as data in another table, you should name both columns the same: ie. user_id should be the same in each table.
