Discussion:
select records from two tables
(too old to reply)
cerr
2012-02-08 19:06:29 UTC
Permalink
Hi,

I want to check for records in two tables and came up withe below
query:
SELECT * FROM xymply_users, xymply_newusers WHERE xymply_users.user
='test' OR xymply_newusers.user = 'test'

But this also shows me record where user isn't = 'test' why is that?
How do I do this correctly?

Thank you,
Ron
Gordon Burditt
2012-02-08 21:58:05 UTC
Permalink
Post by cerr
I want to check for records in two tables and came up withe below
What are you trying to accomplish?
Post by cerr
SELECT * FROM xymply_users, xymply_newusers WHERE xymply_users.user
='test' OR xymply_newusers.user = 'test'
You are joining two tables with no relationship between the pairs
of records specified in an ON or WHERE clause. This is often (not
always, but often) a mistake.

This query selects every PAIR of records where one of the pair has
user = 'test'. Assuming that there is at least one user where user
= 'test' in both tables, this will include all records from either
of the tables as the first or second of the pair. If there are more
than one record with user = 'test' in each table, you'll get multiple
copies of each record.
Post by cerr
But this also shows me record where user isn't = 'test' why is that?
If you want only pairs of records where both records have user =
'test', use AND not OR in the WHERE clause.
Post by cerr
How do I do this correctly?
What is it you are attempting to do correctly?

What comes to mind is using two queries, one for each table. If
the two tables have the same structure, perhaps you want to use two
queries with UNION.

Loading...