CODEWITHSUNDEEP
sqlmysql
Harri
Harri

Reputation: 2732

Querying two tables at once in MySQL

I have two tables; productstorymap and stories. productstorymap has productId and storyId columns. stories has id and status columns.

I need to query all stories which belong to a certain product and have certain status. Closest thing I've got is

SELECT map.*, story.*
FROM productstorymap map, stories story
WHERE map.productId=1 AND story.status=1

But it returns me stories which don't belong to product 1. It returns something like this:

Array
(
    [0] => Array
        (
            [id] => 1
            [productId] => 1
            [storyId] => 1
            [name] => Create a User Story
            [content] => Admin should be able to create user stories.
            [duration] => 1
            [priority] => 0
            [created] => 2010-09-22 17:36:21
            [edited] => 
            [status] => 1
        )

    [1] => Array
        (
            [id] => 4
            [productId] => 1
            [storyId] => 1
            [name] => Bar
            [content] => Xyzzy!
            [duration] => 
            [priority] => 1
            [created] => 2011-02-10 17:50:56
            [edited] => 2011-02-10 17:50:56
            [status] => 1
        )

)

Even though productstorymap has only two rows:

ID  prodcutId   storyID
1   1           1
2   7           4

I'm using MySQL and PHP, if that is relevant.

Upvotes: 3

Views: 26233

Answers (3)

Aivan Monceller
Aivan Monceller

Reputation: 4670

SELECT * 
FROM productstorymap prod
JOIN stories story ON prod.storyId = story.Id
WHERE prod.productId = 1
AND story.status = 1

Upvotes: 3

FrustratedWithFormsDesigner
FrustratedWithFormsDesigner

Reputation: 27486

I think you were forgetting to join them on the common field, which I assume is productstorymap.storyID and stories.id

SELECT map.*, story.*
FROM productstorymap map, stories story
WHERE map.productId=1
     AND story.status=1
     and map.storyID = story.id

Join-syntax can also look like this:

SELECT map.*, story.*
FROM productstorymap map
JOIN stories story on map.storyID = story.id
WHERE map.productId=1
     AND story.status=1;

Upvotes: 5

ACdev
ACdev

Reputation: 62

SELECT map.*, story.*
FROM productstorymap map, stories story
WHERE map.productId=1 AND story.status=1 AND map.storyId = story.Id

This is call a JOIN between table. Look for it

Upvotes: 0

Related Questions