AndieB Posted January 23, 2009 Share Posted January 23, 2009 Hi all, I've read many posts about JOIN, LEFT JOIN, RIGHT JOIN.. I guess every JOIN alternative I could get hold of, but I must admit that it confuses me. What I would like to achieve is the following. I have two tables. Table one consists of user information and one field (e_id) which is relative to a post that exists in the second table. I want to get ALL the fields from TABLE1 and get only three fields from TABLE2. That match that must take place is that the e_id field is equal in both tables. HOW do I write this query?! Thankful for any kind of help! Sincerely, Andreas Link to comment https://forums.phpfreaks.com/topic/142160-about-the-join-alternatives/ Share on other sites More sharing options...
kkubek Posted January 23, 2009 Share Posted January 23, 2009 select a.*, b.c1, b.c2, b.c3 from table1 a, table2 b where a.e_id=b.e_id c1,c2 and c3 are the column names u want from table2 Link to comment https://forums.phpfreaks.com/topic/142160-about-the-join-alternatives/#findComment-744710 Share on other sites More sharing options...
corbin Posted January 23, 2009 Share Posted January 23, 2009 SELECT a.*, b.c1, b.c2, b.c3 FROM table1 a JOIN table2 b ON b.e_id = a.e_id; might be faster. Link to comment https://forums.phpfreaks.com/topic/142160-about-the-join-alternatives/#findComment-744768 Share on other sites More sharing options...
AndieB Posted January 24, 2009 Author Share Posted January 24, 2009 Thank you very much! It seems to work like I want it! I experienced another interesting thing though. In the first table I've used the pre-fix e_ to identify what "application" the belong too. Now, this goes also for table2 AND this makes me thinking. In table1 I've got the following field: e_city In table2 I've got the same field: e_city When I do a mysql_fetch_assoc to obtain the data from my sqlQuery in PHP, HOW will I know which of these fields I'm showing, when echoing: $row["e_city"] ?? Am I fuzzy or clear in my description? Thank you again for your answer! Sincerely, Andreas Link to comment https://forums.phpfreaks.com/topic/142160-about-the-join-alternatives/#findComment-745138 Share on other sites More sharing options...
fenway Posted January 27, 2009 Share Posted January 27, 2009 Show a table structure. Link to comment https://forums.phpfreaks.com/topic/142160-about-the-join-alternatives/#findComment-747525 Share on other sites More sharing options...
AndieB Posted January 27, 2009 Author Share Posted January 27, 2009 Show a table structure. How do I extract the structure via phpMyAdmin so that will become readable in this forum? Sincerely, Andreas Link to comment https://forums.phpfreaks.com/topic/142160-about-the-join-alternatives/#findComment-747849 Share on other sites More sharing options...
AndieB Posted January 27, 2009 Author Share Posted January 27, 2009 Think I solved it. Here is first table, called EVENTS. Field Type Null Key Default Extra e_id int(11) NO PRI NULL auto_increment e_title varchar(250) YES NULL e_date date NO MUL e_starttime time NO e_stoptime time NO e_regstart time NO e_regstop time NO e_city varchar(250) NO e_country varchar(250) NO e_venue varchar(250) NO e_address varchar(250) NO e_map_lat double NO 0 e_map_lng double NO 0 e_manager varchar(250) NO e_man_email varchar(250) NO e_man_phone varchar(20) NO e_num_tickets varchar(250) NO e_max_tickets_buyer int(5) NO e_guest_tickets int(2) NO 0 e_end_res_date date NO e_last_payday datetime NO e_reggable tinyint(2) NO 0 The second table, called E_TICKETBUYER: Field Type Null Key Default Extra e_id int(11) NO PRI NULL auto_increment e_title varchar(250) YES NULL e_date date NO MUL e_starttime time NO e_stoptime time NO e_regstart time NO e_regstop time NO e_city varchar(250) NO e_country varchar(250) NO e_venue varchar(250) NO e_address varchar(250) NO e_map_lat double NO 0 e_map_lng double NO 0 e_manager varchar(250) NO e_man_email varchar(250) NO e_man_phone varchar(20) NO e_num_tickets varchar(250) NO e_max_tickets_buyer int(5) NO e_guest_tickets int(2) NO 0 e_end_res_date date NO e_last_payday datetime NO e_reggable tinyint(2) NO 0 Hope this helps. Link to comment https://forums.phpfreaks.com/topic/142160-about-the-join-alternatives/#findComment-747857 Share on other sites More sharing options...
fenway Posted January 28, 2009 Share Posted January 28, 2009 Did you in fact solve it? If so, post the solution, or I'll look into it. Link to comment https://forums.phpfreaks.com/topic/142160-about-the-join-alternatives/#findComment-748573 Share on other sites More sharing options...
AndieB Posted January 28, 2009 Author Share Posted January 28, 2009 Did you in fact solve it? If so, post the solution, or I'll look into it. No, what I meant I solved, was to extract the table layout. --Andreas Link to comment https://forums.phpfreaks.com/topic/142160-about-the-join-alternatives/#findComment-748633 Share on other sites More sharing options...
fenway Posted January 29, 2009 Share Posted January 29, 2009 oh, i get it... could you also post the query you're talking about? Link to comment https://forums.phpfreaks.com/topic/142160-about-the-join-alternatives/#findComment-749384 Share on other sites More sharing options...
AndieB Posted January 30, 2009 Author Share Posted January 30, 2009 oh, i get it... could you also post the query you're talking about? My Query looks something like: $sqlQuery = "SELECT e_ticketbuyer.*, events.title, events.date, events.city, events.country FROM e_ticketbuyer, events WHERE events.e_id = e_ticketbuyer.e_id Or is there a better way to write it? But, also the main question: When doing a mysql_fetch_assoc for a SQL query, HOW do I differ the fields based on above query? Sincerely, Andreas Link to comment https://forums.phpfreaks.com/topic/142160-about-the-join-alternatives/#findComment-750485 Share on other sites More sharing options...
fenway Posted February 2, 2009 Share Posted February 2, 2009 I prefer: SELECT e_ticketbuyer.* , events.title, events.date, events.city, events.country FROM e_ticketbuyer INNER JOIN events USING ( e_id ) About the "differ" question, the e_id values are the same, by definition, so I'm not sure I understand. The table prefixes do not make it into the field names. Link to comment https://forums.phpfreaks.com/topic/142160-about-the-join-alternatives/#findComment-752442 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.