Jump to content

Query two tables


Canman2005

Recommended Posts

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

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

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

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.