Results 1 to 3 of 3

Thread: Help with MySQL join

  1. #1
    Join Date
    Aug 2005
    Posts
    518
    Rep Power
    0

    Default Help with MySQL join

    i need to do a single query against two table as follow.


    Select item.a, item.b, item.c, count(place.d) as item_count where from item left outer join place on (item.id = place.id) where location='1';

    not the query runs just fine, how my result is not what i want. given that table place will have multiple entry for a, i want to count the amount and display it as one in a single table.

    The result i am looking a table with multiple items and a count of the id as it appears in place

    my issue is that when i run the query i only get one result back not the list, event thought my items table have multiple items and my place table is blank....

    can anyone help????
    Last edited by Overkill; Mar 25, 2013 at 03:16 PM.
    Why fight Information Technology when you can outsource IT

  2. #2
    Join Date
    May 2004
    Posts
    530
    Rep Power
    0

    Default

    Try adding a group by for the column that will make it un unique example item.id
    "...men are that they might have joy."
    Nephi

  3. #3
    Join Date
    Mar 2005
    Posts
    161
    Rep Power
    0

    Default

    Quote Originally Posted by Overkill View Post
    i need to do a single query against two table as follow.


    Select item.a, item.b, item.c, count(place.d) as item_count where from item left outer join place on (item.id = place.id) where location='1';

    not the query runs just fine, how my result is not what i want. given that table place will have multiple entry for a, i want to count the amount and display it as one in a single table.

    The result i am looking a table with multiple items and a count of the id as it appears in place

    my issue is that when i run the query i only get one result back not the list, event thought my items table have multiple items and my place table is blank....

    can anyone help????
    Why your question stated so complexly. Next time use real world examples bro. But like the other guy said use a group by clause to group your items like so

    Select item.a, item.b, item.c, count(place.d) as item_count
    from item
    left outer join place
    on (item.id = place.id)
    where location='1'
    group by item.a, item.b, item.c

    so each data row will look like this
    | item a | item b | item c | item_count
    | Nokia | Lumia | 810 | 5

    P.S. try indenting your code too, makes it so much easier to read
    you could throw in nolock on the table names to prevent lockups, depending on how long ur query takes to run, also alias for easier coding... just saying
    Last edited by Wallizzle; Mar 27, 2013 at 02:11 AM.
    Owned:
    >> Samsung Galaxy Note 3; LG Optimus G; Nexus 4; Samsung Galaxy Ace Duos
    << Microsoft Surface RT; Dell Venue 8; Asus Transformer

Posting Permissions

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