Hi guys I have a query which is pulling data from database :
SELECT sandwich_table.id,
`sandwich_name`,
salad,
COUNT(salad),
salad_type.type,
sandwich_type_table.type
FROM `sandwich_table`
LEFT JOIN urls
ON sandwich_table.id = urls.sandwich_id
LEFT JOIN salad_type
ON urls.salad = salad_type.id
LEFT JOIN sandwich_type_table
ON `sandwich_table`.`sandwich_type` = sandwich_type_table.id
WHERE `active` = 1
GROUP BY sandwich_table.`id`,
salad
Some of the data in salad_type.id is not represented in the COUNT(), as some types have not been established in the url table for example:
salad_type.id : 1,3 and 4 are in the url table but not 2.
I would like create a query to show all types whether there is a value or not? as this query only pulls the salad_type.id when it is established in the url table.
AS requested the table structures:
CREATE TABLE `salad_type`
(
`id` int(50) unsigned NOT NULL auto_increment Primary Key,
`type` varchar(255),
`name` int(10) default NULL,
`date` int(10) default NULL
);
CREATE TABLE `urls`
(
id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
`url` varchar(255) UNIQUE,
`sandwich_id` int,
`salad` int,
`date_added` int,
`username` int
)
CREATE TABLE `sandwich_table`
(
id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
`sandwich_name` varchar(255) UNIQUE,
`active` int,
`sandwich_type` int,
`username` int
)
CREATE TABLEstatements for all tables. – Martin Smith Feb 6 at 11:32sandwich_type_table? – Martin Smith Feb 6 at 11:44