Jump to content

Search the Community

Showing results for tags 'join'.



More search options

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type


Forums

  • W3Schools
    • General
    • Suggestions
    • Critiques
  • HTML Forums
    • HTML/XHTML
    • CSS
  • Browser Scripting
    • JavaScript
    • VBScript
  • Server Scripting
    • Web Servers
    • Version Control
    • SQL
    • ASP
    • PHP
    • .NET
    • ColdFusion
    • Java/JSP/J2EE
    • CGI
  • XML Forums
    • XML
    • XSLT/XSL-FO
    • Schema
    • Web Services
  • Multimedia
    • Multimedia
    • FLASH

Calendars

  • Community Calendar

Group


AIM


MSN


Website URL


ICQ


Yahoo


Jabber


Skype


Location


Interests


Languages

Found 14 results

  1. SELECT JOIN - A Failed Query

    PROBLEM: Turn a two-step SELECT procedure into a one-step procedure. The two-step procedure is 1) Discover with a query to a parent table what other rows in the parent table are related to the queried row. 2) Obtain selected data for the queried row and other related rows from the parent table and a child table that are connected by a FOREIGN KEY. BACKGROUND: I have two tables -- a parent (parent_table) and a child (child_table) table - connected by a valid FOREIGN KEY and an additional table (ref_table) that contains information about the relationship among the rows of both the parent and child tables. Please find below the results of three SHOW CREATE TABLE statements to help you in your understanding of the table structure. In addition, I have included the INSERT statements for the child_table and ref-table. I accidentally destroyed the INSERT statement for the parent_table. This said, the parent and child tables are very similar in structure. DISCLAIMER: Please understand that the problem that I have created is heuristic in nature and is being used to create a prototype for subsequent, more practical use. The PARENT Table parent_table CREATE TABLE `parent_table` ( `id` int(3) NOT NULL DEFAULT '0', `usertype` enum('1','2','3') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '2' COMMENT '1=good, 2=neutral, 3=bad', `username` char(150) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, PRIMARY KEY (`id`,`usertype`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 The CHILD Table child_table CREATE TABLE `child_table` ( `id` int(3) NOT NULL DEFAULT '0', `usertype` enum('1','2','3') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '2' COMMENT '1=good, 2=neutral, 3=bad', `userbio` varchar(500) DEFAULT NULL, KEY `parent` (`id`,`usertype`), CONSTRAINT `child_table_ibfk_1` FOREIGN KEY (`id`, `usertype`) REFERENCES `parent_table` (`id`, `usertype`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 INSERT INTO `child_table` (`id`, `usertype`, `userbio`) VALUES ('1', '1', 'I am Roddy.'), ('2', '2', 'I am Beth.'), ('3', '3', 'I am Matt.'), ('4', '3', 'I am Tim.'), ('5', '2', 'I am Tylor.'), ('6', '1', 'I am Liz.'), ('7', '1', 'I am Aldo.'), ('8', '1', 'I am Adzit.'), ('9', '3', 'I am Jason.'), ('10', '3', 'I am David.') The REFERENCE Table ref_table CREATE TABLE `ref_table` ( `ref_id` int(3) NOT NULL DEFAULT '0', `id` int(3) DEFAULT NULL, `ref` int(3) DEFAULT NULL, `count_ref` int(1) DEFAULT NULL, KEY `par_ref` (`ref_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 INSERT INTO ref_table (ref_id,id,ref,count_ref) VALUES ('1','1','2','1'), ('2','1','6','2'), ('3','1','7','3'), ('4','2','6','1'), ('5','2','9','2'), ('6','4','1','1'), ('7','4','2','2'), ('8','4','6','3'), ('9','4','10','4'), ('10','5','6','1'), ('11','6','1','1'), ('12','6','2','2'), ('13','6','9','3'), ('14','7','5','1'), ('15','7','6','2'), ('16','8','1','1'), ('17','9','10','1'), ('18','10','4','1'), ('19','10','6','2'), ('20',10, NULL,'1'); EXPLANATION BY EXAMPLE: Say a user is interested in row 4 of the parent_table. When the database is queried a SELECT JOIN statement looks in the id field of the ref_table and finds four corresponding rows identified by ref_id 6, 7, 8, and 9. With each of these latter rows is associated a different row -- namely, 1, 2, 6, 10. Without further selection is returned all of the information contained in the parent_table and child_table associated with rows 1, 2, 4, 6, and 10. This is what is supposed to happen, but does not. The REJECTED SQL STATEMENT SELECT * FROM parent_table OUTER JOIN child_table ON parent_table.id = child_table.id OUTER JOIN ref_table ON parent_table.id = ref_table.id QUESTION ONE: Does the table structure make sense? Are the constraints properly set? QUESTION TWO: Why is my SQL statement rejected as poorly formatted? This is my first attempt to use the JOIN clause. So, please be as thorough with your answer as possible. I must believe that I have a long road ahead with MySQL and should prepare for it as i move forward. Roddy
  2. updatetext

    I need to update over 400 email address in a table in the database. I need to keep the first half of the email address and change everything after the '@' symbol. Example: FROM: john.doe@oldname.com -- TO: john.don@newcompanyname.com Is there any easy way?
  3. mysql join from 2 different databases

    Hi all, I have a question about join or inner join; not sure in what way it should be used. I now use 2 queries in 2 different databases (made in phpmyadmin). SELECT `title` FROM `writers` WHERE id ='qwert58efedd1979f'; SELECT `name`, `lastname`, `str`, `nr`,` place` FROM `client` WHERE id ='qwert58efedd1979f'; I would like to make one mysql query and use join to search in 2 tables in 2 different databases. Can anyone tell how mysql does this?
  4. Hi all, I'm trying to create a query which selects fields from 2 tables. I have a table 'stock' to store cars for sale and a table called 'custstock' which has the customer username and the vehID. How do I show all the cars that relate the that user who is logged in? My current query that doesn't work; $username = "-1"; if(isset($_SESSION['username'])){ $username = $_SESSION['username']; } // executeable query $sql = ("SELECT * FROM [stock] JOIN custstock ON [stock].custstockvehID = custstock.vehID WHERE username = '$username'"); Thanks, Jack
  5. I have a mySQL posts table, a files table, and a relationships table. I need to sum file download counts grouped by the post the files are associated with. SELECTp.id, p.post_title, count(d.download_id) FROM posts p INNER JOIN file_log d ON p.id = d.file_idgroup by p.post_title The above query works as long as the titles of the files are the same, which is not always the case. I really need to group them by their relationship to the post they are associated with. The relationships are stored in relationships r, which shows r.post_id (which is p.id from posts), and r.file_id (which is d.file_id from file_log) I have tried a few subqueries, but I am not sure how to join the relationships table to my query and group counts by r.post_id Thanks for any help.
  6. Error #1136 Message

    Hi Guys, am new to PHP and just got stuck with this error message: Column count doesn't match value count at row 1. The code is this below: $query = "INSERT INTO members(email, name, gender, dob, profile, password, group_ID) SELECT operations.group_ID FROM operations JOIN members ON operations.group_ID = members.group_ID" ; $result = $db->query($query); Am trying to insert data into a table called members, and needs to get the group_ID from another table called operations. Not so sure what is wrong with the code; will appreciate help from anyone. Cheers.
  7. Packet and N-SubPacket Msql query

    I am making packet type system when in my **Packet Table** , parent packet ( `id` which is primary key ) and its N -Sub Packet is under (`parent_id`) is stored , below is my table structure :**Packet_table** id | packet_name | parent_id | 1 | 01 | 0 2 | 02 | 0 3 | 03 | 1 4 | 04 | 1 5 | 05 | 1 6 | 06 | 4 7 | 07 | 4 8 | 08 | 3 9 | 09 | 5 10 | 010 | 2 ........................so on and on with N packets in same table Below is what i have tried *but its not getting* `id` N sub packet detail properly: SELECT p.`packet_name` AS MAIN, s.`packet_name` AS SUB FROM packet_table s LEFT JOIN packet_table p ON s.`parent_id` = p.`id`thats as per above table : **id** ( which is primary / auto increment )id = 1 -> main packet (01) , its sub and N sub packets are :01 - > 03,04,0504 -> 06,0703 -> 0805 -> 09 **in short** 01 -> 03 -> 08 04 -> 06 , 07 05 -> 09 its not necessary above design format mysql code .. just simple N sub packet query will doabove is just few but in my case there will be N number of sub packet for each (id).So how it be can achieve .Note : it can be same as Category and N sub Category type
  8. how to find who just join

    hello, i am trying something new, well new for me that is, and im not sure how to go about doing this, i am trying to find out who just joined my page and if the person just join i would like to display that person name onto the site. i just need to know how to find out who just join and i believe i can take it from there
  9. Multiple Nested queries - problem

    Greetings, I've used sql to a moderate degree within vba and some third-party software (i.e. ReportWriter) and for the most part I do ok. I currently have a report I am trying to generate with an underlying sql query and I just can't get it to work. I was able to mimic what I needed in MS Access but there are some syntax and formatting that I have not used before. I've looked through the examples but I'm missing something. The report will displays radio assets that have Not been assigned a workorder ticket for a current project that is underway. Particulars:- ProjectID=27 if the asset has/had a workorder created for the project. NULL otherwise.- CategoryID=21, 22 or 24 if the radio asset can be assigned the ProjectID (21=Portable Radio, 22=Mobile Radio, 24=Control Station) * I've created a report that shows all the assets that have been assigned the ProjectID=27 which was easy enough but I have to be able to query a view table that has both Current and History records - with that said, you can possibly have the same AssetID multiple times. What I've tried to do is perform a query that will1. Pull All AssetID's with CategoryID in(21,22,24) from ASSET_V table.2. Left Join the Asset_V table (assetid) with WorkOrderAll_V table (assetid) / Where ProjectID=27 AND with CategoryID in(21,22,24)3. Left Join both (1 & 2) Where WorkOrderAll_V.assetid Is Null. I have two tables with the fields below: Asset_V-----------asset_v.assetidasset_v.serialnumberasset_v.categoryasset_v.agencygroupasset_v.agencynameasset_v.agencyidasset_v.categoryid ServiceCodeIDasset_v.assignedtoasset_v.vehicleidasset_v.aglocationasset_v.descriptionasset_v.modelnumberasset_v.statusid assetStatusIDasset_v.status WorkOrderAll_V--------------------workorderall_v.assetidworkorderall_v.projectidworkorderall_v.statusidworkorderall_v.status woStatusworkorderall_v.wotype SELECT DISTINCTROWasset_v.assetid,asset_v.serialnumber,asset_v.category,asset_v.agencygroup,asset_v.agencyname,asset_v.agencyid,asset_v.categoryid ServiceCodeID,asset_v.assignedto,asset_v.vehicleid,asset_v.aglocation,asset_v.description,asset_v.modelnumber,asset_v.statusid assetStatusID,asset_v.status,r1.projectid,r1.statusid,r1.status woStatus,r1.wotypeFROM (asset_vLEFTJOIN (SELECT DISTINCTROW a1.ASSETID, a1.CATEGORYID ServiceCodeID FROM asset_v as a1) )LEFTJOIN (SELECT r1.assetid, r1.projectid FROM workorderall_v as r1) ON a1.assetid = r1.assetid WHERE (((a1.CATEGORYID) In (21,22,24)) AND ((r1.projectid)=27)) WHERE (((a1.ServiceCodeID) in (21,22,24)) AND ((r1.assetid) IS NULL))
  10. SQL Inner Join - Issue

    Hi.Wonder if you can help, am quite new to SQL. In simple terms, I have a transaction table - and in that table are references to other related tables. In this simple query I am trying to obtain the first name and last name from the user ID for a transaction. SELECT transaction.transactionId, user.firstName, user.lastNameFROM [transaction]INNER JOIN [user]ON transaction.userId = user.userIdORDER BY user.userId However I get the response "incorrect syntax near the keyword transaction". I then try: SELECT 'transaction'.transactionId, user.firstName, user.lastNameFROM [transaction]INNER JOIN [user]ON 'transaction'.userId = user.userIdORDER BY user.userId I then receive "cannot call methods on varchar" - which I am assuming SQL thinks I am referring to text - rather than table names. I then tried: SELECT dbo.transaction.transactionId, user.firstName, user.lastNameFROM [transaction]INNER JOIN [user]ON dbo.transaction.userId = user.userIdORDER BY user.userId However no joy - I get the initial error message. Is someone able to point me in the right direction as to where I am going wrong? I have quite a few joins I need to do - but I thought if I could get this fixed first that would be a start.Cheers. Kev
  11. {post removed by author}
  12. Hey Everyone, I am fairly new to SQL and pulling out whatever little hair is left on my head. I have a data dump where we are trying count the "First Call Resolution" for tickets that an employee opens and closes himself. The company is using MS Access and I am working with a basic data dump from the ticketing system. I have already weeded out everything I don't need, but I'm having trouble trying to compare and count two rows in the same table. Here is an example of the info in the table ticket_number | agentID | activity | notesGLOB-1234 | tier1_A1 | OPENS | ticket openedy by tier1_A1 - BobGLOB-1234 | tier1_A1 | REMRK | helped client fix their stuffGLOB-1234 | tier1_A1 | CLOSE | ticket closed by tier1_A1 - BobGLOB-1235 | tier1_A2 | OPENS | ticket openedy by tier1_A1 - Chris GLOB-1235 | tier1_A2 | REMRK | Could not resolve issue, routing to tier 2GLOB-1234 | tier2_A2 | REMRK | Recevied ticket from tier1, fixed problem remotely, advised clientGLOB-1234 | tier2_B1 | CLOSE | ticket closed by tier2_A2 - Mike I want to be able to check the table to see how many ticket tier1 agents have resolved on their own. In order to do this, I just have to count the number of times that I have a row where ticket_number, agentID are the same, and where Activity = 'open' and activity = 'close'. I want to make the solution simple and elegant and try and do it within the same table. I guess one other option would be to create two separate views a then compare the two tables to each other? Please help me!!!Thanks in advance
  13. Join query

    Hi I've been looking at the tutorial and spoken to a few people regarding a query which requires me to join two tables together and data within a specific data range. The environment I am running this in is SQL Server 2005. The following is the query I have tried to run: select *from individual (this is a table name)where update_timestamp <= 31/12/2008join delegate (this is a table name)as on member.member_ref=delegate.member_Refwhere start_date >= 31/12/2008 but when I run this, I get:Msg 156, Level 15, State 1, Line 4Incorrect syntax near the keyword 'join'. Someone advised me to try the following: select *from individualjoin delegateon member.member_ref=delegate.member_Refwhere start_date >= 31-dec-2008where update_timestamp <= 31-dec-2008 but when I do this, I get:Msg 156, Level 15, State 1, Line 6Incorrect syntax near the keyword 'where'. Can anyone advise me where I'm going wrong? If you require further info, please let me know. Thanks
  14. Sql Statement Help

    Hi Guys, I am into SQL Query as follows: Event table : need from here event_id :: this PK here and FK in Event attendee Event_attendee table : need from here teacher_id:: FK here and PK in teacher table Teacher table : need from here to know if male or female based on teacher_id What I am into get the total of attendee total of male total of female how Can I do that? Thank you in advance for any help
×