mdmartiny Posted January 5, 2012 Share Posted January 5, 2012 I created a search function for a website that I am working on. Right now it is only searching one table. I would like to make it so it searches two tables. I am trying to do this the simplest way possible. So as not to clutter up my code anymore than I have two. The way that I have been trying to get it to do it is Select * from ttmautos, inperson_autos Where inperson_autos.l_name = 'something' or ttmautos.l_name = 'something' When I use this code I get repeated rows from the table. When I was searching online to see if I could figure out what the problem was and most things I read always used AND. The information does not need to be in both tables. When they are using the search feature of the site. The information could be in either table or in could pull from both tables. I have read about Joins. I am not sure if I am to new to MySql to understand them. I have also read about unions and what I understand is that both tables have to have the same number of columns and same type of field data. Both of my tables do not have the same number of columns. I would appreciate any help on this issue. Quote Link to comment https://forums.phpfreaks.com/topic/254385-searching-multiple-tables/ Share on other sites More sharing options...
fenway Posted January 5, 2012 Share Posted January 5, 2012 You need UNION, not JOIN. Quote Link to comment https://forums.phpfreaks.com/topic/254385-searching-multiple-tables/#findComment-1304585 Share on other sites More sharing options...
mdmartiny Posted January 6, 2012 Author Share Posted January 6, 2012 With a Union don't the tables need to be the same? Or am I reading it wrong about them? Quote Link to comment https://forums.phpfreaks.com/topic/254385-searching-multiple-tables/#findComment-1304678 Share on other sites More sharing options...
fenway Posted January 6, 2012 Share Posted January 6, 2012 I didn't see any schemas posted. Yes, that's true. You need 2 separate queries. Quote Link to comment https://forums.phpfreaks.com/topic/254385-searching-multiple-tables/#findComment-1305085 Share on other sites More sharing options...
kickstart Posted January 9, 2012 Share Posted January 9, 2012 Hi You current SQL will do a cross join, producing a row for every combination of rows on the 2 tables and then bring back any of those combination rows where either name is 'something'. So if you have 100 rows on each table with only 2 rows on one table where the name matches it will bring back 200 rows. All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/254385-searching-multiple-tables/#findComment-1305740 Share on other sites More sharing options...
The Little Guy Posted January 9, 2012 Share Posted January 9, 2012 I didn't see any schemas posted. Yes, that's true. You need 2 separate queries. Actually, I believe only the return result from table A and table B need to be the same, not the actual tables. So: (select animal_id as id, animal_name as name from animal_table) UNION (select person_id as id, person_name as name from person_table) Quote Link to comment https://forums.phpfreaks.com/topic/254385-searching-multiple-tables/#findComment-1305959 Share on other sites More sharing options...
fenway Posted January 10, 2012 Share Posted January 10, 2012 You have to have (a) the same number of columns and (b) they have to be of the same type. Quote Link to comment https://forums.phpfreaks.com/topic/254385-searching-multiple-tables/#findComment-1305977 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.