| Author |
getting records from 2 tables
|
|
|
|
I have 2 tables each with a column called 'item'
I want to list all the records from both tables where items like
'%screws%'
Each table currently holds less than 500 trial records
This is the raw SQL I tried which sends the server into an infinate loop
Can anyone tell me the syntax for how I should do this please?
SELECT * FROM table1,table2 WHERE table1.item OR table2.item like
'%screws%'
--------------
tag
| |
| Bob Stearns 2008-03-09, 7:20 pm |
| tag wrote:
> I have 2 tables each with a column called 'item'
>
> I want to list all the records from both tables where items like
> '%screws%'
>
> Each table currently holds less than 500 trial records
> This is the raw SQL I tried which sends the server into an infinate loop
> Can anyone tell me the syntax for how I should do this please?
>
> SELECT * FROM table1,table2 WHERE table1.item OR table2.item like
> '%screws%'
>
> --------------
> tag
Look at the UNION operator
| |
|
|
| Preventer of Work 2008-03-13, 10:08 pm |
| tag wrote:
> I have 2 tables each with a column called 'item'
>
> I want to list all the records from both tables where items like
> '%screws%'
>
> Each table currently holds less than 500 trial records
> This is the raw SQL I tried which sends the server into an infinate loop
> Can anyone tell me the syntax for how I should do this please?
>
> SELECT * FROM table1,table2 WHERE table1.item OR table2.item like
> '%screws%'
>
> --------------
> tag
Without looking very hard (and I gues a lot), you seem to have two problems:
1) your query might try to deliver 500*500 (250,000) records.
2) You logic is very wrong.
Bad: WHERE table1.item OR table2.item like'%screws%'
Better: WHERE table1.item like '%screws%' OR table2.item like'%screws%'
(but still wrong)
You still need to look into a proper JOIN
| |
|
| On Sun, 09 Mar 2008 14:41:12 GMT, tag wrote...
>
>
>I have 2 tables each with a column called 'item'
>
>I want to list all the records from both tables where items like
>'%screws%'
>
>Each table currently holds less than 500 trial records
>This is the raw SQL I tried which sends the server into an infinate loop
>Can anyone tell me the syntax for how I should do this please?
>
>SELECT * FROM table1,table2 WHERE table1.item OR table2.item like
>'%screws%'
>
>--------------
>tag
A couple of others had already made recommdnations for "UNION" and "LEFT JOIN".
If there was no relation between table1 and table2, then you need to run them as
separate queries.
SELECT * FROM table1 WHERE table1.item like '%screws%';
SELECT * FROM table2 WHERE table2.item like '%screws%';
Tom
--
NewsGuy Free Trial Accounts
Now a massive 20 Gigabytes of unrestricted downloads !
http://newsguy.com/overview.htm
|
|
|
|