mbeals Posted June 19, 2008 Share Posted June 19, 2008 I have 4 tables that I need to join together in a not so simple way and my brain is hurting. Table one is a table of addresses..nothing fancy. Table two contains a list of customers, where one of the attributes is a reference back to an index in the address table. Table three is a table of cable modems which has an attribute which is an index in the customer table. So joining all three tables on these ID's produces a list of customer name, address, and modem info. I have that part of the query working and it's structured like this: Select customers.*, modems.*, addresses.*, Walkdata.* FROM addresses join customers on customers.addID = addresses.index join modems on modems.custID = customers.index This produces name, address, MAC address (and some other stuff) for all modems that are registered as subscribers. Now, I have another table, which is the list of modems (and stats) for all modems that are connected to the cable plant. I need to take this info and match it against the table we just created based on mac address (a common attribute), but I don't want to throw out non matching rows from either table (so I can see who isn't connected as well as unregistered modems attempting to connect). I'm sure the answer is stupidly simple, but my brain is shutting down. Quote Link to comment Share on other sites More sharing options...
fenway Posted June 19, 2008 Share Posted June 19, 2008 use a LEFT JOIN. Quote Link to comment Share on other sites More sharing options...
mbeals Posted June 19, 2008 Author Share Posted June 19, 2008 that will truncate the non matching rows from the right table though. I need to preserve those rows. Say I have these two tables: subscribers Name MAC John1 Jane2 active modems MAC status 1online 3online I need to create: Name MAC status John1online Jane2[/td] 3online the difference is that the first table is the result of two joins Quote Link to comment Share on other sites More sharing options...
mbeals Posted June 19, 2008 Author Share Posted June 19, 2008 doing some googling, I think I need a full outer join, but mysql doesn't support that yet. Any thoughts on a workaround? I saw one where you perform a union to a right join, but with the additional joins, that seems really clunky. Can I turn the first set of joins into a sub query and just perform the two sets of joins on that? Quote Link to comment Share on other sites More sharing options...
mbeals Posted June 19, 2008 Author Share Posted June 19, 2008 well I think i figured out a workaround, but it's complicated. Select customers.name, customers.phone, modems.mac, modems.qos, modems.subdate, addresses.num, addresses.street, addresses.unit, addresses.building, Walkdata.MAC, Walkdata.SNR, Walkdata.RX, Walkdata.STATUS FROM addresses join customers on customers.addID = addresses.index join modems on modems.custID = customers.index left join Walkdata on Walkdata.MAC = modems.mac union Select '','','','','','','','','',`MAC`, `SNR`, `RX`, `STATUS` FROM Walkdata where `MAC` NOT IN (select `mac` from modems)" It does work, but can it be optimized? Quote Link to comment Share on other sites More sharing options...
fenway Posted June 19, 2008 Share Posted June 19, 2008 that will truncate the non matching rows from the right table though. I need to preserve those rows. So start with MAC and left join the other two. Quote Link to comment 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.