[SOLVED] Mysql table comparisons

[yzerman]

I have 2 tables, each with one field in a mysql database. I need to compare the data in these 2 tables, and print the results of the query out.

The second part is not the problem for me. The first part (getting the actual query to work) is the problem.

Here are the table setups:

[table][tr][td][center]Table 1 (Field) - varchar(40) - unique[/center][/td][td][center]Table 2 (Field) varchar (8 ) - unique[/center][/td][/tr][tr][td][center]12345[/center][/td][td][center]12345[/center][/td][/tr][/table]

Now what I want to do with this data, is when a record for table 1, matches the data in table 2, I want it to send that data to another table called matches, and echo that data. I have tried using joins, and I have tried using array_intersect($table1, $table2) and each of these ways results in a null result, however I know that there is at least 1 match.

Unfortunatly, my website is down ATM, so pasting a copy of my code isn't going to happen until it gets back up, but any help would be appreciated.

[Hypnos]

This is best done with a JOIN in your SQL. Trying to do it in arrays will just add to the headache. SQL database servers are designed just for this sort of thing.

I can guess that you would want something like this:

SELECT field.table1, unique.table1, field.table2 AS field2
FROM table1
LEFT JOIN table2
ON unique.table1 = unique.table2

But I'm not an expert at this stuff, like some of the people in the SQL forums.

If you want to learn more about SQL, I highly suggest going here:
http://www.sqlzoo.net/

It's an amazing world once you get past the "SELECT * FROM table" stuff.

[yzerman]

[quote author=yzerman link=topic=124822.msg517733#msg517733 date=1170218638]
I have tried using joins, and I have tried using array_intersect($table1, $table2) and each of these ways results in a null result, however I know that there is at least 1 match.[/quote]

I think you missed the important part, and that was what I had already tried was using joins in an SQL query, and those failed.

[Hypnos]

Comparing data on 2 tables is exactly what JOINs are for. If it "didn't work", your syntax was wrong.

If you have a table like this:
1 orange
2 pear

And another like this:
1 apple
2 cherry

And you want:
1 orange apple
2 pear cherry

Then you want a JOIN. It will "work". Even if the ids only match up sometimes (That's why you have "LEFT JOIN", "RIGHT JOIN", etc).

If you had a NULL result, you obviously didn't do it right. Or is your coding just inherently 100% perfect?

[yzerman]

but thats not what I want

If I have a table like this
1 apple
2 orange
3 watermelon

and another like this

1 cherry
2 pear
3 blueberry
4 watermelon

And I want:
1 watermelon

as the result because it will be the only row that matches, not
1 apple cherry
2 orange pear

and yes, my coding is 100% perfect (explaining my account here ;) :P )

[Hypnos]

Ignore my dislexia on my previous SQL.

SELECT table1.field, table1.unique, table2.unique AS unique2
FROM table1
INNER JOIN table2
ON table1.field = table2.field

Should output:
3 4 watermelon

[yzerman]

Appreciate the help Hypnos, I will let you know if that works.

[yzerman]

Ok, I figured out what the problem was. I did not have the tables indexed.

The query that finally worked, after I set the tables indexes was:

SELECT `table1`.`field`
FROM table1, table2
WHERE (
`table2`.`field` = `table1`.`field`
)