Query help

[louisp]

Hi all, I'm having some problems getting queries right..

SELECT efc_lineup FROM season_reports WHERE efc_lineup LIKE '%Clarke%'

returns 4 results (correct)

whilst

SELECT * FROM season_reports,season_fixtures
WHERE season_fixtures.competition='Friendly' AND season_reports.efc_lineup LIKE '%$Clarke%'

returns about 60 even though they are the same.. (only friendly games have been played so far)

Does anyone know hwat Im doing wrong?

also does anyone know how to reduce server strain for the following:

$friendlygames = mysql_query("SELECT * FROM season_reports,season_fixtures
WHERE season_fixtures.competition='Friendly' AND season_reports.efc_lineup LIKE '%$surname%'",$db)
or die (mysql_error());
($friendlygamesno = @mysql_num_rows($friendlygames));

$leaguegames = mysql_query("SELECT * FROM season_reports,season_fixtures
WHERE season_fixtures.competition='Premier League' AND season_reports.efc_lineup LIKE '%$surname%'",$db)
or die (mysql_error());
($leaguegamesno = @mysql_num_rows($leaguegames));

$facupgames = mysql_query("SELECT * FROM season_reports,season_fixtures
WHERE season_fixtures.competition='FA Cup' AND season_reports.efc_lineup LIKE '%$surname%'",$db)
or die (mysql_error());
($facupgamesno = @mysql_num_rows($facupgames));

$leaguecupgames = mysql_query("SELECT * FROM season_reports,season_fixtures
WHERE season_fixtures.competition='League Cup' AND season_reports.efc_lineup LIKE '%$surname%'",$db)
or die (mysql_error());
($leaguecupgamesno = @mysql_num_rows($leaguecupgames));

$totalgames = ($friendlygamesno+$leaguegamesno+$facupgamesno+$leaguecupgamesno);

Any help will be fantastic!

[Anon]

To answer the first part of your post, in the first query, you are only querying and pulling data from one table (season_reports). In the second query, you are querying and pulling data from two tables (season_fixtures and season_reports). What the second query is doing is pulling all results from season_fixtures where competition = Friendly, and then pulling all the results from season_fixtures where efc_lineup contains 'Clarke' which will give you more results.

[smook]

Louis,

When you query from more than one table in a relational database you have to tell the database how the tables relate - otherwise it will spit out a "cartesian product" which means the result list will have the number of rows in table1 * number of rows in table2

So if you have:

table1

id

A
B
C

table2

num

1
2
3

If you say select * from table1,table2 where id = B and num > 2 the database runs this:

table1 joined with table2
A 1
A 2
A 3
B 1
B 2
B 3
C 1
C 2
C 3

Then... it runs the where on it and reduces it to:

B 3

Server strain is evident there.

If I said:

select * from table1,table2 where num >2 I'd get:
A 2
B 2
C 2
A 3
B 3
C 3

I guess the question you have to ask yourself is why do you want to join table1 to table2 (or lineup to season) ... In this example, there is nothing related in table1 to table2 ... that always leads to trouble.

How are the 2 tables related? Why do you want them joined?

sm