sany101
asked on
MySQL many to many query
Hi I have the following tables:
I currently have this query:
I however would also like to return the category_names which this project belongs to and other project_names that have project_visibility set to "1" and match this projects category_names
-- phpMyAdmin SQL Dump
-- version 3.2.0.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Feb 09, 2011 at 08:20 PM
-- Server version: 5.1.36
-- PHP Version: 5.3.0
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
--
-- Database: `test`
--
-- --------------------------------------------------------
--
-- Table structure for table `c`
--
CREATE TABLE IF NOT EXISTS `c` (
`category_id` int(11) NOT NULL,
`category_name` varchar(100) COLLATE latin1_german2_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci;
--
-- Dumping data for table `c`
--
INSERT INTO `c` (`category_id`, `category_name`) VALUES
(1, 'cat-1'),
(2, 'cat-2'),
(3, 'cat-3'),
(4, 'cat-4');
-- --------------------------------------------------------
--
-- Table structure for table `projects`
--
CREATE TABLE IF NOT EXISTS `projects` (
`project_id` int(11) NOT NULL AUTO_INCREMENT,
`project_name` varchar(100) COLLATE latin1_german2_ci NOT NULL,
`thumb` varchar(100) COLLATE latin1_german2_ci NOT NULL,
`project_visibility` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`project_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=4 ;
--
-- Dumping data for table `projects`
--
INSERT INTO `projects` (`project_id`, `project_name`, `thumb`, `project_visibility`) VALUES
(1, 'a', 'a-thumb', 1),
(2, 'b', 'b-thumb', 0),
(3, 'c', 'c-thumb', 1);
-- --------------------------------------------------------
--
-- Table structure for table `p_c`
--
CREATE TABLE IF NOT EXISTS `p_c` (
`project_id` int(11) NOT NULL,
`category_id` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci;
--
-- Dumping data for table `p_c`
--
INSERT INTO `p_c` (`project_id`, `category_id`) VALUES
(1, 3),
(1, 4),
(2, 1),
(2, 4),
(3, 2);
I currently have this query:
SELECT * FROM projects WHERE project_name = %s
which displays a project row based on the project_name passed via url variable.I however would also like to return the category_names which this project belongs to and other project_names that have project_visibility set to "1" and match this projects category_names
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.