Query two tables

[Canman2005]

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

[xtopolis]

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]

[Canman2005]

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?

[xtopolis]

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]