Search the Community

Hi, I am not sure how to write a query for the below case, Pls help me out. ID description values M1 ab1 23 M1 ab2 54 M1 ab3 23 M2 ab1 67 M2 ab2 56 M2 ab3 91 M3 ab1 41 M3 ab2 53 M3 ab3 27 M3 ab4 41 Conditions: I need to pick the row when values are same for different description under similar ID, Example: Under ID (M1) , I have the description (ab1 and ab3) have same values (23), Similarly ID (M3) have same values 41 for descripttion ab1 and ab4. hence the result must be only Red texted in the table. Thanks In advance.

I am joining 2 tables using the query below. Its many to many relationship. The join works fine but creates multiple rows for each item which is expected but the Amount/value column is also duplicated. How to avoid this? Thanks As you can see the Current output the value (50000 appears 3 times instead of 1 and 27000 appears 3 times instead of 1) SELECT * FROM T1 INNER JOIN T2 ON T1.Projectex = T2.WBS_Parent Table 1: +-----------+------------+-----------+----------+--------+----------+-------+ | Projectex | CAPEX_OPEX | costelmnt | sap_vers | Period | FISCYEAR | VALUE | +-----------+------------+-----------+----------+--------+----------+-------+ | 0-01081 | CAPEX | 3416 | 61 | 3 | 2020 | 50000 | | 0-01081 | OPEX | 7077 | 30 | 5 | 2020 | 27000 | +-----------+------------+-----------+----------+--------+----------+-------+ Table2: +------------+-----------------+---------+---------+ | WBS_PARENT | FINANCILAL_YEAR | MEASURE | AMOUNT | +------------+-----------------+---------+---------+ | 0-01081 | 2020 | CPX | 2000000 | | 0-01081 | 2020 | OPX | 50000 | | 0-01081 | 2020 | OPX | 1000000 | +------------+-----------------+---------+---------+ CURRENT OUTPUT: Projectex| CAPEX_OPEX| costelmnt| sap_vers| Period| FISCYEAR| VALUE| 0 0-01081| CAPEX| 3416| 61| 3| 2020| 50000| 1 0-01081| CAPEX| 3416| 61| 3| 2020| 50000| 2 0-01081| CAPEX| 3416| 61| 3| 2020| 50000| 3 0-01081| OPEX| 7077| 30| 5| 2020| 27000| 4 0-01081| OPEX| 7077| 30| 5| 2020| 27000| 5 0-01081| OPEX| 7077| 30| 5| 2020| 27000| WBS_PARENT FINANCILAL_YEAR MEASURE AMOUNT 0 0-01081| 2020| CPX| 2000000 1 0-01081| 2020| OPX| 50000 2 0-01081| 2020| OPX| 1000000 3 0-01081| 2020| CPX| 2000000 4 0-01081| 2020| OPX| 50000 5 0-01081| 2020| OPX| 1000000 Expected Output: Projectex| CAPEX_OPEX| costelmnt| sap_vers| Period| FISCYEAR| VALUE| 0 0-01081| CAPEX| 3416| 61| 3| 2020| 50000| 1 0-01081| CAPEX| 3416| 61| 3| 2020| 2 0-01081| CAPEX| 3416| 61| 3| 2020| 3 0-01081| OPEX| 7077| 30| 5| 2020| 27000| 4 0-01081| OPEX| 7077| 30| 5| 2020| 5 0-01081| OPEX| 7077| 30| 5| 2020| WBS_PARENT FINANCILAL_YEAR MEASURE AMOUNT 0 0-01081| 2020| CPX| 2000000 1 0-01081| 2020| OPX| 50000 2 0-01081| 2020| OPX| 1000000 3 0-01081| 2020| CPX| 4 0-01081| 2020| OPX| 5 0-01081| 2020| OPX|

I have a problem with an XML file that has the following structure: <?xml version="1.0" encoding="UTF-8" standalone="yes"?> <DATAPACKET Version="2.0"> <METADATA><FIELDS> <FIELD attrname="Id" fieldtype="i4" readonly="true" SUBTYPE="Autoinc"/> <FIELD attrname="Blocco" fieldtype="string" WIDTH="5"/> <FIELD attrname="Domanda" fieldtype="string" WIDTH="2"/> <FIELD attrname="Risposta" fieldtype="boolean"/> <FIELD attrname="Capitolo" fieldtype="string" WIDTH="2"/> <FIELD attrname="Indice" fieldtype="string" WIDTH="3"/> <FIELD attrname="Argomento" fieldtype="string" WIDTH="1"/> <FIELD attrname="SubArgomento" fieldtype="string" WIDTH="2"/> <FIELD attrname="Figura" fieldtype="string" WIDTH="4"/> <FIELD attrname="FiguraBlk" fieldtype="string" WIDTH="4"/> <FIELD attrname="Difficolta" fieldtype="string" WIDTH="2"/> <FIELD attrname="Testo" fieldtype="string" WIDTH="320"/> <FIELD attrname="Lingua1" fieldtype="string" WIDTH="320"/> <FIELD attrname="Lingua2" fieldtype="string" WIDTH="320"/> <FIELD attrname="Lingua3" fieldtype="string" WIDTH="320"/> <FIELD attrname="Commento" fieldtype="string" WIDTH="256"/> <FIELD attrname="Aiuto" fieldtype="string" WIDTH="128"/> <FIELD attrname="Foto1" fieldtype="string" WIDTH="5"/> <FIELD attrname="Foto2" fieldtype="string" WIDTH="5"/> <FIELD attrname="Foto3" fieldtype="string" WIDTH="5"/> <FIELD attrname="Foto4" fieldtype="string" WIDTH="5"/> <FIELD attrname="Foto5" fieldtype="string" WIDTH="5"/> <FIELD attrname="Video1" fieldtype="string" WIDTH="5"/> <FIELD attrname="Edl1" fieldtype="string" WIDTH="15"/> <FIELD attrname="Video2" fieldtype="string" WIDTH="5"/> <FIELD attrname="Edl2" fieldtype="string" WIDTH="15"/> <FIELD attrname="Video3" fieldtype="string" WIDTH="5"/> <FIELD attrname="Edl3" fieldtype="string" WIDTH="15"/> <FIELD attrname="Audio1" fieldtype="string" WIDTH="12"/> <FIELD attrname="Audio2" fieldtype="string" WIDTH="12"/> <FIELD attrname="Audio3" fieldtype="string" WIDTH="12"/> <FIELD attrname="Html1" fieldtype="string" WIDTH="5"/> <FIELD attrname="Html2" fieldtype="string" WIDTH="5"/> <FIELD attrname="Html3" fieldtype="string" WIDTH="5"/><FIELD attrname="Libro1" fieldtype="string" WIDTH="5"/> <FIELD attrname="Libro1PosY" fieldtype="string" WIDTH="5"/> <FIELD attrname="Libro2" fieldtype="string" WIDTH="5"/> <FIELD attrname="Libro2PosY" fieldtype="string" WIDTH="5"/> <FIELD attrname="Libro3" fieldtype="string" WIDTH="5"/> <FIELD attrname="Libro3PosY" fieldtype="string" WIDTH="5"/> <FIELD attrname="Info1" fieldtype="string" WIDTH="120"/> <FIELD attrname="Info2" fieldtype="string" WIDTH="120"/> <FIELD attrname="Gruppo1" fieldtype="string" WIDTH="3"/> <FIELD attrname="Gruppo2" fieldtype="string" WIDTH="3"/> <FIELD attrname="Gruppo3" fieldtype="string" WIDTH="3"/> </FIELDS><PARAMS AUTOINCVALUE="7166"/></METADATA> <ROWDATA> <ROW Id="2" Blocco="11023" Domanda="02" Risposta="TRUE" Capitolo="01" Indice="A01" Argomento="A" SubArgomento="1" Figura="" FiguraBlk="" Difficolta="6" Testo="I ciclomotori possono avere due o tre ruote" Lingua1="Les motocycles légers peuvent avoir deux ou trois roues" Lingua2="Kleinkrafträder können zwei oder drei Räder haben" Lingua3="I ciclomotori possono avere due o tre ruote" Commento="infatti i CICLOMOTORI possono avere DUE, TRE e anche QUATTRO RUOTE, cilindrata fino a 50 cm³ e velocità fino a 45 km/h." Aiuto="Classificazione dei veicoli." Foto1="3113" Foto2="" Foto3="" Foto4="" Foto5="" Video1="" Video2="" Video3="" Audio1="04023_40231" Audio2="" Audio3="" Html1="" Html2="" Html3="" Libro1="1" Libro2="1" Libro3="" Info1="11023" Info2="Ciclomotori"/> <ROW Id="3" Blocco="11023" Domanda="03" Risposta="TRUE" Capitolo="01" Indice="A01" Argomento="A" SubArgomento="1" Figura="" FiguraBlk="" Difficolta="5" Testo="Non tutti i veicoli a motore a due ruote vengono classificati ciclomotori" Lingua1="Pas tous les véhicules à moteur à deux roues peuvent être classifiés des motocycles légers" Lingua2="Nicht alle zweirädrigen Kraftfahrzeuge werden als Kleinkrafträder eingestuft" Lingua3="Non tutti i veicoli a motore a due ruote vengono classificati ciclomotori" Commento="infatti vengono CLASSIFICATI CICLOMOTORI solo i veicoli a DUE RUOTE con CILINDRATA NON SUPERIORE a 50 cm³ e VELOCITÀ NON SUPERIORE a 45 km/h." Aiuto="Classificazione dei veicoli." Foto1="1238" Foto2="" Foto3="" Foto4="" Foto5="" Video1="" Video2="" Video3="" Audio1="04023_40232" Audio2="" Audio3="" Html1="" Html2="" Html3="" Libro1="1" Libro2="1" Libro3="" Info1="11023" Info2="Ciclomotori"/> it continues with this structure but it is very long. How can I do from my SQL DB to extract a data ("Libro3") to insert it inside every occurrence of ''Libro3' of XML file? In my sql to recognize the line to be modified I have Id,Blocco, Libro3 obviously, but i don t know how i can modify the file. to recognize the line to be modified on the sql I have line, id and block

ID Name Orderdate Catalog Price 7b 34-10 NULL 3000 7b 34-10 NULL 3000 7b 34-10 NULL 2000 7b 35-12 PL-17 3000 8b 35-11 PL-18 4000 8b 34-13 PL-18 4000 8b 34-14 PL-18 4000 8b 34-15 PL-18 4000 9b 35-12 PL-19 5000 9b 35-11 PL-19 5000 9b 34-18 PL-19 5000 9b 34-19 PL-19 5000 9b 34-20 PL-19 5000 I want a List of the products where Id starts with 7 where Name starts with 34 where Orderdate = null whit the highest catalog price Output should be this ID Name Orderdate Catalog Price 7b 34-10 NULL 3000 7b 34-10 NULL 3000

Hello, I have installed different versions of SQL Server in my laptop (Asus X44H), but here is the error -> (provider: Named pipes provider, error:40- could not open a connection to SQL Server)(Microsoft SQL Server, Error:2) SQL Server instance MSSQLSERVER could not be installed, I don't know why! (I checked that in services.msc, there is no MSSQLSERVER), I have also changed my windows-10 but the issue has not been resolved yet. Any Solutions? Thanks.

Hi . I'm having trouble with the AUTO INCREMENT statement in SQL. I've built the table : CREATE TABLE tenants( apartment_number_first_floor INT AUTO_INCREMENT, family_name VARCHAR (12) DEFAULT NULL, sur_name VARCHAR (8) DEFAULT NULL, telephone_number INT(3) NOT NULL, PRIMARY KEY (apartment_number_first_floor) ); when inserting a row: INSERT INTO tenants VALUES(' ',' '); The IDE doesn't accept the column count: ER_WRONG_VALUE_COUNT_ON_ROW: Column count doesn't match value count at row 1 So i tried filling the rows just to check if the IDE will run the statement INSERT INTO tenants VALUES(103,'שקלובין','מרתה',203); but the IDE refuses to increment Any ideas? Thanks

Hi, How can I show string results as characters in sql plus? select* from example a where a.insert_time>='12-dec19' and a.status=1 as 'Cars I want that if 'status' field status result would show '1' the the output would show 'Cars' and if the result would show '2' the the output would show 'Bikes' instead of strings. Best regards

Hi, How can I show string results as characters in sql plus? select* from example a where a.insert_time>='12-dec19' and a.status=1 as 'Cars I want that if 'status' field status result would show '1' the the output would show 'Cars' and if the result would show '2' the the output would show 'Bikes' instead of strings. Best regards

Hi I have a query that returns the count of entries based on CURDATE(), it works for any dat in this month (April) but does not work for next month (May). I think it is something to do with CURDATE()+3 returns 2018-04-31 which should be 2018-05-01 This Works as Date is in April SELECT COUNT(Machine) FROM production WHERE Status = 'Confirmed' AND DATE(FROM_UNIXTIME(production.Required)) = CURDATE() This does not work as in May SELECT COUNT(Machine) FROM production WHERE Status = 'Confirmed' AND DATE(FROM_UNIXTIME(production.Required)) = CURDATE()+3

There is a table, named student_mark and I have to find which 2 students are having min marks.I also tried group by but it is not working.

Hi, I'm using the SQL TryIt Editor but the Truncate Table command seems to fail when running. It states there is a syntax error but i cannot see any issue. Is anyone else able to truncate one of the existing tables? Thanks,

Hello everyone, i must do a query that finds all words that finish whit a,e,i,o or u. i' ve used this query but my output in empty . select City from Station where City like '%[aeiou]'; i' ve used the wildcards wrongly?

Hello All, I am trying to get the latest record available for a field, I want to learn how to write/pull what I know as of record status "1" from my results since let's say; an activity was updated several times but I just need the latest update base on the date, I tried using distinct and it worked: each row is different but I am still receiving all the updates of each activity. I tried to use (Group by) base in the activity Id and I am receiving this error message "Column 'activity_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." I am using the Microsoft SQL server management studio.

Hi, I want to be able to filter by date looking back 6 months. I then want to include data from 12 months, 18 months, 24 months etc How would i do this without having to repeat myself? Currently i am using the below and then repeating it e.g between dateadd(dd,-189,getdate()) and dateadd(dd,-183,getdate()) between dateadd(dd,-373,getdate()) and dateadd(dd,-366,getdate()) Thanks in Advance,

Hi, I am trying to join two tables that have similar fields, but in slightly different formats. I was previously using something along the lines of like '%*%' Then SUBSTRING(b.fieldname,CHARINDEX(b.fieldname,'*',1)+1,9) However the format has now changed and I am struggling to put something together. Table A BD8K1 23161*35*201904231110*QULN Table B 23161*35*1 However the number of characters in both could vary so cant use that. Table A BD8K1 242875*10*201904251015*NBB1 Table B 242875*10*113 Is there some code that could be written that's something like: A – From the space use the value up to and including the 2nd * (BD8K1 is at the beginning of every value in table A so could potentially use from character 7) B – Use the value up to and including the 2nd * Any help would be very much appreciated. Thanks James

Hello w3schools community ! I would like to know .. - What is exactly the difference between SQL ( Structured query language ) and MySQL ?, ( with examples if possible ) - Does learning SQL suffice ? Or should I use a RDBMS such as MySQL, Oracle .. etc ? Thank you .

I have a diagram representing a database and need to find a way to update a given table with respect to information about elements in that table. Here is the database design: https://i.stack.imgur.com/HR6q2.jpg I have a Playlist defined which is called "Frank's Stuff" which is owned by the Customer Frank Harris. The question is the following: Update the playlist "Frank's Stuff" as a function of the track duration. Display this playlist with the TrackID, Name and Milliseconds to verify. I've read that you cannot use an ORDER BY statement with and UPDATE statement unless it is enclosed in a SELECT statement. I'm not quite sure how to do all this though and would greatly appreciate some help. Thanks!

can we grant / give access of DDL commands to others ?

So I'm trying to filter some records out of my select inside a form on my website. Problem is that I currently have this setup: Database Table1 Time Number Table2 Number Something I'm trying to filter time by checking how many records show up of number in table2 So basically I select time from table1 where number is less than <some amount> in table2 How do I actually put that in sql? I currently have this but it isn't working: SELECT Time FROM Showcase WHERE Number IN (SELECT COUNT(Number) FROM Build WHERE COUNT(Number) < 50); it currently doesn't end up with anything but in theory it should show the times from the records where the count of the value of number is less than 50 so if Number = 40 and it gets counted 70 times it shouldn't show up. but if Number = 30 is counted 29 times it should show up can anyone tell me what is going wrong?

Hello Everyone, I am writing a query in SQL to pull the first date an employee became a teacher with no break in service. using the job table here to find effective date when a employee became teacher like example. 1. Teacher - 1/1/18. 2. coordinator- 2/1/18 3. Teacher- 3/1/18 Here in result I need the last entry as there is no gap in that title now.

No keywords are highlight. for what reason behind that. please help me Here is my HTML code and at the bottom PHP. HTML CODE: <form id="nbc-searchblue1" method="post" enctype="multipart/form-data" autocomplete="off"> <input type="text" id="wc-searchblueinput1" class="nbc-searchblue1" value="<?php echo $search; ?>" placeholder="Search Iconic..." name="search" type="search" autofocus> <br> <input id='nbc-searchbluesubmit1' value="Search" type="submit" name="button"> </form> PHP CODE: <?php // We need to use sessions, so you should always start sessions using the below code. session_start(); // If the user is not logged in redirect to the login page... if (!isset($_SESSION['loggedin'])) { header('Location: ../index.php'); exit(); } include 'connect.php'; $search = $sql = ''; if (isset($_POST['button'])){ $numRows = 0; if (!empty($_POST['search'])){ $search = mysqli_real_escape_string($conn, $_POST['search']); $sql = "select * from iconic19 where student_id like '%{$search}%' || name_bangla like '%{$search}%' || name like '%{$search}%' || phone like '%{$search}%' || blood like '%{$search}%' || district like '%{$search}%'"; $result = $conn->query($sql); $numRows = (int) mysqli_num_rows($result); } if ($numRows > 0) { echo "<table> <thead> <tr> <th><b>Photo</th> <th><b>Student ID</th> <th style='font-weight: 700;'><b>নাম</th> <th><b>Name</th> <th><b>Mobile No.</th> <th><b>Blood Group</th> <th><b>Email</th> <th style=' font-weight: 700;'><b>ঠিকানা</th></tr></thead>"; while ($row = $result->fetch_assoc()){ $student_id = !empty($search)?highlightWords($row['student_id'], $search):$row['student_id']; $name = !empty($search)?highlightWords($row['name'], $search):$row['name']; $district = !empty($search)?highlightWords($row['district'], $search):$row['district']; echo "<tbody>"; echo "<tr>"; echo "<td>" . "<div class='avatar'><a class='fb_id' target='_blank' href='https://www.facebook.com/" . $row['fb_id'] . "'><img src='" . $row['photo'] . "'><img class='img-top' src='fb.png'></a>" . "</td>"; echo "<td data-label='Student ID'>" . $row['student_id'] . "</td>"; echo "<td data-label='নাম' style=' font-weight: 700;'>" . $row['name_bangla'] . "</td>"; echo "<td data-label='Name' style='font-weight:bold;' >" . $row['name'] . "</td>"; echo "<td data-label='Mobile No'>" . "<a href='tel:" . $row['phone'] . "'>" . $row['phone'] . "</a>" . "</td>"; echo "<td data-label='Blood' style='color:red; font-weight:bold;' >" . $row['blood'] . "</td>"; echo "<td data-label='Email'>" . "<a href='mailto:" . $row['email'] . "'>" . $row['email'] . "</a>" . "</td>"; echo "<td data-label='ঠিকানা' style='font-weight: 700;'>" . $row['address_bangla'] . "</td>"; echo "</tr>"; echo "</tbody>"; } } else { echo "<div class='error-text' style='font-weight: 700;'>No Result</div><br /><br />"; } } $result = $conn->query("SELECT * FROM iconic19 $sql ORDER BY id DESC"); function highlightWords($text, $word){ $text = preg_replace('#'. preg_quote($word) .'#i', '<span style="background-color: #F9F902;">\\0</span>', $text); return $text; } $conn->close(); ?>

Hi I am new to this forum and very new to this SQL issue. As you know Excel allows you to sort a set of data in ascending or descending order and through an ordered list. And here begins my problem, ie I would like to know if it is possible to do this order through a SQL statement. Sort ascendingly and descending I already know how to do but what I wanted, and it is shown in the example I attached, it was first to sort by field F58 in ascending order and then to sort by field F66 by the following list 'RR-CON, RR-COGP, RR -COCN, RR-COCS, RR-COGL, RR-COS ' I've got an example, with the VBA code that fetches me a table from the F58 and F66 values according to certain criteria, and then that data is only sorted by field F58, I'd like it to be sorted from the list above. It's possible? Many thanks Jorge Cabral Teste com SQL_V1 - ENG.xlsm

Hi, Need help in code trying to find employees old positon number but the job table gives current row position number since the employee was rehired. Please help.

PLEASE HELP IN A QUERY. i AM TRYING TO DO SELF JOIN WITH A QUERY WHICH 2 OR MOR ELEFT OUTER JOINS IN IT. HOW TO DO SELF JOIN HERE . THANKS IN ADVANCE.