Is this possible in one query?

Databases
1

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

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 –


SELECT ...
  FROM item_categories
INNER
  JOIN order_forms
    ON order_forms.order_form_value  = item_categories.order_form_value
3

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 :smiley: You’re always such a great help.

4

Well, yes, that’s what a join does… :smiley:

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

5

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

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

this just made my week

thank you for the kind words… it’s truly appreciated

ok, it’s now beer o’clock

8

Well then, hopefully my purchase of your Ebook will really make your week then! Tip one back for me as well :wink:

9

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

If you need to know what table each row comes from, just add a column with an identifying value:


(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

LOL. I had no idea Mysql could do stuff like this. I’m trained in PHP but not in databases :smiley:

12

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


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


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


(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"