Problem with query and resulting data

Hello,

I have the following issue:

I have a database with the following tables:
- products
- authors
- lu_prod_au (luprod_id and luauth_id)

lu_prod_au is the lookup table. Because any product can have up to 4 authors. Every product_id and entered author_id gets stored in this table.

Now when I use the following query this results in a products list where products with multiple authors are displayed multiple times while each product should only be shown once in the list of products.
Note:. The various authors need to get displayed next to the listed product in stead of the repetition of a product with multiple authors stating a different author next to it each time...

Can anyone tell me how to change the query to make it only show each of the products with mulitple authors only once?

Here is the query:
SELECT * FROM products, lu_prod_au, authors WHERE prod_Id = luprod_id AND luauth_id = auth_Id ORDER BY xxx DESC

I hope someone can help me out with this.

WKR
RV
WitheetAsked:
Who is Participating?
 
Pratima PharandeConnect With a Mentor Commented:
SELECT Image ,prod_Id , GROUP_CONCAT(auth_name ) as Authors FROM products, lu_prod_au, authors WHERE prod_Id = luprod_id AND luauth_id = auth_Id
group by Image ,prod_Id

This will work
GROUP_CONCAT(auth_name )  will give you comma seperated list of all possible authors for that product in one column
0
 
Pratima PharandeCommented:
do you want something like this
give your GROUP_CONCAT(auth_name ) frildname for auth_name



SELECT prod_Id , GROUP_CONCAT(auth_name )FROM products, lu_prod_au, authors WHERE prod_Id = luprod_id AND luauth_id = auth_Id
group by prod_Id
0
 
WitheetAuthor Commented:
TYVM for your quick reply..
I will try this and let you know asap if it did the trick...

wkr rv
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
WitheetAuthor Commented:
Hello Pratima,

This makes the list not have double items... so tnx for that...
But I still need to be able to display each available author for a product next to the product in the list... Now only one author remains.
The list looks has to look like:
- Image of product
- Title of the product
- Author(s) (here the names of other possible authors need to be displayed aswell. If I am correct the recordset now only holds 1 of the 4 possible authors when there is more then 1 author.)

Any suggestions?

TYVM and wkr
RV
0
 
WitheetAuthor Commented:
That does the trick... Now I have one last small detail... Authname in my authors table is divided over 3 columns: authfirstname / authmiddlename / authlastname

I tried the following:
GROUP_CONCAT(authfirstname , authmiddlename , authlastname)
This however results in the the name to be displayed without spaces between first middle and lastname. Also there is no space between the names and the comma. How can I realise this.
EG. LukeSkywalker,HanSolo needs to be: Luke Skywalker, Han Solo

The comma I could probably add a space using str_replace in php (Atleast that's what I would try first) But the spaces between the name parts I can't think of a solution for...

Again tyvm this is exactly what I needed... Hope you can help me fix this last thing..

wkr rv
0
 
WitheetAuthor Commented:
Pratima Don't bother... I have the solution! TYVM for your help.. this helps me a great deal in this issue and understanding more complex queries in general..very grateful for this...

GROUP_CONCAT(authfirstname, ' ',  authmiddlename, ' ', authlastname)

Tanx again

WKR RV
0
 
WitheetAuthor Commented:
Excellent help!
0
 
Pratima PharandeCommented:
try

GROUP_CONCAT(Concat_ws(' ',authfirstname , authmiddlename , authlastname) )
0
 
WitheetAuthor Commented:
I'll be sure to use your last solution instead of mine... looks different and probably better then mine... TY again ... very happy to get this working correct...
0
 
Pratima PharandeCommented:
:)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.