Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 13 of 13
  1. #1
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,098
    Thanks
    297
    Thanked 12 Times in 12 Posts

    Exclamation query returning multiple rows when only expecting 1?

    Hi All

    I have the following query
    Code:
    SELECT *
    FROM tbl_tradesmen AS t
    LEFT JOIN tbl_tradesmen_trades AS tt ON tt.tradesmenID = t.tradesmenID
    LEFT JOIN tbl_trades AS trade ON trade.tradeID = tt.tradeID
    LEFT JOIN tbl_tradesmen_location AS loc ON loc.tradesmenID = t.tradesmenID
    LEFT JOIN tbl_locations AS l ON l.locationID = loc.locationID
    ORDER BY createdDate DESC
    LIMIT 0 , 10
    i currently one have 1 record in my tbl_tradesmen table but this tradesmen has two trades listed in the tbl_tradesmen_trades table.

    so what i want to happen is for the query to return one record which shows both trades, however with the current query im getting two records returned 1 for each of the trades the tradesmen has

    here is my database layout

    tbl_tradesmen => tradesmenID, tradesmen, tradesmenIMG, tradesmenBio, dateRegistered
    tbl_tradesmen_trades => tradesmenID, tradeID
    tbl_trades => tradeID, tradeName
    tbl_tradesmen_location => tradesmenID, locationID
    tbl_location => locationID, locationName, locationInfo

    and here is the data thats in each table

    tbl_tradesmen
    tradesmenID = 1
    tradesmen = Joe Blogs
    tradesmenIMG = images/profile.jpg
    tradesmenBio = Joe Blogs Bio Here
    dateRegistered = 23/12/2013

    tbl_tradesmen_trades
    tradesmenID = 1
    tradeID = 1

    tradesmenID = 1
    tradeID = 2

    tbl_trades
    tradeID = 1
    tradeName = Aerial / Network Specialist

    tradeID = 2
    tradeName = Carpenter / Joiner

    i've tried grouping the results by tradesmenID but that only returns the first trade and not the second, but it does only return one record.

    any idea how i can return just one record containing both trades?
    any help is greatly appreciated!!

    Luke

    p.s its officially xmas day :O hope you all have a great festive period!!!
    Last edited by LJackson; 12-25-2013 at 11:46 PM.

  • #2
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts
    Code:
    SELECT distinct group_concat(tbl_tradesmen_trades.tradesmenID),group_concat(tbl_tradesmen_trades.tradeID)
    FROM tbl_tradesmen AS t
    LEFT JOIN tbl_tradesmen_trades AS tt ON tt.tradesmenID = t.tradesmenID
    LEFT JOIN tbl_trades AS trade ON trade.tradeID = tt.tradeID
    LEFT JOIN tbl_tradesmen_location AS loc ON loc.tradesmenID = t.tradesmenID
    LEFT JOIN tbl_locations AS l ON l.locationID = loc.locationID
    group by tbl_tradesmen.tradesmenID
    ORDER BY createdDate DESC
    LIMIT 0 , 10
    something like that
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #3
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,098
    Thanks
    297
    Thanked 12 Times in 12 Posts
    thanks for your help mate!

    the above returns this
    Code:
    group_concat(tt.tradesmenID) 	group_concat(tt.tradeID) 	
    1,1 	27,28
    which is half way there, but also need all the info from the other tables like tradesmen name, location etc

    thanks, merry xmas and a happy new year !!!!

  • #4
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,098
    Thanks
    297
    Thanked 12 Times in 12 Posts
    think i've cracked it,

    Code:
    SELECT * , group_concat( tt.tradeID )
    FROM tbl_tradesmen AS t
    LEFT JOIN tbl_tradesmen_trades AS tt ON tt.tradesmenID = t.tradesmenID
    LEFT JOIN tbl_trades AS trade ON trade.tradeID = tt.tradeID
    LEFT JOIN tbl_tradesmen_location AS loc ON loc.tradesmenID = t.tradesmenID
    LEFT JOIN tbl_locations AS l ON l.locationID = loc.locationID
    GROUP BY t.tradesmenID
    ORDER BY createdDate DESC
    LIMIT 0 , 10
    seems to be returning what im after
    thanks for your help mate couldnt of got there without your help!

    Luke

  • #5
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Keep in mind as well that the GROUP_CONCAT is a non-standard SQL function.
    If you want to keep it generic, you should use multiple records and let your language deal with the assembly. Otherwise, you'll find if you ever move to a different dbms, you'll need to re-write that block of sql code.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #6
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,098
    Thanks
    297
    Thanked 12 Times in 12 Posts
    oh ok mate, its very unlikely that i will be using any other dbms but who knows, how would you go about assembling it in php?

    would you add it to an array, bearing in mind there could be 20,000+ records pulled from the database query, would this be too? not sure of any other way around this?

    any ideas please
    Thanks

    Luke

  • #7
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts
    Don't worry, at lest for oracle I seen custom made group_concat stored proc/function.
    And certainly you can do it yourself for any db.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #8
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,098
    Thanks
    297
    Thanked 12 Times in 12 Posts
    any idea as to why this query
    Code:
    SELECT COUNT(*) , group_concat( tt.tradeID ) AS tradeArray 
    FROM tbl_tradesmen AS t 
    LEFT JOIN tbl_tradesmen_trades AS tt ON tt.tradesmenID = t.tradesmenID 
    LEFT JOIN tbl_trades AS trade ON trade.tradeID = tt.tradeID 
    LEFT JOIN tbl_tradesmen_location AS loc ON loc.tradesmenID = t.tradesmenID 
    LEFT JOIN tbl_locations AS l ON l.locationID = loc.locationID 
    GROUP BY t.tradesmenID 
    ORDER BY createdDate DESC
    returns a count of 2

    where as this query returns a count of 1
    Code:
    SELECT * , group_concat( tt.tradeID ) AS tradeArray
    FROM tbl_tradesmen AS t
    LEFT JOIN tbl_tradesmen_trades AS tt ON tt.tradesmenID = t.tradesmenID
    LEFT JOIN tbl_trades AS trade ON trade.tradeID = tt.tradeID
    LEFT JOIN tbl_tradesmen_location AS loc ON loc.tradesmenID = t.tradesmenID
    LEFT JOIN tbl_locations AS l ON l.locationID = loc.locationID
    GROUP BY t.tradesmenID 
    ORDER BY createdDate DESC
    shouldnt the first query should return a count of 1 also?

    thanks
    Last edited by LJackson; 12-30-2013 at 12:53 PM.

  • #9
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts
    The second does not return 'anything' coz there is no "COUNT" in it.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #10
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,098
    Thanks
    297
    Thanked 12 Times in 12 Posts
    but the second query returns 1 record in my database when ran in phpmyadmin, but the value of count in the first query is 2.
    As there is only one record why is it returning 2? seems strange

    thanks

  • #11
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts
    To rephraze:
    1. first query returns count 2 as a result in one of fields (one recod only)
    2. second query returns 1 record
    And you have 2 records in db.


    That is because count is grupped too,
    with command GROUP BY t.tradesmenID.
    That is the whole point having group by.
    Forget for a second on group_concat, means nothing at understanding thing.

    The first query returns one record because of grupping of 2 record into one, if you have forinstance twice t.tradesmenID = 1 in db

    Code:
    t.tradesmenID  group_concat   count
    1                   1,1                 2
    Means you hawe 2 records with t.tradesmenID = 1

    The second query returns 1 record, also expected, coz you are grouping 2 records into one.

    will give you simle sample with 2 fields
    f1 f2
    1 b
    1 c
    1 d

    you want to see how many record are for 1
    select count(*),group concat(f2) from t
    group by f1
    -->
    3 a,b,c

    want to see all f2 values within 1

    select f1,group concat(f2) from t
    group by f1
    --> 1 a,b,c

    BTW - php admin is something that I suspect to be not good
    to access db with separate client I recomend excelent: http://www.heidisql.com/download.php
    Always when not sure what is going on in db/query create small table with small sample data
    Last edited by BubikolRamios; 12-30-2013 at 03:41 PM.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #12
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,098
    Thanks
    297
    Thanked 12 Times in 12 Posts
    i understand what you are saying what i am trying to do is to display the total number of tradesmen stored in my database on my website.

    So i currently have 1 tradesmen in my database but he has 2 trades

    but when using my count(*) query above its returning 2 tradesmen or records. so my page says i have two tradesmen listed on my site where as i actually only have 1.

    do i need to run another query just for the number of tradesmen or can i modify this one to return the correct number?

    i've tried counting just the t.tradesmenID like so

    Code:
    SELECT COUNT( t.tradesmenID ) , group_concat( tt.tradeID ) AS tradeArray
    FROM tbl_tradesmen AS t
    LEFT JOIN tbl_tradesmen_trades AS tt ON tt.tradesmenID = t.tradesmenID
    LEFT JOIN tbl_trades AS trade ON trade.tradeID = tt.tradeID
    LEFT JOIN tbl_tradesmen_location AS loc ON loc.tradesmenID = t.tradesmenID
    LEFT JOIN tbl_locations AS l ON l.locationID = loc.locationID
    GROUP BY t.tradesmenID
    but that still returns 2. not sure how to get this query to count just the number of tradesmen. should return a count of 1

    thanks, appreciate the help

  • #13
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts
    Having only on tradesman count(distinct t.tradesmenID) would work, but at more tradesmans not.

    In order to get that (count distinct of all tradesmans) you need separate query.

    If you want only one trip to db, store both queries to stored procedure(can return resultset + additional out parameters).


    Ok, could write a novel on subqueries, user variables, .... but I would do upper way, doh in time stored procedures are getting piled up.

    If not familiar with stored procedures you can combine both queries with union, that is what I would do if I would be lazy, like

    SELECT count(distinct t.tradesmenID) as c,''AS tradeArray from tbl_tradesmen
    UNION
    SELECT COUNT( t.tradesmenID ) as c, group_concat( tt.tradeID ) AS tradeArray
    from ...

    And when it comes back to your app you know first resultset record has your count.
    Ok I seen so called professionals, they loved to get big recordset to client, using all mighty (persistance api), and then doing sum on client all mighty object oriented datasets grrrr (-:
    Last edited by BubikolRamios; 12-30-2013 at 10:56 PM.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search


  •  

    Posting Permissions

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