?
Solved

Combine to queries

Posted on 2011-10-10
7
Medium Priority
?
279 Views
Last Modified: 2012-05-12
Hello experts.
I need help to replace  the following two queries with one.
The two tables artikel and artikelersatz are connected with the artikel_main table
where
artikel.art_id = artikel_main.art_id  and
artikelersatz.art_id = artikel_main.art_id

any help?
<cfquery name="qry_inactive_artikel" datasource="#request.dsn#">
SELECT a.uuid,u.Lastname,u.Firstname,u.Emailaddress,g.geschlecht_text2,a.user_id,a.urlrw
FROM artikel a
LEFT JOIN (users u INNER JOIN t_geschlecht g ON (u.geschlecht_id = g.geschlecht_id AND u.langid = g.langid)) ON u.user_id = a.user_id
where a.lastenable < DATE_ADD(NOW(), INTERVAL -25 DAY)
</cfquery>
<cfquery name="qry_inactive_artikel2" datasource="#request.dsn#">
SELECT a.uuid,u.Lastname,u.Firstname,u.Emailaddress,g.geschlecht_text2,a.user_id,a.urlrw
FROM artikelersatz a
LEFT JOIN (users u INNER JOIN t_geschlecht g ON (u.geschlecht_id = g.geschlecht_id AND u.langid = g.langid)) ON u.user_id = a.user_id
where a.lastenable < DATE_ADD(NOW(), INTERVAL -25 DAY)
</cfquery>

Open in new window

0
Comment
Question by:Panos
  • 4
  • 2
7 Comments
 
LVL 24

Expert Comment

by:johanntagle
ID: 36942152
So the expected result will have two uuid, Lastname, Firstname, etc, from artikel and from artikelersatz?  Please clarify.
0
 
LVL 2

Author Comment

by:Panos
ID: 36942165
Hi johanntagle.
A little explanation.
A user (user_id) can have "inserts" in table artikel and in table artikelersatz.
each insert ("art_id") has a uuid field.
I want to select wich inserts from the two tables that belong to the same user are older than 25 days (lastenable < DATE_ADD(NOW(), INTERVAL -25 DAY) and than post the links in ONE email to him.
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 2000 total points
ID: 36942572
Looks like the columns line up, so you could just UNION the two queries together.

When looping, just group by the uuid so you send out one email with multiple items
<cfquery name="qry_inactive_artikel" datasource="#request.dsn#">
SELECT a.uuid,u.Lastname,u.Firstname,u.Emailaddress,g.geschlecht_text2,a.user_id,a.urlrw
FROM artikel a
LEFT JOIN (users u INNER JOIN t_geschlecht g ON (u.geschlecht_id = g.geschlecht_id AND u.langid = g.langid)) ON u.user_id = a.user_id
where a.lastenable < DATE_ADD(NOW(), INTERVAL -25 DAY)
UNION
SELECT a.uuid,u.Lastname,u.Firstname,u.Emailaddress,g.geschlecht_text2,a.user_id,a.urlrw
FROM artikelersatz a
LEFT JOIN (users u INNER JOIN t_geschlecht g ON (u.geschlecht_id = g.geschlecht_id AND u.langid = g.langid)) ON u.user_id = a.user_id
where a.lastenable < DATE_ADD(NOW(), INTERVAL -25 DAY)
</cfquery>

Open in new window

0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 2

Author Comment

by:Panos
ID: 36942994
Hi gdemaria.
The union function is what  i need but i think i have to group this with user_id.
<cfquery name="qry_inactive_artikel" datasource="#request.dsn#">
SELECT a.uuid,u.Lastname,u.Firstname,u.Emailaddress,g.geschlecht_text2,a.user_id,a.urlrw
FROM artikel a
LEFT JOIN (carbay_users u INNER JOIN t_geschlecht g ON (u.geschlecht_id = g.geschlecht_id AND u.langid = g.langid)) ON u.user_id = a.user_id
where a.lastenable < DATE_ADD(NOW(), INTERVAL -25 DAY)
UNION
SELECT a.uuid,u.Lastname,u.Firstname,u.Emailaddress,g.geschlecht_text2,a.user_id,a.urlrw
FROM artikelersatz a
LEFT JOIN (carbay_users u INNER JOIN t_geschlecht g ON (u.geschlecht_id = g.geschlecht_id AND u.langid = g.langid)) ON u.user_id = a.user_id
where a.lastenable < DATE_ADD(NOW(), INTERVAL -25 DAY)
</cfquery>
<!--- output --->
<cfset total = qry_inactive_artikel.recordcount />
Total items:<cfoutput>#total#</cfoutput><br>
<cfoutput group="user_id" query="qry_inactive_artikel">
Email = #Emailaddress#<br>
 <cfoutput>
 <cfif request.use_urlrewrite EQ 'ON'>
 <a href="#request.homeurl#view/#urlrw#">#request.homeurl#view/#urlrw#</a><br>
 <cfelse>
 <a href="#request.homeurl#index.cfm?action=view&amp;art_id=#uuid#"> #request.homeurl#index.cfm?action=view&amp;art_id=#uuid#</a><br>
 </cfif>
 </cfoutput>
</cfoutput>

Open in new window

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 36943094
Yes, group by the user_id, I just looked quickly and thought UUID was the user's primary key...

0
 
LVL 2

Author Comment

by:Panos
ID: 36943400
OK.
Thank you again.
0
 
LVL 2

Author Closing Comment

by:Panos
ID: 36943403
Thank you
regards
panos
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
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