Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
    )   
share|improve this question
2  
Required reading: en.wikipedia.org/wiki/Join_(SQL) –  FreshPhilOfSO Feb 6 '13 at 10:44
    
Please supply the CREATE TABLE statements for all tables. –  Martin Smith Feb 6 '13 at 11:32
    
I see you've added some of them. What about sandwich_type_table? –  Martin Smith Feb 6 '13 at 11:44
    
Apologies i removed active by mistake. I have tried with INNER JOINS and still the only rows that appear are ones that have a count() that notes NULL –  user1714133 Feb 6 '13 at 11:47
    
Also sandwich type is not part of the problem as the problem resides in the sald_type table –  user1714133 Feb 6 '13 at 12:59
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.