BabyFur Denny posted:

Hi, what tools do you use for mySQL clients?
Most of our work day is spent writing Queries, either live or in stored procedures. We're currently using SQL Explorer, an Eclipse plugin, which works nicely with our shared svn repository of SQL scripts, which we imported as project in Eclipse using SVNKit.
The issue we have is that Eclipse/ SQL Explorer crashes a lot whenever we click the wrong button at the wrong time and it gets really frustrating because usually quite some queries/code are just gone.

I've been looking at other tools, but I haven't found one yet that offers such a nice integration of browsing/committing/updating a svn repository.
At this point we would also easily consider a commercial solution.

Any recommendations here?

Anaxandrides posted:

The EXPLAIN explains it all about your other table. Those indexes you're making aren't doing the trick for this thing -- SQL chose to use the PK for mdl_user rather than a specific index that includes those columns, and your other index (newindex1) isn't being used either. 99% bet that it's not being used because the column you're using as a join predicate-cum-WHERE clause isn't included in the index.

Replace CREATE INDEX newindex1 ON mdl_course ( shortname, startdate ); with CREATE INDEX IX_IDStartShortName ON mdl_course ( id, startdate, shortname ); and you should see some improvement. Similarly, CREATE INDEX IX_IDName ON mdl_user ( id, firstname, lastname ); should help. I'd be surprised if it didn't.

Sulla-Marius 88 posted:

code:

SELECT c.id, 
       a.id             AS userid, 
       a.firstname, 
       a.lastname, 
       d.shortname, 
       d.startdate, 
       d.id             AS courseid, 
       cc.timecompleted AS status, 
       cc.timestarted 
FROM   mdl_user a, 
       mdl_enrol b, 
       mdl_user_enrolments c, 
       mdl_course d 
       LEFT JOIN mdl_course_completions cc 
              ON cc.course = d.id 
WHERE  a.id = cc.userid 
       AND a.id = c.userid 
       AND b.id = c.enrolid 
       AND b.courseid = d.id 
ORDER  BY d.shortname, 
          d.startdate, 
          a.lastname, 
          a.firstname 
LIMIT  0, 100; 

kloa posted:

My DBA experience since graduating college has been working as a Prod DBA (2 years) and I haven't really gotten into SQL development yet. Is there a recommended version control add-on for MS-SQL? Preferably free as we're mostly SaaS anyways and I don't need anything that robust, just for personal learning.

Jethro posted:

Are you left joining course completions (i.e. you want all courses and the users enrolled in them, regardless of whether or not the user completed the course), or are you inner joining it? Putting a.id = cc.userid means this will behave as an inner join despite the left outer join above.

If you want to use a left join, do this:

the posted:

I need to grab a bunch of entries, which method is faster?

a. Grab entire list A, Grab entire list B, filter out on my end

b. Grab entire list A, Grab what I need from list B by doing a query on each entry of list A

I'm trying to run b right now, and it's taking forever. I'm basically doing something like this:

code:

SELECT Info FROM List WHERE Id = [id from row in list A]

Over the 20,000 or so rows in list A

Malcolm XML posted:

Smells like a join to me

Malcolm XML posted:

Smells like a join to me

the posted:

Go on....

the posted:

You can view my history, but I'm doing queries in Salesforce using Python. The SQL queries are basically no different, but they're embedded in Python code.

I'm grabbing one list with the following:

Python code:

opp_query = "SELECT AccountId, Id, StageName FROM Opportunity 
WHERE RecordType.Name = \'FD - Buying\'"
query_opps = svc.query(opp_query)

Then doing some stuff to it, and then doing a second query with:

Python code:

for row in filtered_opps:
	ocr_query = "SELECT ContactId FROM OpportunityContactRole WHERE IsPrimary = TRUE 
AND OpportunityId = " + '\'' + row[1] + '\''
	query = svc.query(ocr_query)

Where you can see here, I'm making about 20,000 individual queries (the size of filtered_opps).

the posted:

I have this exact line:

opp_query = "SELECT O.AccountId, O.Id, O.StageName, R.ContactId FROM Opportunity AS O JOIN OpportunityContactRole AS R ON R.OpportunityId = O.Id WHERE RecordType.Name = \'FD - Buying\' AND R.IsPrimary = TRUE"

And I'm getting an error:

code:

SoapFaultError: 'MALFORMED_QUERY' 'MALFORMED_QUERY: R.ContactId FROM 
Opportunity AS O JOIN OpportunityContactRole AS ^ ERROR at Row:1:Column:73 
unexpected token: JOIN'

So, it looks like there's an issue with the JOIN command?

edit: If I'm supposed to put it on multiple lines, I'm not sure how I can format that in Python.

the posted:

fletcher would know how to do this.

According to that page, I can link by simply going Blah.blah, but I'm getting errors when doing it.

For example, every Opportunity has an OpportunityContactRole associated with it, but I get errors when trying to reference that:

opp_query = "SELECT AccountId, Id, Stagename, OpportunityContactRole.ContactId FROM Opportunity WHERE RecordType.Name = \'FD - Buying\'"

Error:

SoapFaultError: 'INVALID_FIELD' "INVALID_FIELD: SELECT AccountId, Id, Stagename, OpportunityContactRole.ContactId ^ ERROR at Row:1:Column:34 Didn't understand relationship 'OpportunityContactRole' in field path. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names."

the posted:

You can view my history, but I'm doing queries in Salesforce using Python. The SQL queries are basically no different, but they're embedded in Python code.

I'm grabbing one list with the following:

Python code:

opp_query = "SELECT AccountId, Id, StageName FROM Opportunity 
WHERE RecordType.Name = \'FD - Buying\'"
query_opps = svc.query(opp_query)

Then doing some stuff to it, and then doing a second query with:

Python code:

for row in filtered_opps:
	ocr_query = "SELECT ContactId FROM OpportunityContactRole WHERE IsPrimary = TRUE 
AND OpportunityId = " + '\'' + row[1] + '\''
	query = svc.query(ocr_query)

Where you can see here, I'm making about 20,000 individual queries (the size of filtered_opps).

the posted:

fletcher would know how to do this.

According to that page, I can link by simply going Blah.blah, but I'm getting errors when doing it.

For example, every Opportunity has an OpportunityContactRole associated with it, but I get errors when trying to reference that:

opp_query = "SELECT AccountId, Id, Stagename, OpportunityContactRole.ContactId FROM Opportunity WHERE RecordType.Name = \'FD - Buying\'"

Error:

SoapFaultError: 'INVALID_FIELD' "INVALID_FIELD: SELECT AccountId, Id, Stagename, OpportunityContactRole.ContactId ^ ERROR at Row:1:Column:34 Didn't understand relationship 'OpportunityContactRole' in field path. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names."

PapFinn posted:

I'm pretty much a beginner with SQL...

I need to select all the terminated records for the calender year, but exclude people who have at least one active job code. Some people have multiple jobs, usually with matching employee id's but sometimes not.

Sedro posted:

...

Jethro posted:

code:

WHERE legend =1 AND final_ed =0

WHERE legend <> 1 AND final_ed <> 0

WHERE legend <> 1 AND final_ed = 0

?

Fluue posted:

So there's no way to do this query without making 3 separate ones?

Jethro posted:

I think I mostly just didn't understand what you were asking.
If you want all three scenarios at once, then you'd do:

code:

WHERE ((legend =1 AND final_ed =0)
      OR (legend <> 1 AND final_ed <> 0)
      OR (legend <> 1 AND final_ed = 0))

though the three conditions you want could also be written as:

code:

WHERE (legend <> 1 OR final_ed =0)

Fluue posted:

Sorry for not being clear. I was a little vague on this, but the queries that do not say that final_ed must be '0' should also include rows that have final_ed as '1' (By this I mean, if we don't want only rows with the 0 attribute in final_ed, then we want both 0 and 1)

Since I can't do a wildcard on this (without using a LIKE, which leads to even bigger issues when dealing with INT) am I left dynamically building this query in my script before sending it to the DB?

e: it'd probably be easier to split legend off into another table and just do a join, right?

Fluue posted: