?
Solved

Problem with query and resulting data

Posted on 2011-09-30
10
Medium Priority
?
307 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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 2000 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

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

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

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
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…
Suggested Courses
Course of the Month16 days, 11 hours left to enroll

862 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