Multiple Table Search, ambiguous error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Multiple Table Search, ambiguous error

Author

Topic

Maddocks
Starting Member

8 Posts

Posted - 2006-04-18 : 13:51:17

HI, i am new to these forums, i have setup a search script which searches one table and this works fine. Now, i want the script to search two tables, the tables have the same structure and i want the script to select from the "Name" column in both tables. I have tried using a select statement for two tables but receive this error: Column 'Name' in where clause is ambiguous. I heard that this means the script doesnt know which table to search as they both have the same column name. I want it to search both. Here is my select script:

select * from downloads_anti ,downloads_multi where Name like \"%$trimmed%\"

any suggestions would help me greatly thanks.

http://pchelpworld.awardspace.com < Free PC/PDP Forums - no signup required

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-18 : 14:18:33

WHERE downloads_anti.[Name] LIKE ... OR downloads_multi.[Name} LIKE ...

Tara Kizer
aka tduggan

Maddocks
Starting Member

8 Posts

Posted - 2006-04-18 : 14:20:06

thanks for replying, what do i replace the ... with/ and don't i want to select, or can yo just compare?

http://pchelpworld.awardspace.com < Free PC/PSP Forums - no signup required

Maddocks
Starting Member

8 Posts

Posted - 2006-04-18 : 14:25:14

tried that, and it didnt work, another error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE downloads_anti.[Name] LIKE "%winamo%\ OR downloads_multi.[

http://pchelpworld.awardspace.com < Free PC/PSP Forums - no signup required

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-18 : 14:28:17

You have posted your question in a Microsoft SQL Server forum. Answers will almost always be in T-SQL. T-SQL code might not work in MySQL. I'd suggest posting in the MySQL forum over at www.dbforums.com.

You replace your WHERE clause with my WHERE clause. I think you'll have to further modify your query though. I believe you'll need a UNION.

SELECT ... <-- your columns go here, don't use *
FROM (
SELECT ... <-- your columns go here, don't use *
FROM Table1
UNION ALL
SELECT ... <-- your columns go here, don't use *
FROM Table2) t
WHERE [Name] LIKE ... <-- your LIKE clause goes here

Tara Kizer
aka tduggan

Maddocks
Starting Member

8 Posts

Posted - 2006-04-18 : 14:37:56

Thanks for this, got a new error nor, i used this:

SELECT Name FROM (SELECT Name FROM downloads_anti UNION ALL SELECT Name FROM downloads_multi) WHERE [Name] LIKE \"%$trimmed%\"

and got this error:

Every derived table must have its own alias

http://pchelpworld.awardspace.com < Free PC/PSP Forums - no signup required

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-18 : 14:39:26

You didn't include the t after the left parenthesis like in my example. The alias can be anything. I use t to stand for derived table.

Tara Kizer
aka tduggan

Maddocks
Starting Member

8 Posts

Posted - 2006-04-18 : 14:50:26

i did that and received another error,
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[Name] LIKE "%avast%"' at line 1

%avast% "avast" what i searched for
thanks for all your help

http://pchelpworld.awardspace.com < Free PC/PSP Forums - no signup required

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-18 : 14:53:31

Please post your query again. Remember that my answer will be a T-SQL answer, which means it'll work in SQL Server, but it might not work in MySql. So you might be wasting your time in this SQL Server forum.

Tara Kizer
aka tduggan

Maddocks
Starting Member

8 Posts

Posted - 2006-04-18 : 14:56:56

here is my query:

"SELECT Name FROM (SELECT Name FROM downloads_anti UNION ALL SELECT Name FROM downloads_multi) t WHERE [Name] LIKE \"%$trimmed%\"

thanks for your help, if it doesnt work where can i post my problem?

http://pchelpworld.awardspace.com < Free PC/PSP Forums - no signup required

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-18 : 15:09:33

I don't understand your LIKE portion. In T-SQL, what you have is invalid. It would be:

LIKE '%$trimmed%'

But if that doesn't work in MySql, I'm out of ideas.

Tara Kizer
aka tduggan

Maddocks
Starting Member

8 Posts

Posted - 2006-04-18 : 15:11:31

there is another error now, anywhere i can post it to get some help?

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[Name] LIKE '%avast%'' at line 1

http://pchelpworld.awardspace.com < Free PC/PSP Forums - no signup required

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-18 : 15:16:47

As mentioned in my second post, try the MySql forum at www.dbforums.com .

Tara Kizer
aka tduggan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-19 : 03:30:07

Try

SELECT [Name] FROM (SELECT [Name] FROM downloads_anti UNION ALL SELECT [Name] FROM downloads_multi) t WHERE [Name] LIKE '%$trimmed%'

or try at www.MySQL.com

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources