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-01T07:26:02Z —
#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

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-01T21:38:58Z —
#4

wh33t said:

Unfortunately the query did as I expected it would and it joined the data together

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-01T21:43:52Z —
#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;

r937
—
2013-02-01T21:48:40Z —
#7

wh33t said:

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

wh33t
—
2013-02-01T21:51:28Z —
#8

r937 said:

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

wh33t
—
2013-02-01T21:53:59Z —
#9

guido2004 said:

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.

guido2004
—
2013-02-01T21:57:11Z —
#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;

wh33t
—
2013-02-01T21:58:47Z —
#11

guido2004 said:

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;

`

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

wh33t
—
2013-02-01T22:12:17Z —
#12

guido2004 said:

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;

`

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.

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