SEARCH YOUR SOLUTION HERE  

Joining tables having unmatching keys

,

I have two tables (in IBM DB2) to join. I will use inner join as

Code SQL: SELECT * FROM T1 X INNER JOIN T2 Y ON X.C1=Y.C1 WHERE X.C2>12;
Problem is key of the first table is the combination of three fields of another.

Code PHP: $left_table_id = $right_table_id1 . $right_table_id2 . $right_table_id3
I am not sore how to do this in query. It has to be something like that

Code SQL: SELECT * FROM T1 X INNER JOIN T2 Y ON X.C1=Y.D1 . Y.D2 . Y.D3 WHERE X.C2>12;
Any opinion

(For your interest, I am not the one who design this )

Posted On: Thursday 25th of October 2012 11:00:22 PM Total Views:  308
View Complete with Replies




Related Messages:

Excel VBA SQL - joining two tables   (184 Views)
- I'm having trouble joining two Excel worksheets (tables) in SQL using VBA. I have two tables with the same number of rows in each, one table iscalled Properties and another is called Dataprod and both have a column called entity as the first column. I would like to join these two tables with the entity dataand then filter the result with criteria of region (= abc) in the properties table. I've pasted the code I tried below but get an error when I tried to run: No value given for one or more required parameters MySQL = " SELECT * FROM [Properties$] , [DataProd$]" & _ " WHERE (Properties.entity = DataProd.entity)" & _ " AND (Properties.region='abc')" Set MyRecordset = New ADODB.Recordset MyRecordset.Open MySQL, MyConnect, adOpenStatic, adLockReadOnly
Filter results by joining three tables?   (90 Views)
MESSAGES +----------+-------------------+-------------+ | NAME | MESSAGE | DEPARTMENT | +----------+-------------------+-------------+ | Bob | Finished Task | Marketing | | Jack | Sending order | Sales | | Bob | Following up | Sales | | Tony | Collected | Research | | Phil | Given stock | Marketing | | James | Ordered wine | Sales | | Bob | Mail Done | Sales | | Cameron | Sent post | Research | +----------+-------------------+-------------+ STAFF +----------+-------------------+ | NAME | FOLLOWS | +----------+-------------------+ | Bob | Jake | | Jack | John | | Bob | Phil | | Tony | Bob | | Jack | John | | Bob | Phil | | Tony | Bob | +----------+-------------------+ DEPARTMENTS +----------+-------------------+ | NAME | Department | +----------+-------------------+ | Bob | Marketing | | Bob | Sales | | Jack | Sales | | Bruce | Cleaning | | Tony | Sales | | Jake | Marketing | | Phil | Research | | Tony | Sales | | Jake | Marketing | | Phil | Research | +----------+-------------------+ What i'm trying to do is SELECT 10 records from MESSAGES for a user thats logged in. Lets say for example user 'Bob' ------------------------------------------------- I want to show all messages either ---> posted by bob, or by someone he is following (in table STAFF), or for a department he is a member of (in table DEPARTMENTS). ------------------------------------------------- I managed to do the first two conditions, but by the third i break it Happy to clarify if this is confusing!
Trouble joining two tables properly.   (96 Views)
I have two tables. OTRegents just has 12 rows one for each "Title" FullCredits has all of the results by studentid for any completed Title's with Mark. I am trying to display Title's regardless if they exist in FullCredits and have them show up null if they do not exist,but still have them display so that each StudentID has 12 rows regardless. When I use this query: Code: SELECT c.StudentID, oc.Department, oc.Title, (case when oc.Title = c.Title then c.Mark else 0 end) Mark, (case when c.Grade >= oc.Grade then 1 else 0 end) Required, (case when c.Grade >= oc.Grade AND c.Mark >= oc.OnTrack then 1 else 0 end) Passed FROM FullCredits c JOIN OTRegents oc ON (oc.Title = c.Title) WHERE c.Type = 'Regents' GROUP BY c.StudentID, oc.Department, oc.Title I am receiving the proper mark's for each test, but the problem is not all of the tests are showing. I need it show all 12 tests for each StudentID When i Remove the Code: FROM FullCredits c JOIN OTRegents oc ON ---> remove the join on (oc.Title = c.Title) I get all of the tests, but the mark will only show correctly for ONE test. I am simply trying to have all 12 rows in the OTRegents be shown for each StudentID and show Mark for each one, if there is no row existing for the Title/Mark for that StudentID I need it to still show up, but for it to be null. Hopefully this makes sense, its clearly a joining issue, but I have been trying many ways and running out of ideas.
Treat value as a table name for joining   (103 Views)
! Wondering if there any way to treat some value as a table name when running Select queries. I mean: select t1.* from table1 AS t1 left join t1.column_with_table2_name AS t2 ...
Problem joining 4 tables   (92 Views)
I'm trying to join 4 tables but for some reason its not working out as I want it too. Here are the 4 tables and their relevant fields: teams - teamID, name, sport(sports.sportID) sports - sportID, name odds - oddsID, event(events.eventID), odd1, oddX, odd2, type events - eventID, team1(teams.teamID), team2(teams.teamID), sport(sports.sportID), timeUTC Just for clearifying when I write "team1(teams.teamID)" i mean there is a field team1 and that it "links" to the field teamID in the table teams. In events there are about 950 rows, and I would like to join the events table with all the other tables. As you perhaps see there are the fields team1 and team2 in the events, those fields will contain a reference each to some team in the teams table. Here is my suggestion on how to join the tables (unfortunatelly this doesnt work as I want it too), it returns 0 rows instead of 950. I tryed to left join all the tables, this returned about 22000 rows but team1.name, team2.name and sports.name where null in all the rows (even though I know only existing teams are referenced in the fields team1 and team2 of the events table). When I tryed right joins, I'm still trying to get the hang of joins so testing is a big part of it, mysql took most of the cpu and ran untill I kill it. Code: events.eventID, date_format(events.timeUTC, '%H:%i'), team1.name, team2.name, sports.name, odds.type, odds.odd1, odds.odd2, odds.oddX FROM `events` INNER JOIN teams AS team1 ON team1.name=events.team1 INNER JOIN teams AS team2 ON team2.name=events.team2 INNER JOIN sports ON sports.name=events.sport INNER JOIN odds ON odds.event=events.eventID Can you please help me Should I maybe join the tables in some other order, or is that irrelevant
COUNT/GROUP BY problem on joining tables   (82 Views)
I have 2 tables simplified below: Code: Table: `friends` fields: `id`|`name` 1|Jhon 2|Jack Table: `cars` fields: `friends`.`id`|`model` 1|BMW 1|Ford 2|Daihatsu 1|Benz 2|Ford 1|Daihatsu Now all I want to now is how many cars Jhon and Jack have got. so the result should look like: Jhon =>4 Jack=>2 So I did this Code: SELECT `name` COUNT(`cars`,`freinds`.`id`) FROM `friends`,`cars` GROUP BY `model` The query does not return any error but also no results. I was playing around with that sql statemet but no success.....only when I put all data in one table........so I wonder if I would have to apply a LEFT JOIN ON cars frist
joining multiple tables and finding average.   (109 Views)
This part has been solved, My new problem is the third post in this thread I have a query so far that looks like this: SELECT games.gameid, games.gamename, games.gameplat, platform.platformid, platform.platname FROM games left join platform on games.gameplat = platform.platformid WHERE gameplat = 'colname' ORDER BY gamename ASC This returns all game titles which match the platform of the url parameter. I also have a review table with columns review.id(key), review.itemid (gameid the rating is for) and review.rating. (score for gameid). I want to find the average value for review.rating and have that tied to gameid (i hope that makes sense ) When i try to find the avg for the gameid on another page I've been using the following query which works well but I don't know how to get this one and the one above working together. SELECT AVG(rating) as avg_rating FROM review left join games on review.review_item_id = games.gameid WHERE rating !='' AND review.review_item_id = games.gameid AND approve = 'y' I hope I've explained that well enough. I'll sum it up just to make sure. I want to get the platid from the url, Then return all gameids for that platid, and return the average value for review.rating where the itemid is the same as the gameid.
Trouble joining two queries that work together.   (91 Views)
I've got 2 queries that work independently and I'm having trouble joining them into one query. Code: SELECT deals.*, (SELECT count(deal_id) FROM spam WHERE deal_id = deals.deal_id) AS spamreports, (SELECT count(deal_id) FROM liked WHERE deal_id = deals.deal_id) AS timesliked, (SELECT count(deal_id) FROM comments WHERE deal_id = deals.deal_id) AS comments, (SELECT count(deal_id) FROM expired WHERE deal_id = deals.deal_id) AS expiredreports, members.member_id, members.member_name FROM deals LEFT OUTER JOIN members ON deals.member_id = members.member_id HAVING spamreports
"joining" tables in SELECT   (75 Views)
I'm trying to do a "join" kinda thing (relation) with a query. The table structures are: CREATE TABLE IF NOT EXISTS `glinks_Advert_Relations` ( `ID` int(11) NOT NULL, `CatName` char(255) NOT NULL, KEY `CatName` (`CatName`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE IF NOT EXISTS `glinks_Adverts` ( `ID` int(11) NOT NULL auto_increment, `Image` char(255) NOT NULL default '', `AdType` char(255) NOT NULL, `WhereToShow` char(255) NOT NULL, `Username` char(255) NOT NULL, `HitCredit` float default NULL, `HitPrice` float default NULL, `ImpressionsLeft` int(11) default NULL, `ImpressionUsed` int(11) default NULL, `ExpirationMonth` char(20) default NULL, `isActive` char(5) NOT NULL, `AreasToShow` char(255) NOT NULL, `DestinationURL` char(255) default NULL, `FullCatList` longtext, `NEWS` int(11) default NULL, `ARTICLES` int(11) default NULL, `VIDEO` int(11) default NULL, `PINUP` int(11) default NULL, `CONTESTS` int(11) default NULL, `ALL_LISTINGS` int(11) default NULL, `isDefault` int(11) NOT NULL default '0', `ALLAREAS` int(11) default '0', PRIMARY KEY (`ID`), KEY `isactive` (`isActive`), KEY `id` (`ID`), KEY `adtype` (`AdType`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ; Now, what I'm trying to do is -: SELECT * FROM glinks_Adverts,glinks_Advert_Relations WHERE ALLAREAS = 1 OR Advert_Relations.CatName = "British Columbia/Vancouver/Concrete/Cement Finishing" How exacly would I go by this Basically - a bit of history as to why I'm trying to do this. The "Advert" table just holds the advert (but can also have stuff like NEWS, ALLAREAS,etc. Then the "relation" table holds a list of the "categoires" this listing can show in. I need to randomly select a value, but just can't get it working :/ TIA! Andy , Is ALLAREAS 1 for all the rows If so then that would be why. The WHERE clause will filter on ALAREAS = 1 OR ... Do you mean AND instead
Importing CSV, and joining tables   (158 Views)
folks, I posted for help on this one in the PHP forum a while back, but wasn't able to get the help i needed. I have 5 scoresheets from 5 store locations (laser tag score sheets). They are CSV's, containing average scores, usernames, and a couple other score things. I will be importing them once a month for each store. I have other tables created, containing dates (month uploaded), and the store name itself, as well as relationship tables to tie it all together. Only thing is, there's too much data for me to manually update the relationship tables after uploading the initial data. Is there a better way i can do this Please let me know if any more info is needed.
Need help joining 2 tables   (105 Views)
I need help with my forum, its at http://www.beechlawnrovers.com/chat.php - I basically have 2 tables forummessages which contains all the posted topics and forumreplies which contains all the replies. They are linked by messageid and replymessageid. I just have simple SQL to list the topics by last posted on the main forum page: Code: SELECT * FROM forummessages ORDER BY messageid DESC However, I'm trying to have so so that when someone posts a new reply that topic then gets sent to the top. These are the structures of the 2 tables forummessages: messageid, messagetitle, messagebody, messageuser, messageemail, messagedate, messageip, messageexactadded forumreplies: replyid, replymessageid, replybody, replyuser, replyemail, replydate, replyip, replyexactadded Please help!
mysql joining 2 tables (default values)   (153 Views)
.. I came to situation where I have to join 2 tables on some other table: some_table: id domain table domains: id domaintype_id domain table domaintypes: id type This is how I join: domains ON some_table.domain = domains.domain domaintypes ON domaintype.id = domains.domaintype_id Now it happens that sometimes there is no domain for some_table row (so the domaintype will be NULL) and I want to be able to set this column default value 0 (when that happens).. How is it possible to do that Many
MAX between 2 tables   (93 Views)
Currently, this working query retrieves a number of people I haven't made contact for a month. Code: SELECT personas.id , DATEDIFF( NOW( ) , MAX(tareas_alias.fecha ) ) AS days_since_contact FROM personas LEFT OUTER JOIN tareas AS tareas_alias ON tareas_alias.persona = personas.id GROUP BY personas.id HAVING (days_since_contact >29 OR days_since_contact IS NULL ) AND mantener_contacto =3 ORDER BY days_since_contact DESC LIMIT 0 , 30 I gotta change it, because now I have another table that I use for storing emails that I send or receive from them. The structure is Table: emails (id, fecha, subject, body) . The "fecha" field contains the date. How can I change the Query, so I also include this contacts in the 'day's since contact' calc
Database Design : single vs multiple tables   (85 Views)
I have to design a database with a structure lika Airlines -> Route -> Flights now each flight has a number of prices which are variable from day to day so there could be some price for say 15th May to 20th May..or the price could be specific to just one day.. should i make a seperate table for prices for each flight.. or a single common table..or mayb a table for each airline or each route.. also should i have different tables for prices which are from say Day A to Day B and prices which are for a specific day...(since if i have a common table then there will be either a null or redundant data for the flight with a price on a specific date)
Delete from two tables   (82 Views)
So I've been trying to figure this one out this morning. I have two tables, work_history and work_history_detail. Not every work_history has a work_history_detail. I am trying to delete the records in work_history that fit a certain criteria and the corresponding records in work_history_detail (there is a request_id field in both tables). Any suggestions Should I delete from work_history and then go delete the records out of work_history_detail that don't have a record in work_history This is SQL Server.
consolidate tables   (75 Views)
hi, how can i consolidate my 4 tables i already used union but its a long code to make and it takes 85 seconds to execute.
joining a table on 1 of 2 columns   (115 Views)
I'm a bit stuck on how to achieve the following: I have 3 tables: members members_contacts members_business members_contacts consists of: member_id contact_member_id approved member_id and contact_member_id are the primary key I want to retrieve a list of contacts for a given member. The members table needs to be joined in order to get the members name. I'm having trouble retrieving the correct name as the member_id to join on could either be member_id or contact_member_id depending upon who added who. So, somehow I need to JOIN on member_id if its not equal to the given member id or JOIN on member_contact_id if thats not equal to the given member_id. So far, I have this: Code: SELECT m.firstname, m.lastname, mc.*, mb.business_id, mb.company FROM members m, members_contacts mc LEFT JOIN members_business mb ON mb.member_id = mc.contact_member_id WHERE ( mc.member_id = $memberid OR mc.contact_member_id = $memberid ) I need to add something to the where clause above to get the correct name or perhaps something completely different. Any ideas Many
Entering form data into multiple tables   (78 Views)
, I've got a two tables in my database: "customers" and "orders." I want to insert information from a form into both of them - in some cases identical information will need to be inserted into both. The other twist is that I'd like to insert the automatically generated id customer 'id' value into the orders table. I've tried using joins. Something like: PHP Code: INSERTINTOorders,customers(customers.id,customers.first_name,orders.del_address .....) but I don't seem to be having any luck. Can anyone help at all please ANY help would be hugely appreciated ...
Getting data from two tables in one column   (145 Views)
, I have a slightly tricky query that is giving me some trouble. I have 3 tables, as follows: keywords: id, name glossary: id, term, definition synonyms: id, synonym, glossary_id synonyms contains synonyms of the terms in my glossary. Keywords contains a list of keywords that we use throughout the site. What I need is to have a list of my keywords, and then have a list of any synonyms and glossary terms that match or are related to those keywords. So, basically, if keywords had 'cap', and glossary had 'hat' with a synonym of 'cap', my list needs to contain both 'cap' and 'hat'. Or, if my keywords and glossary had 'cap', and I had a synonym of 'hat', I still need both 'cap' and 'hat'.
Using a left join with 2 values linked to the same tables   (144 Views)
, I have a table that has two values, author_id and approver_id. Both of these refer to the value id in my users table. Is it possible to use a left join and have the query get the author and approver name (also in the users table) Or will I have to use multiple queries (Can't use nested selects, this particular server is still running MySQL3 -sigh-).