SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Zealot wh33t's Avatar
    Join Date
    Aug 2011
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Is this possible in one query?

    Hey SP,

    I've got two tables and I need to grab information from. One is a category table, and one is an order form table. They share a common column name called order_form_category however they are not related by a foreign key. Is it possible to select records from both tables in one query that match the same order_form_category value?

    For example if records in the item_categories have an order_form_category value of 4 and there is also records in the order_forms table that has an order_form_value = 4 is it possible to select all of the records from these two tables that have an order_form_value of 4.

    To me this seems like a table join, but the two tables aren't related to one another so I can't see how a table join would work.

    It's not a huge deal if I have to do this in two queries, I know that I can merge the records using PHP, and then sort them using PHP but I'd rather do it all from the query if possible.

    Your thoughts and tips are greatly appreciated.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    you can write a query to join two tables on any columns you wish

    there doesn't have to be a foreign key, although obviously, if the tables are actually related, it would be better (because of relational integrity) for the foreign key to be implemented

    so go ahead and write your join query --
    Code:
    SELECT ...
      FROM item_categories
    INNER
      JOIN order_forms
        ON order_forms.order_form_value  = item_categories.order_form_value
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot wh33t's Avatar
    Join Date
    Aug 2011
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you can write a query to join two tables on any columns you wish

    there doesn't have to be a foreign key, although obviously, if the tables are actually related, it would be better (because of relational integrity) for the foreign key to be implemented

    so go ahead and write your join query --
    Code:
    SELECT ...
      FROM item_categories
    INNER
      JOIN order_forms
        ON order_forms.order_form_value  = item_categories.order_form_value
    I went ahead and wrote the query. Unfortunately the query did as I expected it would and it joined the data together. I think I'm probably not explaining my issue correctly but I thank you for the help anyways You're always such a great help.

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by wh33t View Post
    Unfortunately the query did as I expected it would and it joined the data together
    Well, yes, that's what a join does...

    Maybe what you are looking for is UNION? http://dev.mysql.com/doc/refman/5.5/en/union.html

  5. #5
    SitePoint Zealot wh33t's Avatar
    Join Date
    Aug 2011
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Well, yes, that's what a join does...

    Maybe what you are looking for is UNION? http://dev.mysql.com/doc/refman/5.5/en/union.html
    Ooh, that does look like what I'm after. Would you be able to tell me whether or not you can union two sets of results together and order them under the same clause? In this case alphabetically?

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    From the manual I linked to
    To use an ORDER BY or LIMIT clause to sort or limit the entire UNION result, parenthesize the individual SELECT statements and place the ORDER BY or LIMIT after the last one. The following example uses both clauses:

    (SELECT a FROM t1 WHERE a=10 AND B=1)
    UNION
    (SELECT a FROM t2 WHERE a=11 AND B=2)
    ORDER BY a LIMIT 10;

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by wh33t View Post
    I think I'm probably not explaining my issue correctly but I thank you for the help anyways You're always such a great help.
    this just made my week

    thank you for the kind words... it's truly appreciated

    ok, it's now beer o'clock
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Zealot wh33t's Avatar
    Join Date
    Aug 2011
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    this just made my week

    thank you for the kind words... it's truly appreciated

    ok, it's now beer o'clock
    Well then, hopefully my purchase of your Ebook will really make your week then! Tip one back for me as well

  9. #9
    SitePoint Zealot wh33t's Avatar
    Join Date
    Aug 2011
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    From the manual I linked to
    Thank you for that. I apologize, I have a hard time understanding technical manuals (Yes I know it's strange for a programmer to have that handicap!). At any rate, the issue I'm having now is that I'm have troubles discerning which table the data came from. Apparently I have to select the same number of columns from each table for the result to work but they are different tables with different data and column names. I'll keep digging.

  10. #10
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    If you need to know what table each row comes from, just add a column with an identifying value:
    Code:
    (SELECT 
          't1' as tablename
        , a 
     FROM t1 
     WHERE a=10 AND B=1
    )
    UNION
    (SELECT 
          't2' as tablename
        , a 
     FROM t2 
     WHERE a=11 AND B=2
    )
    ORDER BY a LIMIT 10;

  11. #11
    SitePoint Zealot wh33t's Avatar
    Join Date
    Aug 2011
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    If you need to know what table each row comes from, just add a column with an identifying value:
    Code:
    (SELECT 
          't1' as tablename
        , a 
     FROM t1 
     WHERE a=10 AND B=1
    )
    UNION
    (SELECT 
          't2' as tablename
        , a 
     FROM t2 
     WHERE a=11 AND B=2
    )
    ORDER BY a LIMIT 10;
    LOL. I had no idea Mysql could do stuff like this. I'm trained in PHP but not in databases

  12. #12
    SitePoint Zealot wh33t's Avatar
    Join Date
    Aug 2011
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    If you need to know what table each row comes from, just add a column with an identifying value:
    Code:
    (SELECT 
          't1' as tablename
        , a 
     FROM t1 
     WHERE a=10 AND B=1
    )
    UNION
    (SELECT 
          't2' as tablename
        , a 
     FROM t2 
     WHERE a=11 AND B=2
    )
    ORDER BY a LIMIT 10;
    I should also add, in this case what would t1 be? Is it an actual column or the actual table? I don't understand how to use this. Let me dump you some of my vitals here.

    Table 1 is item_categories

    Code:
    item_category_id 	int(11)	NO 	PRI 	NULL	auto_increment
    item_type_id 	int(11)	NO 	MUL 	NULL	
    item_category_name 	varchar(255)	NO 		0	
    order_form_category 	tinyint(1)	NO 		0
    Table 2 is order_forms

    Code:
    order_form_id 	int(11)	NO 	PRI 	NULL	auto_increment
    order_form_name 	varchar(255)	NO 		0	
    order_form_category 	tinyint(1)	NO 		NULL
    I want to be able to get one result set that has records from both tables that match an order_form_category of 1 that is sorted alphabetically by order_form_name. In order to achieve this I can do a "select item_category_name as order_form_name" but I still need to discern which table the data came from. If I could somehow get the item_type_id from the table item_categories that would be great, but I don't know how to do that.

    Here is what I've got so far, but it doesn't work because I am requesting three columns from item_categories and only two from order_forms

    Code:
    (select item_category_id as order_form_id, item_category_name as order_form_name, item_type_id from item_categories)
    UNION
    (select order_form_id, order_form_name)
    where order_form_category = "1"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •