[Encantado]

Hi all,
I've got a question and hope that somebody can help me with it

The Tables:

Table A : Saunas
- field: id
- field: name
- field: visible

Table B : Spas
- field: id
- field: name
- field: visible

The Question:
How do I select in 1 query every product that has visible = 1.
So I need to display all the Saunas and all the Spas which are visible. These table are not linked with each other.

I have been trying queries like :

SELECT saunas. * , spa_jacuzzis. * FROM saunas, spa_jacuzzis
WHERE saunas.special_offer = '1' AND spa_jacuzzis.special_offer = '1'

But it only shows me the visible saunas

Some help would be great!

[freelancr]

why use 2 tables for that? surely you could just have:

[products]
- id
- name
- type (spa, sauna, etc)
- visible

then just use this query.

SELECT * FROM `products` WHERE `visible` = '1';

KISS principle - Wikipedia, the free encyclopedia

[Encantado]

Because i have in total 9 product tables with each their own specific fields.

[haku]

Maybe someone else knows better, but I don't think you can select unrelated data from two different tables in one query.

[d3mcfadden]

Originally Posted by Encantado

Because i have in total 9 product tables with each their own specific fields.

That is flawed logic and terrible db design. In situations like that you abstract out all fields into their own table and join them back into to one products table.

Anyways, if you want to achieve what you are asking in one query there are two ways to do it, a temporary table or a union command. Using a temp table you select all rows from both tables INTO the temp table them select back out then DROP the temp table. Union will only work if you select the same exact fields from each table.

SELECT id, name 
FROM Saunas
WHERE visible = 1
   UNION 
SELECT id, name 
FROM Spas
WHERE visible = 1

I would recommend updating your DB schema

[Hunch]

If you want better control of your data across multiple tables, use a JOIN.

MySQL :: MySQL 5.1 Reference Manual :: 12.2.7.1 JOIN Syntax

That's what it's designed for.

[sukino]

Just in case some people still wonders:

Table A : Saunas
- field: id
- field: name
- field: visible

Table B : Spas
- field: id
- field: name
- field: visible

Use a union (since there is no way to link them as there is no relationship between the tables):

Select id, name From Saunas
Where visible = 1
Union
Select id, name From Spas
Where visible = 1

You will get what you need.

[arn_e]

i think this would work

SELECT
*
FROM
Saunas sa , Spas sp
WHERE
sa.visible = 1 AND sp.visible = 1

[combatsheep]

It's not really good practice to link tables on fields other that ID's so UNION could be used, it depends on the coders personal choice.

I would personally use UNION