#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    8
    Rep Power
    0

    New to SQL need help combining queries


    I've tried looking at MySQL documentation but I'm too green to translate it effectively. Could I please have some help combining these two queries into one query? I would appreciate it immensely.

    SELECT USERID, FNAME, LNAME
    FROM TBL_USER_BASIC
    WHERE USERCODE_CONCAT LIKE '%.gasoh';

    SELECT ScheduleID, AssignCodeID, AssignCodeName
    FROM TBL_SCHED_ASSIGNCODE;

    Thank you!
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    combine how? I don't see any relation between those two tables. If there is one, please tell us what it is.

    Just in case there is a misunderstanding: You cannot "combine" any two queries. What you can do is join tables, which are in some way related (like an "orders" table to a "customers" table).
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    8
    Rep Power
    0

    This is my problem: I'm green


    I've been thrown into a job quite haphazardly, the expectations are high, the stress is high, and my knowledge is very low, so I apologize. I've only been using MySql for four days.

    There's no relationship between the tables other than they are tied to the same front end piece of software. I want to return all results from the columns I listed from TBL_USER_BASIC and all the results from the columns I listed from TBL_SCHED_ASSIGNCODE

    3 columns will probably be longer than the other 3 columns, I don't even know if that's a possible thing. Like I said ... I'm a MySQL moron at this point.

    The LIKE statement is because I only want results that match that wildcard for the 3 columns from TBL_USER_BASIC.

    I hope I'm being clearer, I'm genuinely sorry if I'm not.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,170
    Rep Power
    4274
    Originally Posted by tannercampbell
    Could I please have some help combining these two queries into one query?
    the best advice i can give you is ~not~ to combine them, but rather, continue to run them as separate queries
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    8
    Rep Power
    0

    You're right.


    Thanks guys. I'll probably be in here a lot and I promise that before I ask a question I'll do my due diligence and research.

    Thanks!
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,170
    Rep Power
    4274
    Originally Posted by Jacques1
    You cannot "combine" any two queries.
    actually, you can

    it's called a UNION

    it's even somewhat feasible in this instance, because the two queries return the same number (three) of the same datatype columns (presumably character, although the ids might require silent conversion or CASTing)

    however, to discombobulate a union query's result rows in the application is a lot messier than simply running two queries, and the union query doesn't really offer a very big performance improvement
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Originally Posted by r937
    actually, you can

    it's called a UNION
    *lol*

    Yeah, I'm sure MySQL actually can build a UNION from any two queries -- just like it can have "hidden columns", which aren't functionally dependend on the GROUP BY columns.

    Other database systems call that a bug, MySQL calls it a feature.
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    8
    Rep Power
    0

    Nice


    Originally Posted by r937
    actually, you can

    it's called a UNION
    I feel silly asking this at this point, but would I write a UNION in this case?
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,170
    Rep Power
    4274
    the idea of hidden columns in a GROUP BY being a "bug" is only somewhat accurate, since the sql standard does not support it, but the idea of it being "wrong" is a common misconception

    please read Debunking GROUP BY myths -- there's a good, and quite valid, reason for hidden columns to be omitted

    as for your comment about mysql and UNION queries -- ~all~ databases work the same way in this regard
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #10
  19. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,170
    Rep Power
    4274
    Originally Posted by tannercampbell
    I feel silly asking this at this point, but would I write a UNION in this case?
    no, i already advised against it

    "to discombobulate a union query's result rows in the application is a lot messier than simply running two queries, and the union query doesn't really offer a very big performance improvement"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  20. #11
  21. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Originally Posted by r937
    the idea of hidden columns in a GROUP BY being a "bug" is only somewhat accurate, since the sql standard does not support it, but the idea of it being "wrong" is a common misconception
    Um, we just had a discussion about that about a week ago. Already forgot everything?

    I'm also not sure how you came to the conclusion that MySQL's "hidden columns" are not wrong. The article doesn't say that. It doesn't even talk about "hidden columns", really (so I'm surprised you bring it up again). What it says is that non-aggregate expressions are fine as long as they're functionally dependend on the grouping columns. That's exactly what the (2003) standard says, what many database systems do and what I said in my post. MySQL, however, goes beyond that and allows any expression amongst aggregate expressions. And that's what's wrong about "hidden columns".



    Originally Posted by r937
    as for your comment about mysql and UNION queries -- ~all~ databases work the same way in this regard
    Nope. PostgreSQL, for example, has no automatic type mangling, so bullsh*t UNIONs will actually fail.

    But I'm pleasently surprised that MySQL won't allow a UNION over queries with a different number of columns. I thought it would select the columns randomly or something.
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    8
    Rep Power
    0

    Thumbs up Look what I've started :)


    Thanks for the education guys. I think DevShed is going to be crucial in my learning and becoming a MUCH better MySQL user. I think for 4 days of hands on and 5 days of book reading, I'm doing okay - can't wait to learn more. Thanks a lot, everyone.
  24. #13
  25. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,170
    Rep Power
    4274
    Originally Posted by Jacques1
    Um, we just had a discussion about that about a week ago. Already forgot everything?
    please stop being so bloody arrogant

    we never "discussed" anything -- my post in that thread went completely unanswered

    perhaps you oughta just stay in the postgresql forum if you dislike mysql so much
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo