Combine to queries
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?
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>
johanntagle
So the expected result will have two uuid, Lastname, Firstname, etc, from artikel and from artikelersatz? Please clarify.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi gdemaria.
The union function is what i need but i think i have to group this with user_id.
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&art_id=#uuid#"> #request.homeurl#index.cfm?action=view&art_id=#uuid#</a><br>
</cfif>
</cfoutput>
</cfoutput>
Yes, group by the user_id, I just looked quickly and thought UUID was the user's primary key...
ASKER
OK.
Thank you again.
Thank you again.
ASKER
Thank you
regards
panos
regards
panos