wh33t — 2013-01-31T22:30:26-05:00 — #1
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.
r937 — 2013-02-01T02:26:02-05:00 — #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 --
ON order_forms.order_form_value = item_categories.order_form_value
wh33t — 2013-02-01T16:32:55-05:00 — #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 You're always such a great help.
guido2004 — 2013-02-01T16:38:58-05:00 — #4
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
wh33t — 2013-02-01T16:40:49-05:00 — #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?
guido2004 — 2013-02-01T16:43:52-05:00 — #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)
(SELECT a FROM t2 WHERE a=11 AND B=2)
ORDER BY a LIMIT 10;
r937 — 2013-02-01T16:48:40-05:00 — #7
this just made my week
thank you for the kind words... it's truly appreciated
ok, it's now beer o'clock
wh33t — 2013-02-01T16:51:28-05:00 — #8
Well then, hopefully my purchase of your Ebook will really make your week then! Tip one back for me as well
wh33t — 2013-02-01T16:53:59-05:00 — #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.
guido2004 — 2013-02-01T16:57:11-05:00 — #10
If you need to know what table each row comes from, just add a column with an identifying value:
't1' as tablename
WHERE a=10 AND B=1
't2' as tablename
WHERE a=11 AND B=2
ORDER BY a LIMIT 10;
wh33t — 2013-02-01T16:58:47-05:00 — #11
LOL. I had no idea Mysql could do stuff like this. I'm trained in PHP but not in databases
wh33t — 2013-02-01T17:12:17-05:00 — #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)
(select order_form_id, order_form_name)
where order_form_category = "1"