Hoboy…

“So it needs to be one query?”

“Yes.”

select concat(con.last_name,’, ‘,con.first_name) as ‘Contact Name’, cl.tablename as ‘Relationship’, com.company_name as Name, ifnull(com.company_type,’ ‘) as Other
from contacts con, contactlink cl, companies com where con.contact_id = cl.contact_id and cl.tablename=’companies’ and cl.table_id = com.company_id
union
select concat(con.last_name,’, ‘,con.first_name) as ‘Contact Name’, cl.tablename as ‘Relationship’, p.title as Name, ifnull(concat(p.opened_date,’ – ‘,p.closed_date),’ ‘) as Other
from contacts con, contactlink cl, project p where con.contact_id = cl.contact_id
and cl.tablename=’project’ and cl.table_id = p.project_id
union
select concat(con.last_name,’, ‘,con.first_name) as ‘Contact Name’, cl.tablename as ‘Relationship’, concat(‘Reactor: ‘,r.reactor_id) as Name, ‘ ‘ as Other from contacts con, contactlink cl, reactors r where con.contact_id = cl.contact_id and cl.tablename=’reactors’ and cl.table_id = r.reactor_id
union
select concat(con.last_name,’, ‘,con.first_name) as ‘Contact Name’, cl.tablename as ‘Relationship’, s.name as Name, ifnull(s.description,’ ‘) as Other from contacts con, contactlink cl, sites s where con.contact_id = cl.contact_id and cl.tablename=’sites’ and cl.table_id = s.site_id
union
select concat(con.last_name,’, ‘,con.first_name) as ‘Contact Name’, cl.tablename as ‘Relationship’, v.name as Name, ifnull(concat(v.city,’, ‘,v.state),’ ‘) as Other
from contacts con, contactlink cl, venues v where con.contact_id = cl.contact_id and cl.tablename=’venues’ and cl.table_id = v.venue_id
union
select concat(con.last_name,’, ‘,con.first_name) as ‘Contact Name’, ‘NONE!’ as Relationship, ‘ ‘ as Name, ‘ ‘ as Other from contacts con left join contactlink cl on con.contact_id=cl.contact_id
where cl.contactlink_id is null order by ‘Contact Name’, Relationship, Name, Other

I can’t believe I wrote that. I feel dirty now.

At least it doesn’t have a “having” clause in it too.

15 Responses to “Hoboy…”

  1. momentrabbit says:

    (boggle)

    (bows and strews flower petals at your feet, mumbling “Am not worthy!”)

  2. krdbuni says:

    Familiar territory

    At my job, if a particular monitor requires a script, it requires a Change Board request. If it can be typed in at the command line, it doesn’t. As a result, I have become unpleasantly skilled at sed, awk, find and grep.

    Kristy

  3. That’s hot…

    SELECT*FROM((SELECT messages.id,messages.originid AS cid,users.level,comments.username,comments.type,comments.originid,comments.parenttype,comments.parentid,comments.body,comments.tags,comments.datetime,works.title FROM comments,users,messages,works WHERE(users.username=comments.username)AND(messages.originid=comments.id)AND(comments.originid=works.id)AND messages.type=’comm_w’ AND messages.userid=’$user[id]’)UNION(SELECT messages.id,messages.originid AS cid,users.level,comments.username,comments.type,comments.originid,comments.parenttype,comments.parentid,comments.body,comments.tags,comments.datetime,comments.originid FROM comments,users,messages,works WHERE(users.username=comments.username)AND(messages.originid=comments.id)AND messages.type=’comm_r’ AND messages.userid=’$user[id]’)UNION(SELECT messages.id,messages.originid AS cid,users.level,comments.username,comments.type,comments.originid,comments.parenttype,comments.parentid,comments.body,comments.tags,comments.datetime,comments.originid FROM comments,users,messages WHERE(users.username=comments.username)AND(messages.originid=comments.id)AND messages.type=’comm_u’ AND messages.userid=’$user[id]’)UNION(SELECT messages.id,messages.originid AS cid,users.level,comments.username,comments.type,comments.originid,comments.parenttype,comments.parentid,comments.body,comments.tags,comments.datetime,blogs.title FROM comments,users,messages,blogs WHERE(users.username=comments.username)AND(messages.originid=comments.id)AND(comments.originid=blogs.id)AND messages.type=’comm_b’ AND messages.userid=’$user[id]’))AS cock GROUP BY cock.originid ORDER BY cock.originid DESC LIMIT 32;

  4. shaterri says:

    I wholeheartedly recommend firing this one off to The Daily WTF. 🙂 (And while you’re at it, check out the attempt at a proper RFC2822-compliant regexp for recognizing e-mail addresses, if you haven’t seen it before. Terrifying stuff.)

  5. krin_o_o_ says:

    SQL Geek

    or SQEEK for short!

    Welcome to my job.

    I right the guts of our companies relational (SQL) query generation system.

    I don’t write the queries anymore, I just write the software that reads the data models that other people write and that softwear then writes the queries.

    DELEGATION! Briliant!

    Oh, if you need to shorten that any, you don’t really need any of the AS identifiers after the first query in a union.
    They are ignored outside the scope of the individual query.

    Also, I think each subquery in this generates a unique result set based on Relationship, but if you expand this and find that duplicate rows from seperate subqueries are being dropped then you will want to use “UNION ALL” rather than “UNION”.

    select
    concat(con.last_name,', ',con.first_name) as 'Contact Name',
    cl.tablename as 'Relationship',
    com.company_name as Name,
    ifnull(com.company_type,' ') as Other
    from
    contacts con,
    contactlink cl,
    companies com
    where
    con.contact_id = cl.contact_id and
    cl.tablename='companies' and
    cl.table_id = com.company_id
    union
    select
    concat(con.last_name,', ',con.first_name) as 'Contact Name',
    cl.tablename as 'Relationship',
    p.title as Name,
    ifnull(concat(p.opened_date,' - ',p.closed_date),' ') as Other
    from
    contacts con,
    contactlink cl,
    project p
    where
    con.contact_id = cl.contact_id and
    cl.tablename='project' and
    cl.table_id = p.project_id
    union
    select
    concat(con.last_name,', ',con.first_name) as 'Contact Name',
    cl.tablename as 'Relationship',
    concat('Reactor: ',r.reactor_id) as Name,
    ' ' as Other
    from
    contacts con,
    contactlink cl,
    reactors r
    where
    con.contact_id = cl.contact_id and
    cl.tablename='reactors' and
    cl.table_id = r.reactor_id
    union
    select
    concat(con.last_name,', ',con.first_name) as 'Contact Name',
    cl.tablename as 'Relationship',
    s.name as Name,
    ifnull(s.description,' ') as Other
    from
    contacts con,
    contactlink cl,
    sites s
    where
    con.contact_id = cl.contact_id and
    cl.tablename='sites' and
    cl.table_id = s.site_id
    union
    select
    concat(con.last_name,', ',con.first_name) as 'Contact Name',
    cl.tablename as 'Relationship',
    v.name as Name,
    ifnull(concat(v.city,', ',v.state),' ') as Other
    from
    contacts con,
    contactlink cl,
    venues v
    where
    con.contact_id = cl.contact_id and
    cl.tablename='venues' and
    cl.table_id = v.venue_id
    union
    select
    concat(con.last_name,', ',con.first_name) as 'Contact Name',
    'NONE!' as Relationship,
    ' ' as Name,
    ' ' as Other
    from
    contacts con
    left join
    contactlink cl
    on
    con.contact_id=cl.contact_id
    where
    cl.contactlink_id is null
    order by
    'Contact Name',
    Relationship,
    Name,
    Other
    
    
    		
    				
  6. krin_o_o_ says:

    No! Don’t do it BigTig!

    IT’S A TRAP!

    I lost all but my last SAN point trying to read and understand the overlapping RFC’s for that last year.

    Some things man (or woman) was not meant to know!

    – Krin

  7. krin_o_o_ says:

    Blink…

    Blink…

    “)AS cock” ???

    having a bit of fun skinny dipping in the code pool are we?

    – Krin ^ _ ^

  8. Phil says:

    Yeah. I’ve got the “Mastering Regex in Perl” book that has that in the appendix. Frikking scary.

  9. Phil says:

    Re: SQL Geek

    Yeah. I know about the naming being unecessary after the first query and the deduping effect of union. I had to write this in a hurry so there was a lot of copy and paste as you can see 🙂

    That being said, I still feel dirty.

  10. Phil says:

    Re: Familiar territory

    Yeah. We have an interesting SSHing and mysql security system for this company. It’s not uncomming to send a complex sed/awk/find/mysql command over ssh -C and then turn around and manipulate it a half dozen more times.

    It’s amazing what you can do with shell.

  11. hehehe! Not like anybody will ever see the code, right?

    I write programs like dirty smut novels…

  12. krin_o_o_ says:

    Yes definitely

    “AS cock GROUP By cock.”

    heh.

    – Krin

  13. krin_o_o_ says:

    Re: SQL Geek

    Don’t worry.

    It doesn’t hurt so much after the first few times.

    After a while, you might even start to like it!

    – Krin ^ _ ^

  14. krin_o_o_ says:

    Re: Familiar territory

    PERL!

    All the power of sed, awk, find, grep, and pipe rolled together with a heaping big pile of regexp and some brand new OOP nomenclature silliness to make it all shiney and gnu!

    Don’t leave home without it!

    – Krin

  15. krdbuni says:

    Re: Familiar territory

    Anything I can’t do in shell commands, or in “perl -e”, I do in a perl script, but at that point, I’m “building a script” and I have to go through change board. It’s a weird artifact of our SOX compliancy rules that we can execute arbitrary commands through our application, and those are okay, but if we have to build a script, even if the application calls the script, then it’s a code change and requires a lot of bureaucracy to adjust. Thus, sed, awk, find, grep, and pipe; but not perl if I can avoid it. =>,< =

    Kristy

Leave a Response