Page 1 of 3 123 LastLast
Results 1 to 15 of 34
  1. #1
    Join Date
    Oct 2009
    Posts
    67

    Answered: Help needed with what should be a simple join, but I must be missing something.

    It's been a long while since I've visited, but I'm probably missing something ridiculously easy & it's been nagging at me all day. Here are the 2 tables:

    Click image for larger version. 

Name:	table-structure.png 
Views:	2 
Size:	36.4 KB 
ID:	16793

    Here are the 2 working queries I'd like to perform in one query:
    Code:
    SELECT rank, id, name
    FROM item_types
    WHERE collection_id = 20
    ORDER BY rank ASC;
    Returns 15 rows:
    Code:
    +------+-----+---------------------+ 
    | rank | id  | name                | 
    +------+-----+---------------------+ 
    |    1 | 521 | Hazel Dazzle        | 
    |    2 | 640 | AbleGamers Joystick | 
    |    3 | 532 | Dryathlon           | 
    |    4 | 405 | 5pm.co.uk           | 
    |    5 | 351 | No Words by 1x.com  | 
    |    6 | 205 | HoneyBlend Icon     | 
    |    7 | 207 | MailChimp Hat       | 
    |    8 | 208 | MailChimp Plushie   | 
    |    9 | 209 | Pinto Burrito       | 
    |   10 | 203 | ExpenseMagic Wand   | 
    |   11 | 204 | Highlights Icon     | 
    |   12 | 206 | ItemBrowser Icon    | 
    |   13 | 272 | Sheep Up!           | 
    |   14 | 270 | Mangatar            | 
    |   15 | 271 | Nakrum              | 
    +------+-----+---------------------+
    2nd query:
    Code:
    SELECT item_type_id, id, number
    FROM items
    WHERE user_id = 15690
    AND item_type_id in (
      SELECT id
      FROM item_types
      WHERE collection_id = 20
    );
    Returns 13 rows:
    Code:
    +--------------+----------+--------+
    | item_type_id | id       | number |
    +--------------+----------+--------+
    |          521 |  8815787 |   4675 |
    |          271 | 14431899 |  13185 |
    |          272 | 14631979 |  13497 |
    |          204 | 14635494 |  13430 |
    |          207 | 14636192 |  13414 |
    |          206 | 14650339 |  13454 |
    |          203 | 14650678 |  13465 |
    |          209 | 14657974 |  13730 |
    |          270 | 14660658 |  13483 |
    |          351 | 14660930 |  13001 |
    |          532 | 14660957 |  12557 |
    |          205 | 14664921 |  13387 |
    |          208 | 14673362 |  13484 |
    +--------------+----------+--------+
    When I try to combine the queries, I only end up with 13 rows, when I should end up with 15 and the user has 2 nulls for items ranked 2 & 4. No combination of right/left/outer will give me all 15 rows with 2 nulls for item_id/number:
    Code:
    SELECT it.rank, it.id item_type_id, it.name, i.id item_id, i.number
    FROM item_types it
    JOIN items i
    ON i.item_type_id = it.id
    WHERE i.user_id = 15690
    AND it.collection_id = 20
    ORDER BY it.rank;
    Returns the following 13 rows:
    Code:
    +------+--------------+--------------------+----------+--------+
    | rank | item_type_id | name               | item_id  | number |
    +------+--------------+--------------------+----------+--------+
    |    1 |          521 | Hazel Dazzle       |  8815787 |   4675 |
    |    3 |          532 | Dryathlon          | 14660957 |  12557 |
    |    5 |          351 | No Words by 1x.com | 14660930 |  13001 |
    |    6 |          205 | HoneyBlend Icon    | 14664921 |  13387 |
    |    7 |          207 | MailChimp Hat      | 14636192 |  13414 |
    |    8 |          208 | MailChimp Plushie  | 14673362 |  13484 |
    |    9 |          209 | Pinto Burrito      | 14657974 |  13730 |
    |   10 |          203 | ExpenseMagic Wand  | 14650678 |  13465 |
    |   11 |          204 | Highlights Icon    | 14635494 |  13430 |
    |   12 |          206 | ItemBrowser Icon   | 14650339 |  13454 |
    |   13 |          272 | Sheep Up!          | 14631979 |  13497 |
    |   14 |          270 | Mangatar           | 14660658 |  13483 |
    |   15 |          271 | Nakrum             | 14431899 |  13185 |
    +------+--------------+--------------------+----------+--------+
    Last edited by LAYGO; 03-02-16 at 18:19.

  2. Best Answer
    Posted by healdem

    "it will not give the results you want if you specify the userid
    the only other option I can think of is to use a subquery / sub select to retrieve the information from the items table"


  3. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Use a left join as opposed to a natural join ?
    I'd rather be riding on the Tiger 800 or the Norton

  4. #3
    Join Date
    Oct 2009
    Posts
    67
    Same result:
    Code:
    SELECT it.rank, it.id item_type_id, it.name, i.id item_id, i.number
    FROM item_types it
    LEFT JOIN items i
    ON i.item_type_id = it.id
    WHERE i.user_id = 15690
    AND it.collection_id = 20
    ORDER BY it.rank;
    
    +------+--------------+--------------------+----------+--------+
    | rank | item_type_id | name               | item_id  | number |
    +------+--------------+--------------------+----------+--------+
    |    1 |          521 | Hazel Dazzle       |  8815787 |   4675 |
    |    3 |          532 | Dryathlon          | 14660957 |  12557 |
    |    5 |          351 | No Words by 1x.com | 14660930 |  13001 |
    |    6 |          205 | HoneyBlend Icon    | 14664921 |  13387 |
    |    7 |          207 | MailChimp Hat      | 14636192 |  13414 |
    |    8 |          208 | MailChimp Plushie  | 14673362 |  13484 |
    |    9 |          209 | Pinto Burrito      | 14657974 |  13730 |
    |   10 |          203 | ExpenseMagic Wand  | 14650678 |  13465 |
    |   11 |          204 | Highlights Icon    | 14635494 |  13430 |
    |   12 |          206 | ItemBrowser Icon   | 14650339 |  13454 |
    |   13 |          272 | Sheep Up!          | 14631979 |  13497 |
    |   14 |          270 | Mangatar           | 14660658 |  13483 |
    |   15 |          271 | Nakrum             | 14431899 |  13185 |
    +------+--------------+--------------------+----------+--------+
    13 rows in set (0.01 sec)

  5. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you want all 15 rows from item_types and as many matching rows from items then your left join is the wrong way round

    Code:
    SELECT it.rank, it.id item_type_id, it.name, i.id item_id, i.number
    FROM item_types it
    LEFT JOIN items i
    ON it.id = i.item_type_id
    WHERE i.user_id = 15690
    AND it.collection_id = 20
    ORDER BY it.rank;
    I'd rather be riding on the Tiger 800 or the Norton

  6. #5
    Join Date
    Oct 2009
    Posts
    67
    I wish it was that easy! I tried all inner/outer/left/right combinations.

    Code:
    SELECT it.rank, it.id item_type_id, it.name, i.id item_id, i.number
    FROM  items i
    LEFT JOIN item_types it
    ON i.item_type_id = it.id
    WHERE i.user_id = 15690
    AND it.collection_id = 20
    ORDER BY it.rank;
    
    +------+--------------+--------------------+----------+--------+
    | rank | item_type_id | name               | item_id  | number |
    +------+--------------+--------------------+----------+--------+
    |    1 |          521 | Hazel Dazzle       |  8815787 |   4675 |
    |    3 |          532 | Dryathlon          | 14660957 |  12557 |
    |    5 |          351 | No Words by 1x.com | 14660930 |  13001 |
    |    6 |          205 | HoneyBlend Icon    | 14664921 |  13387 |
    |    7 |          207 | MailChimp Hat      | 14636192 |  13414 |
    |    8 |          208 | MailChimp Plushie  | 14673362 |  13484 |
    |    9 |          209 | Pinto Burrito      | 14657974 |  13730 |
    |   10 |          203 | ExpenseMagic Wand  | 14650678 |  13465 |
    |   11 |          204 | Highlights Icon    | 14635494 |  13430 |
    |   12 |          206 | ItemBrowser Icon   | 14650339 |  13454 |
    |   13 |          272 | Sheep Up!          | 14631979 |  13497 |
    |   14 |          270 | Mangatar           | 14660658 |  13483 |
    |   15 |          271 | Nakrum             | 14431899 |  13185 |
    +------+--------------+--------------------+----------+--------+
    13 rows in set (0.03 sec)

  7. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    well thats peculiar
    what version of MySQL are you using
    ..just wondering out aloud if using a reserved word 'name' causes a problem

    ..instead of re running your same query what happens if you try as suggested in #4
    IE the left join where you select all the rows from item types (the left side of the join) and as many rows as match from items on the right side of the join

    if you have genuinely tried all permutations, especially the one suggested in #4 then it could be you have a data error which precludes the result you expect
    Last edited by healdem; 03-07-16 at 12:32.
    I'd rather be riding on the Tiger 800 or the Norton

  8. #7
    Join Date
    Oct 2009
    Posts
    67
    Re-look at the same query, I did reverse the table names. I can try aliasing the col name.

  9. #8
    Join Date
    Oct 2009
    Posts
    67
    Aliasing the name column did not help.

  10. #9
    Join Date
    Oct 2009
    Posts
    67
    OOPS! Forgot to include my MySQL version:
    5.6.29 MySQL Community Server (GPL)

  11. #10
    Join Date
    Oct 2009
    Posts
    67
    As far as a data error, there are 15 rows in `item_types` with Collection_Id = 20. I have that data included in the 1st post. I need all of those returned 100% of the time & whatever `items` a particular user might have.

  12. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by LAYGO View Post
    Re-look at the same query, I did reverse the table names. I can try aliasing the col name.
    all 3 examples you have posted have the same structure

    Code:
    select * from item_types  as it 
    left join items as i on it.id = i.item_type_id
    order by rank
    gives
    Code:
    1	521	hazel dazzle	521	something
    2	640	joystick		
    3	532	dryath	532	something
    4	405	5pm		
    5	351	No Words	351	something
    6	205	6	205	something
    7	207	7	207	something
    8	208	8	208	something
    9	209	9	209	something
    10	103	10		
    11	204	Highlights Icon	204	something
    12	206	ItemBrowser Icon	206	something
    13	272	Sheep Up!	272	something
    14	270	Mangatar	270	something
    15	271	Nakrum	271	something
    but bear in mind I renamed name to tname
    i created a column description in items in place of the 2 other columns in your example above, and set that column = 'something' so where you see something it indicates that a row was matched in items, where you see nothing there wasn't
    I used select * instead of expressly typing the column names

    i eliminated columns that aren't germane to the problem, it could be that the answers you are getting are correct because of a data error / misconception

    seeing as you couldn't be bothered to post the ddl and some sample data I winged it. so the column names aren't the same, the data isn't the same. but the above query returns 15 rows

    15 from item types and 12 values from matching rows in items
    I'd rather be riding on the Tiger 800 or the Norton

  13. #12
    Join Date
    Oct 2009
    Posts
    67
    I'm not trying to be difficult, sorry if it appears so.

    I posted exactly what the queries returned in the 1st post. If you need more data, let me know.

    This is someone else's schema who is NOT a developer. Probably why he sold the company to us.

    Item_types:
    Code:
    CREATE TABLE `item_types` (
    	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    	`collection_id` INT(10) UNSIGNED NOT NULL,
    	`name` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',
    	`qualifier` VARCHAR(20) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
    	`found_description` TEXT NULL COLLATE 'utf8_unicode_ci',
    	`bought_description` TEXT NULL COLLATE 'utf8_unicode_ci',
    	`recipe_formula` VARCHAR(255) NULL DEFAULT NULL,
    	`image_url` VARCHAR(255) NOT NULL,
    	`silhouette_image_url` VARCHAR(255) NOT NULL,
    	`image_key` VARCHAR(64) NOT NULL,
    	`rank` INT(10) NOT NULL DEFAULT '1',
    	`cost` INT(10) UNSIGNED NOT NULL DEFAULT '0',
    	`store_duration` VARCHAR(20) NULL DEFAULT NULL,
    	`store_cost` INT(11) NOT NULL DEFAULT '0',
    	`stores` VARCHAR(100) NOT NULL,
    	`places` VARCHAR(100) NOT NULL,
    	`locked` TINYINT(1) NOT NULL DEFAULT '0',
    	`release_date` DATETIME NULL DEFAULT NULL,
    	`branded_description` TEXT NOT NULL COLLATE 'utf8_unicode_ci',
    	`branded_prize_description` VARCHAR(255) NOT NULL,
    	`branded_prize_max` INT(3) NOT NULL,
    	`branded_prize_button_text` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
    	`branded_button_text` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
    	`branded_action_url` VARCHAR(255) NOT NULL,
    	`custom_item_user_id` VARCHAR(255) NOT NULL DEFAULT '',
    	`custom_item_recycle_distribution` TINYINT(1) NOT NULL DEFAULT '0',
    	`custom_item_type` ENUM('wallabee','player') NULL DEFAULT NULL,
    	`original_release_date` DATETIME NULL DEFAULT NULL,
    	`perfume` TEXT NULL,
    	`aftershave` TEXT NULL,
    	`custom_item_mixer_pool` TINYINT(1) NOT NULL DEFAULT '0',
    	PRIMARY KEY (`id`),
    	INDEX `collection_id` (`collection_id`),
    	INDEX `recipe_formula` (`recipe_formula`),
    	INDEX `custom_item_user_id` (`custom_item_user_id`),
    	INDEX `release_date` (`release_date`)
    )
    COLLATE='latin1_swedish_ci'
    ENGINE=InnoDB
    AUTO_INCREMENT=2042
    ;
    Code:
    INSERT INTO `item_types` (`rank`, `id`, `name`) VALUES (1, 521, 'Hazel Dazzle');
    INSERT INTO `item_types` (`rank`, `id`, `name`) VALUES (2, 640, 'AbleGamers Joystick');
    INSERT INTO `item_types` (`rank`, `id`, `name`) VALUES (3, 532, 'Dryathlon');
    INSERT INTO `item_types` (`rank`, `id`, `name`) VALUES (4, 405, '5pm.co.uk');
    INSERT INTO `item_types` (`rank`, `id`, `name`) VALUES (5, 351, 'No Words by 1x.com');
    INSERT INTO `item_types` (`rank`, `id`, `name`) VALUES (6, 205, 'HoneyBlend Icon');
    INSERT INTO `item_types` (`rank`, `id`, `name`) VALUES (7, 207, 'MailChimp Hat');
    INSERT INTO `item_types` (`rank`, `id`, `name`) VALUES (8, 208, 'MailChimp Plushie');
    INSERT INTO `item_types` (`rank`, `id`, `name`) VALUES (9, 209, 'Pinto Burrito');
    INSERT INTO `item_types` (`rank`, `id`, `name`) VALUES (10, 203, 'ExpenseMagic Wand');
    INSERT INTO `item_types` (`rank`, `id`, `name`) VALUES (11, 204, 'Highlights Icon');
    INSERT INTO `item_types` (`rank`, `id`, `name`) VALUES (12, 206, 'ItemBrowser Icon');
    INSERT INTO `item_types` (`rank`, `id`, `name`) VALUES (13, 272, 'Sheep Up!');
    INSERT INTO `item_types` (`rank`, `id`, `name`) VALUES (14, 270, 'Mangatar');
    INSERT INTO `item_types` (`rank`, `id`, `name`) VALUES (15, 271, 'Nakrum');
    Items:
    Code:
    CREATE TABLE `items` (
    	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    	`item_type_id` INT(10) UNSIGNED NOT NULL,
    	`number` INT(10) UNSIGNED NOT NULL,
    	`variant` INT(2) NOT NULL DEFAULT '0',
    	`misc` INT(10) UNSIGNED NOT NULL DEFAULT '0',
    	`recipe_1` INT(10) UNSIGNED NULL DEFAULT NULL,
    	`recipe_2` INT(10) UNSIGNED NULL DEFAULT NULL,
    	`user_id` INT(10) UNSIGNED NOT NULL DEFAULT '0',
    	`place_id` INT(10) UNSIGNED NOT NULL DEFAULT '0',
    	`item_id` INT(10) UNSIGNED NOT NULL DEFAULT '0',
    	`is_vaulted` TINYINT(1) NOT NULL DEFAULT '0',
    	`pouch_rank` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
    	`created_at` DATETIME NOT NULL,
    	`prize_redeemed` TINYINT(1) NOT NULL DEFAULT '1',
    	`coupon_serial` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
    	`status` ENUM('default','trade','market','locked','hidden','lottery','limbo','unique') NOT NULL DEFAULT 'default',
    	`status_action_id` INT(10) UNSIGNED NOT NULL DEFAULT '0',
    	PRIMARY KEY (`id`),
    	INDEX `user_id` (`user_id`),
    	INDEX `spot_id` (`place_id`),
    	INDEX `item_id` (`item_id`),
    	INDEX `item_type_id` (`item_type_id`),
    	INDEX `recipe_1` (`recipe_1`),
    	INDEX `recipe_2` (`recipe_2`),
    	INDEX `created_at` (`created_at`),
    	INDEX `place_id` (`place_id`),
    	INDEX `number` (`number`),
    	INDEX `status` (`status`),
    	INDEX `locked` (`user_id`, `is_vaulted`, `place_id`),
    	INDEX `number_2` (`number`),
    	INDEX `item_type_id_2` (`item_type_id`, `number`),
    	INDEX `number_3` (`number`)
    )
    COLLATE='latin1_swedish_ci'
    ENGINE=InnoDB
    AUTO_INCREMENT=15097654
    ;
    Code:
    INSERT INTO `items` (`item_type_id`, `id`, `number`) VALUES (521, 8815787, 4675);
    INSERT INTO `items` (`item_type_id`, `id`, `number`) VALUES (271, 14431899, 13185);
    INSERT INTO `items` (`item_type_id`, `id`, `number`) VALUES (272, 14631979, 13497);
    INSERT INTO `items` (`item_type_id`, `id`, `number`) VALUES (204, 14635494, 13430);
    INSERT INTO `items` (`item_type_id`, `id`, `number`) VALUES (207, 14636192, 13414);
    INSERT INTO `items` (`item_type_id`, `id`, `number`) VALUES (206, 14650339, 13454);
    INSERT INTO `items` (`item_type_id`, `id`, `number`) VALUES (203, 14650678, 13465);
    INSERT INTO `items` (`item_type_id`, `id`, `number`) VALUES (209, 14657974, 13730);
    INSERT INTO `items` (`item_type_id`, `id`, `number`) VALUES (270, 14660658, 13483);
    INSERT INTO `items` (`item_type_id`, `id`, `number`) VALUES (351, 14660930, 13001);
    INSERT INTO `items` (`item_type_id`, `id`, `number`) VALUES (532, 14660957, 12557);
    INSERT INTO `items` (`item_type_id`, `id`, `number`) VALUES (205, 14664921, 13387);
    INSERT INTO `items` (`item_type_id`, `id`, `number`) VALUES (208, 14673362, 13484);

  14. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so having used your DDL, butchering it to remove all columns with NOT NULL constraints the following query
    Code:
    select it.rank, i.item_type_id, it.name, i.id, i.number from item_types as it
    left join items as i on IT.id = i.item_type_id
    order by it.rank
    generates
    Code:
    	rank	item_type_id	name	id	number
    	1	521	Hazel Dazzle	8815787	4675
    	2		AbleGamers Joystick		
    	3	532	Dryathlon	14660957	12557
    	4		5pm.co.uk		
    	5	351	No Words by 1x.com	14660930	13001
    	6	205	HoneyBlend Icon	14664921	13387
    	7	207	MailChimp Hat	14636192	13414
    	8	208	MailChimp Plushie	14673362	13484
    	9	209	Pinto Burrito	14657974	13730
    	10	203	ExpenseMagic Wand	14650678	13465
    	11	204	Highlights Icon	14635494	13430
    	12	206	ItemBrowser Icon	14650339	13454
    	13	272	Sheep Up!	14631979	13497
    	14	270	Mangatar	14660658	13483
    	15	271	Nakrum	14431899	13185
    Essentially its the same as post#4.
    if you don't get the same results on your server then its a data problem. you are getting the rows you should, but not the row2s you expect. the most likely cause for that is down to the data and how it is filtered in the WHERE clause
    I'd rather be riding on the Tiger 800 or the Norton

  15. #14
    Join Date
    Oct 2009
    Posts
    67
    Thanks Healdem, I'll look into the where clauses.

  16. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Im not being funny,but before doing a data check make certain you have formed the left join correctly. both query examples posted in #3 & #5 have the join incorrectly formed for your stated purpose


    try the query in #13 and see what results it returns

    the order of columns (& tables) when referrignj to a LEFT (or right) JOIN is critical
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •