4 Replies - 359 Views - Last Post: 22 February 2019 - 08:46 AM

#1 lastpeony   User is offline

  • D.I.C Head

Reputation: -1
  • View blog
  • Posts: 75
  • Joined: 14-October 17

Simple SQL Join Question

Posted 24 January 2019 - 12:09 PM

Lets say that i have 2 tables:
1st has following columns:
products
--------
id   name        price
1  someproduct  99

2nd
productimages
-----------
productId img
1          someimgurl
1          someimgurl2


I would like to get name,price and images of product 1.
SELECT products.name, products.price, productimages.img  FROM products INNER JOIN productimages WHERE products.id=1

This query gives me following result:
[ {
    name: 'someproduct',
    price: 99,
    img:
     'someimg' },
   {
    name: 'someproduct',
    price: 99,
    img:
     'someimgurl2' }
]


As you see name and price are repeated.What i am trying to get as result is this:
[ {
    name: 'someproduct',
    price: 99,
    img:[
     'someimgurl','someimgurl2'] },
]


This post has been edited by lastpeony: 24 January 2019 - 12:58 PM


Is This A Good Question/Topic? 0
  • +

Replies To: Simple SQL Join Question

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15058
  • View blog
  • Posts: 60,124
  • Joined: 12-June 08

Re: Simple SQL Join Question

Posted 24 January 2019 - 12:27 PM

Perhaps just a left join.

.. but honestly a simple join will not double up the columns of data into one cell.

MSSQL has a 'stuff' function perhaps look at something similar with mysql.

https://www.mssqltip...ql-server-data/
Was This Post Helpful? 1
  • +
  • -

#3 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6795
  • View blog
  • Posts: 28,082
  • Joined: 12-December 12

Re: Simple SQL Join Question

Posted 25 January 2019 - 02:13 AM

Your join syntax is incomplete. INNER JOIN .. ON. You could discover if/that it isn't working by adding more records.

http://www.mysqltuto...inner-join.aspx

You could explore 'stuff' or equivalent, although it depends what you need to do with the output. That is, is it something that should be handled in SQL or something that external code can handle appropriately.
Was This Post Helpful? 0
  • +
  • -

#4 lastpeony   User is offline

  • D.I.C Head

Reputation: -1
  • View blog
  • Posts: 75
  • Joined: 14-October 17

Re: Simple SQL Join Question

Posted 25 January 2019 - 07:05 AM

View Postmodi123_1, on 24 January 2019 - 12:27 PM, said:

Perhaps just a left join.

.. but honestly a simple join will not double up the columns of data into one cell.

MSSQL has a 'stuff' function perhaps look at something similar with mysql.

https://www.mssqltip...ql-server-data/


thanks there is concat function i solved my problem

This post has been edited by lastpeony: 25 January 2019 - 07:05 AM

Was This Post Helpful? 0
  • +
  • -

#5 ajwsurfer   User is offline

  • D.I.C Regular
  • member icon

Reputation: 21
  • View blog
  • Posts: 385
  • Joined: 24-October 06

Re: Simple SQL Join Question

Posted 22 February 2019 - 08:46 AM

Here is a concat statement used with an embedded "case" and "distinct" statements, a "left join" would definitely not solve this:

...
,GROUP_CONCAT(DISTINCT CASE WHEN LENGTH(`Project Sponsor`) > 1 THEN `Project Sponsor` ELSE NULL END  SEPARATOR ', ') `Sponsor`
....


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1