2 tables join together

[lstargel]

Ok here is what i'm try to do. I've made a webpage for real estate. I can add listings to the database in one table(lisitngs) and add agents(agents) to another table. Inside the listings table i have 2 fields(agent & agent2) for agents name that has the property listed. I need to display the listing on a page with the agents information. The agent field always has data but agent2 field is null sometimes. On this same page i have it repeat region that show all the listings for a category. So this information needs to repeat down the page.

Here i my tables information:

Table: listings Fields: id,listingsNumber, agent, agent2, category, category2, etc

Table: agents Fields: agentID, name, phone, phone2, photo

Here is my code that i've used to display only the first agents information.

 

SELECT *
FROM listings, agents
WHERE (agents.name = listings.agent) AND (listings.category2 = 'lake' or listings.category = 'lake')
ORDER BY listings.timeStamp DESC

 

I've changed

WHERE (agents.name = listings.agent) AND (listings.category2 = 'lake' or listings.category = 'lake')

to

WHERE (agents.name = listings.agent and agents.name = listings.agent2) AND (listings.category2 = 'lake' or listings.category = 'lake')

WHERE (agents.name = listings.agent or agents.name = listings.agent2) AND (listings.category2 = 'lake' or listings.category = 'lake')

But has failed to get agent2 information.

Any kind of help would be greatly appreciated.

Thanks

[kickstart]

Hi

 

I would use something like this:-

 

SELECT *
FROM listings
INNER JOIN agents
ON agents.name = listings.agent
WHERE listings.category2 = 'lake' or listings.category = 'lake')
UNION 
SELECT *
FROM listings
INNER JOIN agents
ON agents.name = listings.agent2
WHERE listings.category2 = 'lake' or listings.category = 'lake')
ORDER BY listings.timeStamp DESC 

 

All the best

 

Keith

[mikosiko]

I see a bad design for your table listings.

a) agent, agent2 and category, category2 should not be part of listings those fields should be part of some "bridge" tables (one for listings-agents and probably one for listings-categories.

b) you should be using agentID as the linking field instead of agent.name (same apply for categories)

 

changing your design will give you a more flexible and generic model and your select will be more simple just using JOINs

[kickstart]

changing your design will give you a more flexible and generic model and your select will be more simple just using JOINs

 

I agree

 

All the best

 

Keith

[lstargel]

mikosiko,

Can you show me where i can read about this "bridge" table. Guess you can tell im new to this. So i can create 5 tables named

listings, category, agents, listings-category(bridge table), & listings-agents(bridge table).

So the info i put into listings, category, & agents table it would bridge and combined that data into a table called listings-category and listings-agents? Its this the way it would work or im way off base.

[kickstart]

Hi

 

Idea is that you have your table of listings , and your table of agents and one of categories. Probably all with an auto increment primary key.

 

Then you have a table to link listings to agents:-

Id

ListingId

AgentId

 

Same for categories

Id

ListingId

CategoryId

 

This was to find the agents for a listing you join the listings table with the listings / agent link table and then join that with the agent table to get the details.

 

This means that you can have as many agents as you want for each listing without having to change the table designs to cope. Also means that you are joining on a single field.

 

Furthermore, if you wanted to know all the categories for listings an agent was involved in you only have to join agents to listings via a couple of joins, rather than joining on 2 columns (which becomes even nastier when you change it to, say, 10 agent columns in the future)

 

All the best

 

Keith

[lstargel]

Ok i'm still stuck after reading alot on the web. I've got some info about relational columns.

Here is my tables:

CREATE TABLE `listings` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`listingName` VARCHAR(50) NOT NULL,
`address` VARCHAR(255) NOT NULL,
`agent` INT(10) UNSIGNED NOT NULL,
`agent2` INT(10) UNSIGNED NULL DEFAULT NULL,
`categoryID` INT(10) UNSIGNED NOT NULL,
`category2ID` INT(10) UNSIGNED NULL DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `agent` (`agent`),
INDEX `agent2` (`agent2`),
INDEX `categoryID` (`categoryID`),
INDEX `category2ID` (`category2ID`),
CONSTRAINT `FK_listings_agents` FOREIGN KEY (`agent`) REFERENCES `agents` (`agendID`) ON UPDATE CASCADE,
CONSTRAINT `FK_listings_agents_2` FOREIGN KEY (`agent2`) REFERENCES `agents` (`agendID`) ON UPDATE CASCADE,
CONSTRAINT `FK_listings_category` FOREIGN KEY (`categoryID`) REFERENCES `category` (`catID`) ON UPDATE CASCADE,
CONSTRAINT `FK_listings_category_2` FOREIGN KEY (`category2ID`) REFERENCES `category` (`catID`) ON UPDATE CASCADE
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT

 

CREATE TABLE `agents` (
`agendID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NOT NULL,
`phone` VARCHAR(50) NOT NULL,
`cellPhone` VARCHAR(50) NULL DEFAULT NULL,
PRIMARY KEY (`agendID`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT

 

CREATE TABLE `category` (
`catID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`categoryName` VARCHAR(50) NOT NULL,
PRIMARY KEY (`catID`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT

 

Im running this query:

SELECT *
FROM listings
JOIN agents
ON (listings.agent=agents.agendID or listings.agent2=agents.agendID)
AND (listings.categoryID=1 or listings.category2ID=1)
ORDER BY listings.listingName

 

The problem im having it will show 2 row if i have 2 agents listed in the listings table. So when i display this in my webpage i get 2 listings for this property the first with agent1 and another with agent2. I need to some how only show the property 1 time and display both agents on it.

I know you said i should create 5 tables but have not figure out that part.(listings, agents, category, listings_agents, listings_category)

Again thank for your guys help.

[kickstart]

Hi

 

That is more a php question than a MySQL question.

 

If you want one per page then probably best to either bring them all back and store them in an array (maybe in a session variable given the numbers will be limited) and page through that array, or you use a LIMIT clause on the select to select the one you want, and on the next page you select the other one.

 

All the best

 

Keith