Canman2005 Posted October 1, 2008 Share Posted October 1, 2008 Hi all I have the following database tables one table called "items1" id name 1 iPod Nano 2 iPod Touch 3 iMac and another table called "items2" id main_id name 1 2 iPod Classic 2 1 iPhone 3 2 MacBook Now as you can see I have 2 tables and the second table "items2" contains a field called "main_id" that ID number relates to the ID number on "items1". My question is, how can I run a query to get all rows which have the number 2 in either the ID field of the "items1" table or a number 2 in the "items2" table. So the results would look like 2 iPod Touch 1 2 iPod Classic 3 2 MacBook Any ideas? Thanks Dave Link to comment https://forums.phpfreaks.com/topic/126546-query-two-tables/ Share on other sites More sharing options...
xtopolis Posted October 1, 2008 Share Posted October 1, 2008 While this query gets what you asked, I feel it's not the solution for whatever you are trying to do. SELECT i1.id,'' as main_id,i1.name FROM items1 i1 WHERE id=2 UNION SELECT i2.id,i2.main_id,i2.name FROM items2 i2 WHERE main_id=2 Outputs:[pre] id main_id name 2 iPod Touch 1 2 iPod Classic 3 2 MacBook [/pre] Link to comment https://forums.phpfreaks.com/topic/126546-query-two-tables/#findComment-654457 Share on other sites More sharing options...
Canman2005 Posted October 1, 2008 Author Share Posted October 1, 2008 Thanks, but how can I order these rules, I tried, but the rows returned from the first QUERY appear at the top and the rows returned from the second QUERY appear at the bottom. Is there a way to sort all the rows returned? Link to comment https://forums.phpfreaks.com/topic/126546-query-two-tables/#findComment-654884 Share on other sites More sharing options...
xtopolis Posted October 1, 2008 Share Posted October 1, 2008 Again, this does what you ask, but I think your table structure might need to be redone, using lookup tables, etc. SELECT i1.id,'' as main_id,i1.name FROM items1 i1 WHERE id=2 UNION SELECT i2.id,i2.main_id,i2.name FROM items2 i2 WHERE main_id=2 ORDER BY id DESC [pre] id main_id name 3 2 MacBook 2 iPod Touch 1 2 iPod Classic [/pre] Link to comment https://forums.phpfreaks.com/topic/126546-query-two-tables/#findComment-654917 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.