[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Mysql code need help

Posted on 2012-09-12
5
Medium Priority
?
315 Views
Last Modified: 2012-10-14
Hi All,

I use this code

select tt.URL, ifnull((select n.title from node n where n.nid = tt.nid),'=Title is missing=') Title
from
(select
ua.src rr,
substring_index(ua.src, '/', -1) as nid,
substring_index(ua.src, '/', 1) as ptype,
concat('http://www.xyz.com/', ua.dst) URL
from url_alias ua
having nid not in ('feed','contact','track') and ptype in ('node', 'taxonomy')
) tt;

The titles that come along with the URL's a few show wrong.
What could the issue be

i have drupal CMS

thanks
0
Comment
Question by:bsharath
  • 3
  • 2
5 Comments
 
LVL 24

Expert Comment

by:johanntagle
ID: 38393700
Think the main problem is the "having" clause - that should be a where clause.  Also think it's better to have a join instead of sub-selects.  Try:

select tt.URL, ifnull(n.title,'=Title is missing=') Title
from
(  select
    ua.src rr,
    substring_index(ua.src, '/', -1) as nid,
    substring_index(ua.src, '/', 1) as ptype,
    concat('http://www.xyz.com/', ua.dst) URL
    from
    WHERE nid not in ('feed','contact','track') and ptype in ('node', 'taxonomy')
) tt left outer join node n on (n.nid = tt.nid );

Btw, why select for ua.src and ptype if you're not going to use them in the outer SQL?
0
 
LVL 11

Author Comment

by:bsharath
ID: 38396038
Thanks
i get this error

[Window Title]
Error

[Content]
SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE nid not in ('feed','contact','track') and ptype in ('node', 'taxonomy')
)' at line 9

[OK]

Note : THis was given by my dba who is not available now
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 38397053
of course, i somehow deleted the table name between FROM and WHERE in the subquery.  If your DBA is really a DBA he should have seen that ;-)
0
 
LVL 11

Author Comment

by:bsharath
ID: 38404645
Its me who is running them not my dba
Can you tell me which code i need to use?
0
 
LVL 24

Accepted Solution

by:
johanntagle earned 2000 total points
ID: 38404659
just add back "url_alias ua" after the second "from"
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
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…
Suggested Courses
Course of the Month19 days, 14 hours left to enroll

873 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