Solved

Problem with query and resulting data

Posted on 2011-09-30
10
300 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:Witheet
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 36890848
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
 

Author Comment

by:Witheet
ID: 36890875
TYVM for your quick reply..
I will try this and let you know asap if it did the trick...

wkr rv
0
 

Author Comment

by:Witheet
ID: 36890932
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 500 total points
ID: 36890956
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
 

Author Comment

by:Witheet
ID: 36891028
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
 

Author Comment

by:Witheet
ID: 36891060
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
 

Author Closing Comment

by:Witheet
ID: 36891062
Excellent help!
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 36891067
try

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

Author Comment

by:Witheet
ID: 36891082
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
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 36891095
:)
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
php subtract from date 2 29
Survey branching tutorial 11 44
Special characters in a TCPDF 4 27
&& operators not performing required logic 8 18
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question