| Home | Register | FAQ | Members List | Search | Today's Posts | Mark Forums Read |
|
|
#1 (permalink) |
|
Creativo
Join Date: Nov 2006
Location: Torrevieja (Alicante), Spain
Posts: 9
|
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! |
|
|
|
|
|
#2 (permalink) |
|
Web Developer
|
why use 2 tables for that? surely you could just have: [products] - id - name - type (spa, sauna, etc) - visible then just use this query. Code:
KISS principle - Wikipedia, the free encyclopedia |
|
|
|
#5 (permalink) | |
|
Senior Member
|
Quote:
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. Code:
I would recommend updating your DB schema Last edited by d3mcfadden : 13-04-2008 at 21:01. |
|
|
|
|
#6 (permalink) |
|
Grumpy old man
Join Date: Oct 2007
Location: North Japan
Posts: 1,128
|
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. |
|
![]() |