Solved

Problem with query and resulting data

Posted on 2011-09-30
10
298 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
  • 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to count occurrences of each item in an array.

772 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