MySQL SELECT

[Anon]

Hi,

I have a MySQL database with two tables in it, both tables have the same three fields amongst others. I need to select all the records from one table which have some of the same value(s) in these three fields as those in the other table. I also need to order the records by the number of matches, i.e. records where all three fields match first and then the records where two fields match and so on. These fields are all strings.

Any ideas will be much appreciated.

[[deleted]]

Tricky but possible I think:
Have a peek at this and see if you understand it at all.

SELECT *,
CASE
&nbsp;&nbsp;WHEN&nbsp;(table1.col1=table2.col1&nbsp;AND&nbsp;table1.col2<>table2.col2&nbsp;AND&nbsp;table1.col3<>table2.col3)
&nbsp;&nbsp;&nbsp;&nbsp;OR&nbsp;(table1.col1<>table2.col1&nbsp;AND&nbsp;table1.col2=table2.col2&nbsp;AND&nbsp;table1.col3<>table2.col3)
&nbsp;&nbsp;&nbsp;&nbsp;OR&nbsp;(table1.col1<>table2.col1&nbsp;AND&nbsp;table1.col2<>table2.col2&nbsp;AND&nbsp;table1.col3=table2.col3)
&nbsp;&nbsp;&nbsp;THEN&nbsp;1
ELSE
&nbsp;&nbsp;CASE
&nbsp;&nbsp;&nbsp;&nbsp;WHEN
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(table1.col1=table2.col1&nbsp;AND&nbsp;table1.col2=table2.col2&nbsp;AND&nbsp;table1.col3<>table2.col3)
&nbsp;&nbsp;&nbsp;&nbsp;OR&nbsp;(table1.col1=table2.col1&nbsp;AND&nbsp;table1.col2<>table2.col2&nbsp;AND&nbsp;table1.col3=table2.col3)
&nbsp;&nbsp;&nbsp;&nbsp;OR&nbsp;(table1.col1<>table2.col1&nbsp;AND&nbsp;table1.col2=table2.col2&nbsp;AND&nbsp;table1.col3=table2.col3)
&nbsp;&nbsp;&nbsp;&nbsp;THEN&nbsp;2
&nbsp;&nbsp;ELSE
&nbsp;&nbsp;&nbsp;&nbsp;CASE
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WHEN
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(table1.col1=table2.col1&nbsp;AND&nbsp;table1.col2=table2.col2&nbsp;AND&nbsp;table1.col3=table2.col3)
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;THEN&nbsp;3
&nbsp;&nbsp;&nbsp;&nbsp;ELSE&nbsp;0
&nbsp;&nbsp;&nbsp;&nbsp;END
&nbsp;&nbsp;END
END&nbsp;as&nbsp;m&nbsp;
FROM&nbsp;a,table2
ORDER&nbsp;BY&nbsp;m&nbsp;DESC;

[[deleted]]

oops, that 'a' in the FROM should be 'table1'

[Anon]

Cheers mate, I'll give it a go and let you know if it works.

[Anon]

Thanks for the suggestion however I couldn't get it to work. I examined your sample query and managed to understand what you were suggesting then I tried to have a further play around with it to see if I could make it work, but I haven't managed to do so yet.

I am quite new to PHP/MySQL but I thought I would have needed a WHERE_DEFINITION bit in there somwhere.

[[deleted]]

the WHERE is done in the CASE statement.
You cannot use a normal WHERE because you are working with two tables, and there are three columns that can all match or not-match. If all three columns always matched, then a WHERE can be used.

how far did you get?