-1

i have a table called suggestion that has columns like title , price , .. and a user_id as fk and every suggestion has one or more than one pics that are stored in suggestion_images table now if i want to get one single suggestion with it's pictures i use this query 

SELECT  sg.title , sg.price,sg.detail , sg.created_at ,
        user.name , user.phone , sg_image.url
    from sg
    join user on user.id = 16
    JOIN sg_image on sg_image.sg_id=sg.id
    where sg.id = 13 ;

i join suggestion table and user_table and suggestion_images tables my problem is if the suggestion has 3 image it will make 3 row,s with the same data just Different images .. so is there any way to get one single suggestion and it's pictures in one single row ? or should i query them separately ? or maybe my database disign is wrong ?

edit

this is my suggestion table

id | user_id | title | price | detail | complete | active | track_code | created_at

and this my suggestion_images table

id | url | sg_id

i want to get a suggestion withs it's images that in a row lie

id | title | price | detail | created_at | url_1 | url_2 | ....

Rick James
  • 135,179
  • 13
  • 127
  • 222
hamiid reza Gholami
  • 115
  • 2
  • 10
  • 2
    What database design? Please show the table create statements and some sample data as insert statements for data, that 1) shows your issue and 2) what you expect as output. If I want to help you I don't want to be designing tables and filling them with test data, from your description. You have all this already? I don't want a diagram - I want code I can import, copy and paste is fine, and test :) I want to spend no more than 5 minutes creating tables and loading them from your code here. I have an SQL IDE and a complete test setup. It is what I do when answering... – Ryan Vincent Mar 03 '17 at 22:17
  • @RyanVincent dude i updated my question hope it get.s more clear – hamiid reza Gholami Mar 03 '17 at 22:26
  • 2
    Can I copy your table definition an It will create a table in Mysql? No. Can I copy your insert of test data into that table? no. Do I know what the expect results of the query will be? no. You want to help? Or make us spend time creating tables and making test data before we can start to help you. In case it isn't clear. Most of us have to run the queries and we get them wrong. Then we iterate until we get them to do what is required. Yes, We go through the exact same process you do. Hopefully, quicker? – Ryan Vincent Mar 03 '17 at 22:39
  • 2
    If you provide all this as part of your original question then people can have fun fixing it. :) Seriously, I can copy table create and insert statements that you provide while having a cup of coffee. I then try and fix the your query to try and match your expected results that you provide. Except you don't provide any useful test data or expected results? How do you test your own queries? – Ryan Vincent Mar 03 '17 at 23:01
  • 1
    see: http://stackoverflow.com/a/33806675/3184785 for create table and insert test data statements. Yes, you can test it by just looking at the information provided? Now, can you do that for your question? Would it help us? – Ryan Vincent Mar 03 '17 at 23:20
  • 1
    tanx for your comments . i think my question is a simple question for who are expert in mysql . and they will get it what i want , any way tanx for your advices – hamiid reza Gholami Mar 03 '17 at 23:48
  • Unfortunately, the question isn't worded very clearly. Sure, the table structure is (vaguely) added with the attempted query. Now that in itself wouldn't have been a problem, provided DDL with some data to test with was included as well. At times even a little clue on the "expected" output - just as done in the "edit" later - goes a long way. I get a feeling that this could be of your interest: http://stackoverflow.com/q/12004603/2298301. Finally, you might want to speculate whether the tone of the comment above might have discouraged the person responding to you from potentially helping you. – Dhruv Saxena Mar 04 '17 at 21:41

1 Answers1

1
SELECT  sg.title , sg.price,sg.detail , sg.created_at ,
        user.name , user.phone ,
        ( SELECT GROUP_CONCAT(url)
             FROM sg_image on sg_id=sg.id )
    from sg
    join user on user.id = 16
    where sg.id = 13 ;

(Your JOIN to user is rather strange.)

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • tanx i acept this answer but i have 2 question . what.s wrong with user join do you think ? and group contact make them all in on row with a (,) between them but how can i split them ? – hamiid reza Gholami Mar 05 '17 at 19:40
  • 1
    `GROUP_CONCAT` gives you a wide column. Formatting is the responsibility of the app, not MySQL, so you need to split on comma. – Rick James Mar 05 '17 at 21:34
  • 1
    The `JOIN user` is strange because it is a "cross join". "All users with id 16 times all sg's with id 13." – Rick James Mar 05 '17 at 21:36