[Resolved] SQL Query on multi tables

dcjones

Hi all.

Can you advise on the following, I have 2 tables:

TABLE 1. NAME chandos_agents

agent_id
agent_name
agent_location_id

and so on

TABLE 2. NAME chandos_agents_location

agent_location_id
agent_location

I need to write aquery that will show the results as follows:

agent_id
agent_name
agent_location

I have been playing around with my query all day with no result.

I think it should be something like this:

SELECT agents., agent_location.
FROM agents, agent_location
WHERE agent_id = agent_location_id

Or am I going down the wronge road.

Any help would be good.

Kind regards, keep safe and well.

Dereck

dcjones

Hi all again.

I have the following SQL statement.

SELECT chandos_agents., chandos_agents_location.
FROM chandos_agents JOIN chandos_agents_location
WHERE chandos_agents.agent_id = chandos_agents_location.agent_location_id

This returns results but the field "agent_loction2 in the "cahndos_agents_location" table show empty.

Any idea why

Many thanks

Dereck

dcjones

Hi all, once again,

Its odd how looking at your own post you see the resolution.

This is now my SQL statement and it works

SELECT chandos_agents.agent_id,agent_contact, agent_name,chandos_agents_location.*
FROM chandos_agents JOIN chandos_agents_location
WHERE chandos_agents.agent_id = chandos_agents_location.agent_location_id

Thanks

Dereck

TheDefender

Just out of curiosity, why would this happen?
chandos_agents.agent_id = chandos_agents_location.agent_location_id

This is not a desirable table design if an agent ID from one table equals a Location ID from a different table... You should try to use a better naming scheme so it makes more sense...